An ACID<\/a> (acronym standing for A<\/strong>tomicity, C<\/strong>onsistency, I<\/strong>solation, and D<\/strong>urability) compliant relational database that allows you, among others, to manage your data with the powerful and proven SQL<\/a>, to take care of your data integrity with transactions<\/a>, foreign keys<\/a>, …
But you already know that \ud83d\ude42<\/em><\/p>\n\n\n\n
J<\/strong>avaS<\/strong>cript O<\/strong>bjet N<\/strong>otation, better known as JSON<\/a>, is a lightweight and very popular data-interchange format. Use for storing and exchanging data. Started with MySQL 5.7.8, you can handle JSON documents<\/strong> in a \u00ab\u00a0relational way\u00a0\u00bb, using SQL queries and also storing them using the MySQL native JSON data type<\/a>. You should be interested in:<\/p>\n\n\n\n Note<\/u>:<\/p> I would recommend you to have a closer look at JSON_TABLE<\/a> function, that extract data from a JSON document and returns it as a relational table… It’s just amazing!<\/p><\/blockquote>\n\n\n\n However MySQL 8.0 provides another way to handle JSON documents, actually in a \u00ab\u00a0Not only SQL<\/strong>\u00a0\u00bb (NoSQL<\/a>) approach… Let’s have a quick overview of the architecture.<\/p>\n\n\n\n As a disclaimer, I am not a developer, so sorry no fancy code in this blog post. https:\/\/insidemysql.com\/<\/em><\/strong><\/a><\/p>\n\n\n\n And to start, I recommend to focus on the following articles:<\/p>\n\n\n\n And of course the newest articles as well. X DevAPI User Guide<\/em><\/strong><\/a><\/p>\n\n\n\n\n\n If you are a DBA, OPS and obviously a developer, the simplest way to use (or test) MySQL Document Store, is with MySQL Shell.<\/p>\n\n\n\n MySQL Shell<\/a> is an integrated development & administration shell where all MySQL products will be available through a common scripting interface. MySQL Shell key features are :<\/p>\n\n\n\n Note:<\/p> MySQL Shell is also a key component of MySQL InnoDB Cluster<\/strong><\/a>. In this context, it allows you to deploy and manager a MySQL Group Replication<\/strong><\/a> cluster.<\/p> See my MySQL InnoDB Cluster tutorial<\/a>.<\/p><\/blockquote>\n\n\n\n Let’s connect to the MySQL Server with MySQL Shell (mysqlsh) <\/p>\n\n\n\n We must be inside a X session in order to use MySQL as a document store. Luckily there is no extra step, because it’s the default in MySQL 8.0. Note that the default \u00ab\u00a0X\u00a0\u00bb port is 33060. If you are connected inside a classic session, you’ll get the following input (note \u00ab\u00a0<ClassicSession instead of <Session\u00a0\u00bb) :<\/p>\n\n\n\n You can know what is you X protocol port by checking mysqlx_port<\/a> variable. Then reconnect to the server using the right port (33060 by default) and you should be fine :<\/p>\n\n\n\n We are going to create a schema (demo<\/em>) where we will do our tests<\/p>\n\n\n\n Note<\/u>:<\/p> The MySQL Shell default language is JavaScript. However, all the steps described in this article can also be done in Python.<\/p> e.g. <\/p> JS> session.createSchema(‘demo’) <\/p> Py> session.create_schema(‘demo’) Create a collection (my_coll1<\/em>) insert the schema demo<\/em> and insert documents :<\/p>\n\n\n\n Trying to add a non valid<\/u> JSON document raise an error :<\/p>\n\n\n\n To get the list of collections belonging to the current schema use getCollections<\/a>() :<\/p>\n\n\n\n Display the content of a collection with find<\/a>() :<\/p>\n\n\n\n Note<\/u>:<\/p> Each document requires an identifier field called _id<\/em><\/strong>. The value of the _id field must be unique among all documents in the same collection. Please read: Understanding Document IDs<\/a>.<\/p><\/blockquote>\n\n\n\n You can execute many operations on your document. One practical way to get the list of available functions is to press the <TAB><\/em><\/strong> key, to ask for auto-completion, after the dot \u00ab\u00a0.\u00a0\u00bb You can also use the awesome MySQL Shell built-in help (I strongly recommend my colleague Jesper<\/a>‘s article<\/a>) and please bookmark is blog<\/a>. You’ll need the modify<\/a>() function :<\/p>\n\n\n\n You can also modify the structure of a document by remove a key and its content with modify() and unset().<\/p>\n\n\n\n We are missing one last important operation, delete documents with remove<\/a>()<\/p>\n\n\n\n You can also remove all documents in a collection with one command. To do so, use the remove(\u00ab\u00a0true\u00a0\u00bb) method without specifying any search condition. Let’s work with a bigger JSON collection. You can find the JSON file source here<\/a>. <\/p>\n\n\n\n By the way you can import data from MongoDB to MySQL<\/strong> \\o\/<\/p>\n\n\n\n No more excuses to finally get rid of MongoDB \ud83d\ude09<\/p>\n\n\n\n Let’s do some queries…<\/p>\n\n\n\n Display 1 document<\/u><\/p>\n\n\n\n Looks like data relative to a famous TV show<\/a> \ud83d\ude42<\/p>\n\n\n\n\n\n All episodes from season 1<\/u><\/p>\n\n\n\n First episode of each season<\/u><\/p>\n\n\n\n A common pattern with document store datastores is to repeatedly execute the same (or similar) kind of simple queries (e.g. \u00ab\u00a0id<\/em>\u00a0\u00bb based lookup). For example if your application often use the following query:<\/p>\n\n\n\n So it’s probably a good idea to use prepared statements. Then bind the value to the parameter :<\/p>\n\n\n\n Simply powerful!<\/p>\n\n\n\n\n\n Indeed relevant indexes is a common practice to improve performances. MySQL Document Store allows you to index your keys<\/strong> inside the JSON document.<\/p>\n\n\n\n Add a composite Index on keys
A JSON document is a standardized object that can represent structured data. And the structure is implicit in the document.
Anyway, I bet you know that too!<\/em><\/p>\n\n\n\n
We also provides a large set of JSON functions<\/a>.
I hope you were aware of that!<\/em><\/p>\n\n\n\n
In other words, if you need\/want to manage JSON documents (collections) in a non-relational manner, with CRUD<\/a> (acronym for C<\/strong>reate\/R<\/strong>ead\/U<\/strong>pdate\/D<\/strong>elete) operations then you can use MySQL 8.0!
Did you know that?<\/em><\/p>\n\n\n\n\n\nMySQL Document Store Architecture<\/h2>\n\n\n\n
![\"MySQL](\"https:\/\/i0.wp.com\/dasini.net\/blog\/wp-content\/uploads\/MySQL_8.0_Document_Store_Architecture.png\")
<\/figcaption><\/figure><\/div>\n\n\n\nWrite application using X DevAPI<\/h2>\n\n\n\n
However the good news is that I can show you were you’ll be able to found the best MySQL developer resources ever \ud83d\ude42 that is :<\/p>\n\n\n\n
Furthermore, another resource that would be useful to you is the<\/p>\n\n\n\nUse Document Store with MySQL Shell<\/h2>\n\n\n\n
If you don’t know it yet, please download it<\/a>.
Trust me you are going to love it !<\/p>\n\n\n\n![\"MySQL](\"https:\/\/i0.wp.com\/dasini.net\/blog\/wp-content\/uploads\/MySQL_Shell_architecture.png\")
<\/figcaption><\/figure><\/div>\n\n\n\nFirst steps with MySQL Shell<\/h3>\n\n\n\n
$ mysqlsh root@myHost\nMySQL Shell 8.0.15\n... snip ...\nYour MySQL connection id is 15 (X protocol)\nServer version: 8.0.15 MySQL Community Server - GPL\nNo default schema selected; type \\use <schema> to set one.<\/code><\/pre>\n\n\n\n
You can check that you are inside a X session thus using X protocol<\/p>\n\n\n\nMySQL myHost:33060+ JS> session\n<Session:root@myHost:33060>\n\nMySQL myHost:33060+ JS> \\status\n...snip...\n\nSession type: X\n\nDefault schema: \nCurrent schema: \nServer version: 8.0.15 MySQL Community Server - GPL\nProtocol version: X protocol\n...snip...<\/code><\/pre>\n\n\n\n\n\nMySQL myHost:3306 JS> session\n<ClassicSession:root@myHost:3306><\/code><\/pre>\n\n\n\n
I’ll switch to the MySQL Shell SQL mode to execute my SQL command:<\/p>\n\n\n\nMySQL myHost:3306 JS> \\sql\nSwitching to SQL mode... Commands end with ;\n\nMySQL myHost:3306 SQL> SHOW VARIABLES LIKE 'mysqlx_port';\n+---------------+-------+\n| Variable_name | Value |\n+---------------+-------+\n| mysqlx_port | 33060 |\n+---------------+-------+<\/code><\/pre>\n\n\n\nMySQL myHost:3306 SQL> \\connect root@myHost:33060 \n...snip...\nYour MySQL connection id is 66 (X protocol)\nServer version: 8.0.15 MySQL Community Server - GPL\nNo default schema selected; type \\use <schema> to set one.\n\nMySQL myHost:33060+ SQL> \\js\nSwitching to JavaScript mode...\n\nMySQL myHost:33060+ JS> session\n<Session:root@myHost:33060><\/code><\/pre>\n\n\n\n\n\nCRUD<\/h3>\n\n\n\n
MySQL myHost:33060+ JS> session.createSchema('demo')\n<Schema:demo>\n\n\nMySQL myHost:33060+ JS> \\use demo\nDefault schema `demo` accessible through db.<\/code><\/pre>\n\n\n\n
<\/p><\/blockquote>\n\n\n\nCreate documents<\/h4>\n\n\n\n
MySQL myHost:33060+ demo JS> db.createCollection('my_coll1');\n<Collection:my_coll1>\n\n\nMySQL myHost:33060+ demo JS> db.my_coll1.add({\"title\":\"MySQL Document Store\", \"abstract\":\"SQL is now optional!\", \"code\": \"42\"})\nQuery OK, 1 item affected (0.0358 sec)<\/code><\/pre>\n\n\n\nMySQL myHost:33060+ demo JS> db.my_coll1.add(\"This is not a valid JSON document\")\nCollectionAdd.add: Argument #1 expected to be a document, JSON expression or a list of documents (ArgumentError)<\/code><\/pre>\n\n\n\nList collections<\/h4>\n\n\n\n
MySQL myHost:33060+ demo JS> db.getCollections()\n[\n <Collection:my_coll1>\n]<\/code><\/pre>\n\n\n\nFind documents<\/h4>\n\n\n\n
MySQL myHost:33060+ demo JS> db.my_coll1.find()\n[\n {\n \"_id\": \"00005c9514e60000000000000053\",\n \"code\": \"42\",\n \"title\": \"MySQL Document Store\",\n \"abstract\": \"SQL is now optional!\"\n }\n]\n1 document in set (0.0029 sec)<\/code><\/pre>\n\n\n\n
MySQL server sets an _id value if the document does not contain the _id field.<\/p>
For example, type db.my_coll1.<\/strong> then press <TAB><\/em><\/strong>twice, you’ll get the following result:<\/p>\n\n\n\nMySQL myHost:33060+ demo JS> db.my_coll1.\nadd() count() dropIndex() find() getOne() getSession() modify() remove() replaceOne() session\naddOrReplaceOne() createIndex() existsInDatabase() getName() getSchema() help() name removeOne() schema<\/code><\/pre>\n\n\n\n
Last but not least our documentations: X DevAPI User Guide<\/a>, MySQL Shell JavaSCript API Reference<\/a> & MySQL Shell Python API reference<\/a>.<\/p>\n\n\n\nModify documents<\/h4>\n\n\n\n
MySQL myHost:33060+ demo JS> db.my_coll1.find(\"_id='00005c9514e60000000000000053'\").fields(\"code\")\n[\n {\n \"code\": \"42\"\n }\n]\n\n\nMySQL myHost:33060+ demo JS> db.my_coll1.modify(\"_id='00005c9514e60000000000000053'\").set(\"code\",\"2019\")\nQuery OK, 1 item affected (0.0336 sec)\nRows matched: 1 Changed: 1 Warnings: 0\n\n\nMySQL myHost:33060+ demo JS> db.my_coll1.find(\"_id='00005c9514e60000000000000053'\").fields(\"code\")\n[\n {\n \"code\": \"2019\"\n }\n]<\/code><\/pre>\n\n\n\nRemove content from documents<\/h4>\n\n\n\n
MySQL myHost:33060+ demo JS> db.my_coll1.add({\"title\":\"Quote\", \"message\": \"Strive for greatness\"})\nQuery OK, 1 item affected (0.0248 sec)\n\nMySQL myHost:33060+ demo JS> db.my_coll1.find()\n[\n {\n \"_id\": \"00005c9514e60000000000000053\",\n \"code\": \"42\",\n \"title\": \"MySQL Document Store\",\n \"abstract\": \"SQL is now optional!\"\n },\n {\n \"_id\": \"00005c9514e60000000000000054\",\n \"title\": \"Quote\",\n \"message\": \"Strive for greatness\"\n }\n]\n2 documents in set (0.0033 sec)\n\nMySQL myHost:33060+ demo JS> db.my_coll1.modify(\"_id='00005c9514e60000000000000054'\").unset(\"title\")\nQuery OK, 1 item affected (0.0203 sec)\n\nRows matched: 1 Changed: 1 Warnings: 0\n\nMySQL myHost:33060+ demo JS> db.my_coll1.find(\"_id='00005c9514e60000000000000054'\")\n[\n {\n \"_id\": \"00005c9514e60000000000000054\",\n \"message\": \"Strive for greatness\"\n }\n]<\/code><\/pre>\n\n\n\nRemove documents<\/h4>\n\n\n\n
MySQL myHost:33060+ demo JS> db.my_coll1.remove(\"_id='00005c9514e60000000000000054'\")\nQuery OK, 1 item affected (0.0625 sec)\n\n\nMySQL myHost:33060+ demo JS> db.my_coll1.find(\"_id='00005c9514e60000000000000054'\")\nEmpty set (0.0003 sec)<\/code><\/pre>\n\n\n\n
Obviously it is usually not a good practice…<\/p>\n\n\n\n\n\nImport JSON dcouments<\/h3>\n\n\n\n
MySQL Shell provide a very convenient tool, named importJson<\/a><\/strong>(), to easily import JSON documents inside your MySQL<\/strong> Server either in the form of collection or table.<\/p>\n\n\n\nMySQL myHost:33060+ demo JS> db.getCollections()\n[\n <Collection:my_coll1>\n]\n\n\nMySQL myHost:33060+ demo JS> util.importJson('GoT_episodes.json')\nImporting from file \"GoT_episodes.json\" to collection `demo`.`GoT_episodes` in MySQL Server at myHost:33060\n\n.. 73.. 73\nProcessed 47.74 KB in 73 documents in 0.1051 sec (694.75 documents\/s)\nTotal successfully imported documents 73 (694.75 documents\/s)\n\n\nMySQL myHost:33060+ demo JS> db.getCollections()\n[\n <Collection:GoT_episodes>, \n <Collection:my_coll1>\n]<\/code><\/pre>\n\n\n\n
Note that I had to do an extra step before import the data:
sed ‘s\/}}},{\u00ab\u00a0id\u00a0\u00bb\/}}} {\u00ab\u00a0id\u00a0\u00bb\/g’<\/strong> got_episodes.json.BAK > got_episodes.json<\/p>\n\n\n\nMySQL myHost:33060+ demo JS> db.GoT_episodes.find().limit(1)\n[\n {\n \"id\": 4952,\n \"_id\": \"00005c9514e6000000000000009e\",\n \"url\": \"http:\/\/www.tvmaze.com\/episodes\/4952\/game-of-thrones-1x01-winter-is-coming\",\n \"name\": \"Winter is Coming\",\n \"image\": {\n \"medium\": \"http:\/\/static.tvmaze.com\/uploads\/images\/medium_landscape\/1\/2668.jpg\",\n \"original\": \"http:\/\/static.tvmaze.com\/uploads\/images\/original_untouched\/1\/2668.jpg\"\n },\n \"_links\": {\n \"self\": {\n \"href\": \"http:\/\/api.tvmaze.com\/episodes\/4952\"\n }\n },\n \"number\": 1,\n \"season\": 1,\n \"airdate\": \"2011-04-17\",\n \"airtime\": \"21:00\",\n \"runtime\": 60,\n \"summary\": \"<p>Lord Eddard Stark, ruler of the North, is summoned to court by his old friend, King Robert Baratheon, to serve as the King's Hand. Eddard reluctantly agrees after learning of a possible threat to the King's life. Eddard's bastard son Jon Snow must make a painful decision about his own future, while in the distant east Viserys Targaryen plots to reclaim his father's throne, usurped by Robert, by selling his sister in marriage.<\/p>\",\n \"airstamp\": \"2011-04-18T01:00:00+00:00\"\n }\n]<\/code><\/pre>\n\n\n\nMySQL myHost:33060+ demo JS> db.GoT_episodes.find(\"season=1\").fields(\"name\", \"summary\", \"airdate\").sort(\"number\")\n[\n {\n \"name\": \"Winter is Coming\",\n \"airdate\": \"2011-04-17\",\n \"summary\": \"<p>Lord Eddard Stark, ruler of the North, is summoned to court by his old friend, King Robert Baratheon, to serve as the King's Hand. Eddard reluctantly agrees after learning of a possible threat to the King's life. Eddard's bastard son Jon Snow must make a painful decision about his own future, while in the distant east Viserys Targaryen plots to reclaim his father's throne, usurped by Robert, by selling his sister in marriage.<\/p>\"\n },\n {\n \"name\": \"The Kingsroad\",\n \"airdate\": \"2011-04-24\",\n \"summary\": \"<p>An incident on the Kingsroad threatens Eddard and Robert's friendship. Jon and Tyrion travel to the Wall, where they discover that the reality of the Night's Watch may not match the heroic image of it.<\/p>\"\n },\n {\n \"name\": \"Lord Snow\",\n \"airdate\": \"2011-05-01\",\n \"summary\": \"<p>Jon Snow attempts to find his place amongst the Night's Watch. Eddard and his daughters arrive at King's Landing.<\/p>\"\n },\n {\n \"name\": \"Cripples, Bastards, and Broken Things\",\n \"airdate\": \"2011-05-08\",\n \"summary\": \"<p>Tyrion stops at Winterfell on his way home and gets a frosty reception from Robb Stark. Eddard's investigation into the death of his predecessor gets underway.<\/p>\"\n },\n {\n \"name\": \"The Wolf and the Lion\",\n \"airdate\": \"2011-05-15\",\n \"summary\": \"<p>Catelyn's actions on the road have repercussions for Eddard. Tyrion enjoys the dubious hospitality of the Eyrie.<\/p>\"\n },\n {\n \"name\": \"A Golden Crown\",\n \"airdate\": \"2011-05-22\",\n \"summary\": \"<p>Viserys is increasingly frustrated by the lack of progress towards gaining his crown.<\/p>\"\n },\n {\n \"name\": \"You Win or You Die\",\n \"airdate\": \"2011-05-29\",\n \"summary\": \"<p>Eddard's investigations in King's Landing reach a climax and a dark secret is revealed.<\/p>\"\n },\n {\n \"name\": \"The Pointy End\",\n \"airdate\": \"2011-06-05\",\n \"summary\": \"<p>Tyrion joins his father's army with unexpected allies. Events in King's Landing take a turn for the worse as Arya's lessons are put to the test.<\/p>\"\n },\n {\n \"name\": \"Baelor\",\n \"airdate\": \"2011-06-12\",\n \"summary\": \"<p>Catelyn must negotiate with the irascible Lord Walder Frey.<\/p>\"\n },\n {\n \"name\": \"Fire and Blood\",\n \"airdate\": \"2011-06-19\",\n \"summary\": \"<p>Daenerys must realize her destiny. Jaime finds himself in an unfamiliar predicament.<\/p>\"\n }\n]<\/code><\/pre>\n\n\n\n\n\nMySQL myHost:33060+ demo JS> db.GoT_episodes.find(\"number=1\").fields(\"name\", \"airdate\", \"season\").sort(\"season\")\n[\n {\n \"name\": \"Winter is Coming\",\n \"season\": 1,\n \"airdate\": \"2011-04-17\"\n },\n {\n \"name\": \"The North Remembers\",\n \"season\": 2,\n \"airdate\": \"2012-04-01\"\n },\n {\n \"name\": \"Valar Dohaeris\",\n \"season\": 3,\n \"airdate\": \"2013-03-31\"\n },\n {\n \"name\": \"Two Swords\",\n \"season\": 4,\n \"airdate\": \"2014-04-06\"\n },\n {\n \"name\": \"The Wars to Come\",\n \"season\": 5,\n \"airdate\": \"2015-04-12\"\n },\n {\n \"name\": \"The Red Woman\",\n \"season\": 6,\n \"airdate\": \"2016-04-24\"\n },\n {\n \"name\": \"Dragonstone\",\n \"season\": 7,\n \"airdate\": \"2017-07-16\"\n },\n {\n \"name\": \"TBA\",\n \"season\": 8,\n \"airdate\": \"2019-04-14\"\n }\n]\n8 documents in set (0.0047 sec)<\/code><\/pre>\n\n\n\n\n\nCRUD Prepared Statements<\/h3>\n\n\n\n
These queries can be accelerated using <\/strong>prepared (CRUD) statements<\/strong><\/a>.<\/p>\n\n\n\nMySQL myHost:33060+ demo JS> db.GoT_episodes.find(\"number=1 AND season=1\").fields(\"name\", \"airdate\")\n[\n {\n \"name\": \"Winter is Coming\",\n \"airdate\": \"2011-04-17\"\n }\n]<\/code><\/pre>\n\n\n\n
First we need to prepare the query:<\/p>\n\n\n\n\/\/ Prepare a statement using a named parameter\nvar gotEpisode = db.GoT_episodes.find(\"number = :episodeNum AND season = :seasonNum\").fields(\"name\", \"airdate\")<\/code><\/pre>\n\n\n\nMySQL myHost:33060+ demo JS> gotEpisode.bind('episodeNum', 1).bind('seasonNum', 1)\n[\n {\n \"name\": \"Winter is Coming\",\n \"airdate\": \"2011-04-17\"\n }\n]<\/code><\/pre>\n\n\n\nMySQL myHost:33060+ demo JS> gotEpisode.bind('episodeNum', 7).bind('seasonNum', 3)\n[\n {\n \"name\": \"The Bear and the Maiden Fair\",\n \"airdate\": \"2013-05-12\"\n }\n]<\/code><\/pre>\n\n\n\nIndex<\/h3>\n\n\n\n