MySQL’s Document Store – a quick-guide to storing JSON documents in MySQL using...

MySQL’s Document Store – a quick-guide to storing JSON documents in MySQL using JavaScript and Python (and even SQL!)

October 24, 2019 Leave a comment

MySQL introduced a JSON data type in version 5.7 , and expanded the functionality in version 8.0 .

Besides being able to store native JSON in MySQL , you can also use MySQL as a document store (doc store) to store JSON documents. And, you can use NoSQL CRUD (create, read, update and delete) commands in either JavaScript or Python .

This post will show you some of the basic commands for using MySQL as a document store .

Requirements

To use MySQL as a document store, you will need to use the following server features:

The X Plugin enables the MySQL Server to communicate with clients using the X Protocol , which is a prerequisite for using MySQL as a document store. The X Plugin is enabled by default in MySQL Server as of MySQL 8.0 . For instructions to verify X Plugin installation and to configure and monitor X Plugin , see Section 20.5, “X Plugin” .

The X Protocol supports both CRUD and SQL operations, authentication via SASL , allows streaming (pipelining) of commands and is extensible on the protocol and the message layer. Clients compatible with X Protocol include MySQL Shell and MySQL 8.0 Connectors .

Clients that communicate with a MySQL Server using X Protocol can use X DevAPI to develop applications. X DevAPI offers a modern programming interface with a simple yet powerful design which provides support for established industry standard concepts. This chapter explains how to get started using either the JavaScript or Python implementation of X DevAPI in MySQL Shell as a client. See X DevAPI for in-depth tutorials on using X DevAPI .

( source: https://dev.mysql.com/doc/refman/8.0/en/document-store.html )

And, you will need to have MySQL version 8.0.x (or higher) installed, as well as the MySQL Shell version 8.0.x (or higher). For these examples, I am using version 8.0.17 of both. (You could use version 5.7.x, but I have not tested any of these commands in 5.7.x)

Starting MySQL Shell (mysqlsh)

When starting MySQL Shell (Shell), you have two session options . The default option is mysqlx (‐‐mx) , and this allows the session to connect using the X Protocol . The other option when starting Shell is ‐‐mysql , which establishes a “Classic Session” and connects using the standard MySQL protocol. For this post, I am using the default option of mysqlx (‐‐mx) . There are other MySQL Shell command-line options for the X Protocol available. And, there are specific X Protocol variables which may need to be set for the mysqlx connection.

Here is a list of all of the MySQL Shell commands and their shortcuts (for MySQL version 8.0).

( source: https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-shell-commands.html )

Command Alias/Shortcut Description \help \h or \? Print help about MySQL Shell, or search the online help. \quit \q or \exit Exit MySQL Shell. \ In SQL mode, begin multiple-line mode. Code is cached and executed when an empty line is entered. \status \s Show the current MySQL Shell status. \js Switch execution mode to JavaScript. \py Switch execution mode to Python. \sql Switch execution mode to SQL. \connect \c Connect to a MySQL Server. \reconnect Reconnect to the same MySQL Server. \use \u Specify the schema to use. \source \. Execute a script file using the active language. \warnings \W Show any warnings generated by a statement. \nowarnings \w Do not show any warnings generated by a statement. \history View and edit command line history. \rehash Manually update the autocomplete name cache. \option Query and change MySQL Shell configuration options. \show Run the specified report using the provided options and arguments. \watch Run the specified report using the provided options and arguments, and refresh the results at regular intervals. \edit \e Open a command in the default system editor then present it in MySQL Shell. \system \! Run the specified operating system command and display the results in MySQL Shell.

To start the MySQL Shell (Shell), you simply execute themysqlshcommand from a terminal window. The default mode is JavaScript (as shown by the JS in the prompt).

By starting Shell without any variables, you will need to connect to a database instance. You do this with the\connectcommand, or you can use the\cshortcut. The syntax is\c user@ip_address:

Or, to start Shell with the connection information, specify the user and host IP address. The syntax ismysqlsh user@ip_address:

You may find a list of all of the command-line options at https://dev.mysql.com/doc/mysql-shell/8.0/en/mysqlsh.html .

The Shell prompt displays the connection information, whether or not you are using ssl, and your current mode (there are three modes – JavaScript , Python and SQL). In the earlier example, you are in the (default) JavaScript mode. You can also get your session information with the session command:

All of these commands are case-sensitive, so if you type an incorrect command, you will see the following error:

Here is how you switch between the three modes: – JavaScript , Python and SQL.

There are also several different output formats. You may change the output using theshell.options.set command. The default istable. Here are examples of each one, using the same command:

table output format

JSON output format

tabbed format

vertical format

MySQL Shell – Create & Drop Schema

Note: With the MySQL Doc Store, the terms to describe the database, table and rows are different. The database is called theschema(even thought the doc store is “schema-less”). The tables are calledcollections, and the rows of data are calleddocuments.

To create a schema namedtest1, use thecreateSchemacommand:

To get a list of the current schemas, use thegetSchemascommand:

Also, you can run SQL commands inside of the Doc Store – something you can't natively do with most other NoSQL databases. Instead of using thegetSchemascommand, you can issue aSHOW DATABASESSQL command using therunSqlNoSQL command.

To drop a schema namedtest1, you use thedropSchemacommand:

Just like with MySQL, you have to select the schema (database) you want to use. This shows you how to create a schema and set it as your default schema. With the\usecommand, you are really setting a variable nameddbto equal the default schema. So, after you have set your schema with the\usecommand, when you issue thedbcommand, you can see the default schema.

To change the value of the variabledb, you may use the\usecommand or you may set the value ofdbusing thevar(variable) command. The commandsession.getSchemawill return a schema value, but it does not automatically set thedbvariable value.)

You can also create your own variables. Here is an example of setting the variablesdbto equal the SQL commandSHOW DATABASES:

But the variables are only good for your current session. If you quit Shell, log back in, and re-run the variable, you will get an error stating the variable is not defined.

After you have created your schema (database), and selected it via\usecommand, then you can create a collection (table) and insert JSON documents into the collection. I will create a collection namedtest1.

You can get a list of collections by using thegetCollectionscommand, and you can also execute SQL commands with thesession.runSqlcommand. ThegetCollectionscommand is the same as the SQL commandSHOW TABLES.

To drop a collection, use thedropCollectioncommand. You can verify the collection was dropped by using thegetCollectionscommand again.

If you have not selected a default database, you may also specify the schema name prior to the collection name (which is the same type of syntax when you create a table inside of a database in MySQL). You will notice the output is different, as when you specify the schema name before the collection name, that schema name is returned as well.

To add a JSON document to a collection, you use theaddcommand. You must specify a collection prior to using theaddcommand. (You can't issue a command likeworkshop.foods.add.) You can add the document with or without the returns and extra spaces (or tabs). Here the JSON document to add to the collection namedworkshop:

Note: Some JSON formats require double quotes around the keys/strings. In this example,Name_First

has double quotes in the first example, and the second example doesn’t have double quotes. Both formats will work in Shell.

The->in the examples below is added by Shell. You don’t need to type this on the command line.

Here are examples of each method of adding a JSON document – one where all of the data in the JSON document is on one line, and another where the JSON document contains data on multiple lines with returns and spaces included.

So far, I have created a schema, and a collection, and I have added one document to the collection. Next, I will show you how to perform searches using thefindcommand.

Searching Documents

To find all records in a collection, use thefindcommand without specifying any search criteria:

Note: The_idkey in the output above is automatically added to each document and the value of_idcannot be changed. Also, an index is automatically created on the_idcolumn. You can check the index using the SQLSHOW INDEXcommand. (To make the output easier to view, I switched the format toverticaland I switched it back totable)

Here is how you perform a search by specifying the search criteria. This command will look for the first name ofFoxvia theName_First

key.

And, if your search result doesn't find any matching documents, you will get a return ofEmpty setand the time it took to run the query:

The first search returned all of the keys in the document because I didn’t specify any search criteria inside thefindcommand –db.foods.find(). This example is how you retrieve just a single key inside a document. And notice the keyfavorite_foodcontains a list of sub-keys inside of a key.

To return multiple keys, add the additional keys inside thefieldssection, separated by a comma.

Note: The fields are returned in the order they are stored in the document, and not in the order of thefields

section. The query field order does not guarantee the same display order.

To return a sub-key from a list, you add the key prior to the sub-key value in thefieldssection. This search will return the value for theBreakfastsub-key in thefavorite_food

key.

Modifying Documents

To modify a document, you use themodifycommand, along with search criteria to find the document(s) you want to change. Here is the original JSON document.

I am going to change Fox'sfavorite_foodsub-keys toLunchbeing“soup in a bread bowl”andDinnerto“steak and broccoli”.

I can see the changes by issuing afindcommand, searching forName_Firstequal to“Fox”.

Notice that theBreakfastsub-key is no longer in the list. This is because I changed thefavorite_foodlist to only includeLunchandDinner.

To change only one sub-key underfavorite_food, such asDinner, I can do that with thesetcommand:

If I want to remove a single sub-key under favorite_food, I can use theunsetcommand. I will remove theDinnersub-key:

The keyfavorite_foodcontained a list, but a key can also contain an array. The main format difference in an array and a list is an array has opening and closing square brackets[ ]. The brackets go on the outside of the values for the array.

Here is the original document with a list forfavorite_food:

I am going to delete the document forFox, confirm the deletion occurred and insert a new document – but this time, I am going to use an array forfavorite_food(and notice the square brackets).

When dealing with an array, I can modify each element in the array, or I can add another array element. I am going to addFox'sfavoriteSnackto the array with thearrayAppendcommand:

The keyfavorite_foodnow contains an array with two separate values in it. The sub-keys in the array positionfavorite_food[0]contains values forLunch,BreakfastandDinnervalues, while the array positionfavorite_food[1]only contains theSnackvalue.

Note: If you aren't familiar with arrays, an array is like a list that contains elements. Elements of the array are contained in memory locations relative to the beginning of the array. The first element in the array is actually zero (0) elements away from the beginning of the array. So, the placement of the first element is denoted as array position zero (0) and this is designated by[0]

. Most programming languages have been designed this way, so indexing from zero (0) is pretty much inherent to most languages.

I can now delete an element in an array with thearrayDeletecommand. I am going to remove theSnackarray member, which isfavorite_food[1]

.

Modifying a document – adding a key

If I want to add an extra key, I need to add a few more variables and their values. I will need to define myschemaandcollection. Then I can use thepatchcommand to add a key to an existing document.

I am going to add a middle name toFox'sdocument.

Importing Large Data Sets

In order to demonstrate indexes, I will need to import a large amount of data. Before I import data, I need to be sure that I have a variable set for themysqlxclient protocol to allow larger client packets. Specifically, I need to set themysqlx_max_allowed_packetvariable to the largest allowable size of1TB. You can set this variable in the MySQL configuration file (my.cnformy.ini) and reboot the MySQL instance, or you can set it for your session.

I can check the values of themysqlx_max_allowed_packetvariable from within Shell, and if it isn't set to1TB, I will modify it for this session. (I can see the value formysqlx_max_allowed_packetis set to100MBor 100 megabytes)

I don't want to write this large import to the binary log (I havebinlogenabled), so I can use the SQL command to SET SQL_LOG_BIN = 0 first. I am going to create a new collection namedproject, and then import a 400+ megabyte JSON file into the new collection.

As you can see from the output above, I imported 2,613,346 documents.

Note: This imported JSON document contains public data regarding businesses in the State of Washington.

Now that I have my 2.6 million documents in the database, I will do a quick search and limit the results to one record by using thelimitcommand. This will show you the keys in the document.

Again – notice an index for the key_idhas automatically been added:

To demonstrate indexes on a large dataset, I will perform two searches against all documents in the project collection – whereLastNameis equal toFRISCHand whereLastNameis equal toVARNELL. Then, I will create the index onLastName, and re-run the two find queries. Note: I am not displaying all of the returned documents to save space.

The searches took1.3559and1.0854seconds, respectively. I can now create an index onLastName. When I create an index, I have to specify the data type for that particular key. And for a text key, I have to specify how many characters of that key I want to include in the index. ( Note: seeTEXT(20)in the command below)

Now I will re-run the same two queries whereLastNameequalsFRISCHand whereLastNameequalsVARNELL.

The queries ran much faster with an index –0.0097seconds and0.0008seconds. Not bad for searching 2.6 million records.

Note:

The computer I was using for this post is a Hackintosh, running Mac OS 10.13.6, with an Intel i7-8700K 3.7GHz processor with six cores, with 32GB 2666MHz DDR4 RAM, and an SATA III 6 Gb/s, M.2 2280 SSD. Your performance results may vary.

And I can take a look at the index forLastName

:

I can create an index on multiple columns as well. Here is an index created on theStateandZipfields.

I can take a look at the index as well (the other index results were removed to save space).

I will run a query looking for the first entryFRISHbased upon his state(WA)and Zip(99205). The query result time is still pretty good, even though I am also returning hisLastName.

To drop an index, use thedropIndexcommand:

NoSQL and SQL in the same database

The great advantage to using MySQL to store JSON documents is that the data is stored in theInnoDBstorage engine. So, the NoSQL document store database has features and benefits ofInnoDB– such as transactions and ACID-compliance. This also means that you can use MySQL features like replication, group replication, transparent data encryption, etc. And if you need to restore a backup and play back the binlog files for a point-in-time recovery, you can do that as well, as all of the NoSQL transactions are written to the MySQL Binary Log. Here is what a NoSQL transaction looks like in the MySQL binary log:

NoSQL:

MySQL Binlog:

Finally, here is an example of how to do a transaction:

JSON functions

Since the MySQL Document Store utilizes the MySQL database server and since the documents are stored inInnoDB, you can also use MySQL SQL JSON functions as well to manipulate the data stored in either a JSON document or in a JSON data type. Here is a list of the JSON functions available, and while JSON functions were introduced in 5.7, not all of these functions will be in 5.7 – but they are all in version 8.0.

JSON_ARRAY JSON_ARRAY_APPEND JSON_ARRAY_INSERT JSON_CONTAINS JSON_CONTAINS_PATH JSON_DEPTH JSON_EXTRACT JSON_INSERT JSON_KEYS JSON_LENGTH JSON_MERGE_PATCH JSON_MERGE_PRESERVE JSON_OBJECT JSON_OVERLAPS JSON_PRETTY JSON_QUOTE JSON_REMOVE JSON_REPLACE JSON_SCHEMA_VALID JSON_SCHEMA_VALIDATION_REPORT JSON_SEARCH JSON_SET JSON_STORAGE_FREE JSON_STORAGE_SIZE JSON_TABLE JSON_TYPE JSON_UNQUOTE JSON_VALID MEMBER OF

But you already use Mongo? And you have MySQL as well?

If you already use Mongo and MySQL, and you want to switch to MySQL, or if your DBA's already know Mongo, then moving to MySQL or using the MySQL doc store is pretty easy. The commands used in Mongo are very similar to the ones used in the MySQL Document Store. The login command is similar to the MySQL “regular” client command in that you specify the user and password with the-uand-p.

In MySQL Shell, you put theusernamefollowed by the@and theIP address. And you can specify which database or schema you want to use upon login. Mongo does have a few shortcuts with theshowcommand, as inshow dbs,show schemasorshow collections. But you can do almost the same in MySQL Shell by setting variables to equal certain functions or commands (like I did earlier.)

To create a schemda/database in Mongo, you simply execute theuse database namecommand – and it creates the schema/database if it doesn't exist. The other commands having to do with collections are almost the same.

Where there are differences, they are relatively small. Mongo uses the commandinsertand MySQL usesaddwhen inserting documents. But then, other commands such as theremovedocument command are the same.

As for the last item in the table below, native Mongo can't run any SQL commands (without using a third-party software GUI tool) – so again, if you have MySQL DBA's on staff, the learning curve can be lower because they can use SQL commands if they don't remember the NoSQL commands.

Command Mongo MySQL Document Store (via Shell) Login mongo ‐u ‐p ‐‐database mysqlsh [email protected] ‐‐database Select schema use database_name \use database_name Show schemas/dbs show dbs session.getSchemas() Create schema use database_name session.createSchema() Show collections show collections or db.getCollectionNames(); db.getCollections() Create collection db.createCollection("collectionName"); db.createCollection("collectionName"); Insert document db.insert({field1: "value", field2: "value"}) db.insert({field1: "value", field2: "value"}) Remove document db.remove() db.remove() Run SQL command session.runSql(SQL_command)

So, with MySQL, you have a hybrid solution where you can use both SQL and NoSQL at the same time. And, since you are storing the JSON documents inside MySQL, you an also use the MySQL JSON functions to search, update and delete the same records.

Tony Darnell is a Principal Sales Consultant for MySQL , a division of Oracle , Inc. MySQL is the world’s most popular open-source database program. Tony may be reached at info [at] Scripting MySQL .com and on LinkedIn . Tony is the author of Twenty Forty-Four: The League of Patriots
Visit http://2044thebook.com for more information. Tony is the editor/illustrator for NASA Graphics Standards Manual Remastered Edition
Visit https://amzn.to/2oPFLI0 for more information.

Advertisements