How to Work with JSON Data in MySQL

Introduction

JavaScript Object Notation (JSON) is a popular data-interchange format because it is easy to serialize and deserialize by both humans and machines. JSON is also widely supported by most major programming languages, and there are hundreds of libraries for generating and parsing it.

In MySQL, you can define JSON as a data type when creating table columns. While you can practically store the same JSON data in a text field, the JSON data type has a couple of advantages. One, the optimized storage format enables you to access the document elements efficiently. Two, the fields defined with the JSON data type offer automatic validation ensuring only syntactically valid documents are saved into the database.

In this guide, you'll go over a set of the supported MySQL functions and data types that enable operations of JSON values to provide access to individual JSON key-value pairs. You'll also see the flexibility and power of some of these functions when it comes to validating, creating, searching, and manipulating JSON documents.

Prerequisites

To follow along with this tutorial, you need the following:

• An Ubuntu 20.04 server. While this guide is tested on an Ubuntu 20.04 server, it should work pretty well on any other distribution that supports MySQL.
• A non-root user with sudo privileges.
• A MySQL Server.

1. Create a Sample Database

Connect to your server and log in to your MySQL as root.

$ sudo mysql -u root -p

Next, enter your MySQL root password and press Enter to proceed. Once you're logged in to the MySQL server, execute the statement below to create a sample json_test database.

mysql> CREATE DATABASE json_test;

Output.

Query OK, 1 row affected (0.00 sec)

Switch to the new json_test database.

mysql> USE json_test;

Output.

Database changed

Next, define a customers table. This table will uniquely identify customers using an AUTO_INCREMENT customer_id column, which acts as the PRIMARY KEY. You'll also collect the customer names using the first_name and last_name fields defined with the VARCHAR data type. First, however, you'll capture the customers' address information using the JSON data type. This field will give you the flexibility of accepting different address information, which will be dynamic. For instance, some customers might have a single phone number while others might have two different contacts for their homes and office.

Create the customers table.

mysql> CREATE table customers (
         customer_id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
         first_name VARCHAR(50),
         last_name  VARCHAR(50),
         address    JSON
       ) ENGINE = InnoDB;

Output.

Query OK, 0 rows affected (0.02 sec)

Once you've defined a database and a table, you'll now learn about the different JSON data types supported in MySQL.

2. MySQL Document Data Types

In MySQL, you must write JSON values as strings when executing SQL statements. MySQL parses the text and raises an error if you provide an invalid JSON document. Before you start working with the MySQL JSON functions, familiarize yourself with the following JSON data types:

• JSON string: This is a text-based data type that must be enclosed with quotes("). For instance, in the JSON snippet below, SAMPLE PRODUCT 1 is a JSON string while product_name is a JSON key.

    {
       "product_name":"SAMPLE PRODUCT 1"
    }
  

• JSON number: The JSON number format accepts integers and values with floating decimal points. In the following document 25 and 37.89 are JSON numbers assigned to the cost_price and retail_price values respectively.

    {
       "product_name":"SAMPLE PRODUCT 1",
       "cost_price":25,
       "retail_price":37.89
    }
  

• JSON boolean: This is a data type with only two outcomes. That is, either true or false. You can use this data type in different situations. For instance, in an employees database, you can create an is_married key and set it to either true or false depending on the marital status of a staff member. Also, in a products catalog, you can toggle products features on and off by using the boolean data type as shown below.

    {
       "product_name":"SAMPLE PRODUCT 1",
       "cost_price":25,
       "retail_price":37.89
       "available_for_sale":true
       "is_physical_product":false
       "discounted":false
    }
  

• JSON object: This is a set of key-value pairs enclosed with { and } brackets. All the documents used in the previous examples above are JSON objects. However, the JSON object comes in handy when you're nesting values for a single key. For instance, in the example below, the value of the extended_price key is a JSON object.

    {
       "product_name":"SAMPLE PRODUCT 1",
       "cost_price":25,
       "retail_price":37.89,
       "extended_price":{
          "discounted_price":34.26,
          "whole_sale_price":30.50,
          "shipping_cost":5.21
       }
    }
  

• JSON array: This is a list of values separated by commas and enclosed with square brackets. That is, [ and ]. For instance, to display the attributes of two different products, you can use the following JSON array.

    [
       {
          "product_name":"SAMPLE PRODUCT 1",
          "cost_price":25,
          "retail_price":37.89
       },
       {
          "product_name":"SAMPLE PRODUCT 2",
          "cost_price":180.85,
          "retail_price":256.25
       }
    ]

Now that you're familiar with most JSON data types, you'll now validate and save some sample JSON-based records into the customers table.

3. Validate, Check Type and Save JSON Document to MySQL Table

Once you've decided on the format you want to use in a JSON column, you can obtain your document and insert it into your table. In this demonstration, you'll begin by inserting a sample JOHN DOE's record with the following data.

• first_name:

    JOHN
  

• last_name:

    DOE
  

• address: In this column, you'll capture the street address, town, state, zip, and the different customer's phone number using a JSON object as shown below.

    {
       "street":"97 SIMPLE RD. NW #2",
       "town":"NEW TOWN",
       "state":"AZ",
       "zip":1013,
       "phone":{
          "home":111111,
          "work":222222
       },
       "available_in_day_time":true
    }

Before inserting the data into the customers table, use the MySQL inbuilt JSON_VALID function to check if the document's syntax is valid.

mysql> SELECT JSON_VALID('{
                           "street":"97 SIMPLE RD. NW #2",
                           "town":"NEW TOWN",
                           "state":"AZ",
                           "zip":1013,
                           "phone":{
                              "home":111111,
                              "work":222222
                           },
                           "available_in_day_time":true
                        }') AS is_valid;

The response below confirms the address information above is a valid JSON document.

+----------+
| is_valid |
+----------+
|        1 |
+----------+
1 row in set (0.00 sec)

Next, proceed and save the customer's information into the database alongside the JSON address information. To make it easier to follow the guide, first, put the contact information in an @address variable by executing the command below.

mysql> SET @address = ('{
                          "street":"97 SIMPLE RD. NW #2",
                          "town":"NEW TOWN",
                          "state":"AZ",
                          "zip":1013,
                          "phone":{
                            "home":111111,
                            "work":222222
                          },
                          "available_in_day_time":true
                       }');

Output.

Query OK, 0 rows affected (0.00 sec)

Next, confirm the type of document by using the JSON_TYPE function.

mysql> SELECT JSON_TYPE(@address);

The output below confirms that indeed, the address is a JSON OBJECT.

+---------------------+
| JSON_TYPE(@address) |
+---------------------+
| OBJECT              |
+---------------------+
1 row in set (0.00 sec)

Next, execute the INSERT statement below to save the customer's record and use the @address variable to capture the address information.

mysql> INSERT INTO customers (first_name, last_name, address) VALUES ('JOHN', 'DOE', @address);

MySQL executes your INSERT statement without any errors and displays the output below.

Query OK, 1 row affected (0.01 sec)

Confirm the record by running a SELECT statement against the customers table.

mysql> SELECT
       customer_id,
       first_name,
       last_name,
       address
       FROM customers;

MySQL lists the record as shown below.

+-------------+------------+-----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------+
| customer_id | first_name | last_name | address                                                                                                                                                     |
+-------------+------------+-----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------+
|           1 | JOHN       | DOE       | {"zip": 1013, "town": "NEW TOWN", "phone": {"home": 111111, "work": 222222}, "state": "AZ", "street": "97 SIMPLE RD. NW #2", "available_in_day_time": true} |
+-------------+------------+-----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

As earlier indicated, MySQL restricts saving invalid data into JSON columns. You may test this by trying to insert the following record. First, assign a new value to the address variable and deliberately leave the comma between the town and the state keys, as shown below.

mysql> SET @address = ('{
                          "street":"97 SIMPLE RD. NW #2",
                          "town":"SECOND TOWN"
                          "state":"NY",
                          "zip":5070,
                          "phone":{
                            "home":444444,
                            "work":777777
                          },
                          "available_in_day_time":TRUE
                       }');

Make sure the variable is set by confirming the following output.

Query OK, 0 rows affected (0.00 sec)

Then, try to create a new customer with the set invalid data.

mysql> INSERT INTO customers (first_name, last_name, address) VALUES ('MARY', 'ROE', @address);

MySQL fails the command and displays the following error. If you were saving the JSON data directly into a VARCHAR field, MySQL wouldn't offer any form of validation and you would experience technical errors when you retrieve and try to parse the data later. So, always use the JSON data type when working with JSON documents.

ERROR 3140 (22032): Invalid JSON text: "Missing a comma or '}' after an object member." at position 145 in value for column 'customers.address'.

You can see, the error message above is detailed enough, and it gives you a better insight on correcting your JSON document. However, while this approach works well, it won't validate individual key values against your business logic. Luckily, you'll see how you can define your JSON schema and use it with a CHECK CONSTRAINT to validate individual JSON elements.

4. Validate JSON Document Against a Schema

When defining a JSON column, MySQL offers the flexibility of implementing a CHECK CONSTRAINT for validating individual elements against a schema. To test this functionality, create a simple schema that validates the zip part of the customer's address. For example, in the schema below, specify that a valid zip code must be a number between 1 and 9999 by running the code below.

mysql> SET @valid_zip = '{
                             "type":"object",
                                "properties":{
                                   "zip":{
                                      "type":"number",
                                      "minimum":1,
                                      "maximum":9999
                                   }
                                }
                          }';

Output.

Query OK, 0 rows affected (0.00 sec)

Next, define a new address that violates the rule. In this case, set the zip to a large value of 999999.

mysql> SET @address = ('{
                          "street":"101 2nd RD.",
                          "town":"NEW TOWN",
                          "state":"NJ",
                          "zip":999999,
                          "phone":{
                            "home":444444,
                            "work":888888
                          },
                          "available_in_day_time":true
                       }');

Output.

Query OK, 0 rows affected (0.00 sec)

Now, use the MySQL JSON_SCHEMA_VALID function to test if the address @address conforms to your set schema @valid_zip.

mysql> SELECT JSON_SCHEMA_VALID(@valid_zip, @address);

You can see the output displays 0, meaning the data is invalid. A valid value should return 1.

+-----------------------------------------+
| JSON_SCHEMA_VALID(@valid_zip, @address) |
+-----------------------------------------+
|                                       0 |
+-----------------------------------------+
1 row in set (0.00 sec)

Set a valid zip code in a new address, for instance, 3630.

mysql> SET @address = ('{
                          "street":"101 2nd RD.",
                          "town":"NEW TOWN",
                          "state":"NJ",
                          "zip":3630,
                          "phone":{
                            "home":444444,
                            "work":888888
                          },
                          "available_in_day_time":true
                       }');

Output.

Query OK, 0 rows affected (0.00 sec)

Check if the new address value is within the range by executing the command below.

mysql> SELECT JSON_SCHEMA_VALID(@valid_zip, @address);

The value of 1 below confirms that the value of 3630 is valid for the zip key in the address column.

+-----------------------------------------+
| JSON_SCHEMA_VALID(@valid_zip, @address) |
+-----------------------------------------+
|                                       1 |
+-----------------------------------------+
1 row in set (0.00 sec)

When working in a multi-user database environment, you can't trust that all users will validate the JSON data before running an INSERT statement. In that case, you should implement a CHECK CONSTRAINT when defining your table.

Since you've already created the customers table, alter it using the command below to set the zip code constraint.

mysql> ALTER TABLE customers
       ADD CONSTRAINT zip_validator
       CHECK(JSON_SCHEMA_VALID('{
                                    "type":"object",
                                    "properties":{
                                        "zip":{
                                            "type":"number",
                                            "minimum":1,
                                            "maximum":9999
                                        }
                                 }
                               }', address));

Make sure you receive the following confirmation.

Query OK, 1 row affected (0.02 sec)
Records: 1  Duplicates: 0  Warnings: 0

Next, try to insert a new invalid record into the customers table. Begin by putting the address information into the @address variable.

mysql> SET @address = ('{
                          "street":"1 SAMPLE STREET",
                          "town":"THIRD TOWN",
                          "state":"NY",
                          "zip":10000,
                          "phone":{
                            "home":222222,
                            "work":666666
                          },
                          "available_in_day_time":false
                       }');

Output.

Query OK, 0 rows affected (0.00 sec)

Then, execute the INSERT statement below.

mysql> INSERT INTO customers (first_name, last_name, address) VALUES ('JANE', 'SMITH', @address);

MySQL shows the error below detailing that the CHECK CONSTRAINT set has been violated.

ERROR 3819 (HY000): Check constraint 'zip_validator' is violated.

Change the zip code in the new address to a value within the constraint's set range.

mysql> SET @address = ('{
                          "street":"1 SAMPLE STREET",
                          "town":"THIRD TOWN",
                          "state":"NY",
                          "zip": 7630,
                          "phone":{
                            "home":222222,
                            "work":666666
                          },
                          "available_in_day_time":false
                       }');

Output.

Query OK, 0 rows affected (0.00 sec)

Then, execute the INSERT statement again and check the response.

mysql> INSERT INTO customers (first_name, last_name, address) VALUES ('JANE', 'SMITH', @address);

MySQL should now insert the new row and displays the confirmation output below.

Query OK, 1 row affected (0.01 sec)

In the next step, you'll parse JSON documents from a MySQL table.

5. Parse JSON Document

You can deserialize a JSON document and retrieve the value of any named key using the MySQL JSON_EXTRACT function. For instance, to retrieve the customers' information alongside their individual town names, run the SQL command below in your sample table.

mysql> SELECT
       first_name,
       last_name,
       JSON_EXTRACT(address, '$.town') as town
       FROM customers;

You can see from the output below that the town names have been extracted.

+------------+-----------+--------------+
| first_name | last_name | town         |
+------------+-----------+--------------+
| JOHN       | DOE       | "NEW TOWN"   |
| JANE       | SMITH     | "THIRD TOWN" |
+------------+-----------+--------------+
2 rows in set (0.00 sec)

Similarly, if you want to extract a value of a key nested one level deeper into the JSON document, for instance, the home phone numbers, use the syntax below.

mysql> SELECT
       first_name,
       last_name,
       JSON_EXTRACT(address, '$.phone.home') as phone
       FROM customers;

You now have the home phone numbers as shown below.

+------------+-----------+--------+
| first_name | last_name | phone  |
+------------+-----------+--------+
| JOHN       | DOE       | 111111 |
| JANE       | SMITH     | 222222 |
+------------+-----------+--------+
2 rows in set (0.00 sec)

Using the same path extraction syntax, retrieve the work phone numbers by executing the command below.

mysql> SELECT
       first_name,
       last_name,
       JSON_EXTRACT(address, '$.phone.work') as phone
       FROM customers;

Output.

+------------+-----------+--------+
| first_name | last_name | phone  |
+------------+-----------+--------+
| JOHN       | DOE       | 222222 |
| JANE       | SMITH     | 666666 |
+------------+-----------+--------+
2 rows in set (0.00 sec)

Also, when you want to retrieve JSON values without additional parsing, you can make them more human-readable by using the JSON_PRETTY() function. For instance, to retrieve a list of pretty-printed customers' address information, execute the command below.

mysql> SELECT
       JSON_PRETTY(address)
       FROM customers;

The address information should now be printed in a neat format as shown below.

+---------------------------------------+
| JSON_PRETTY(address)                                                                                                                                                                  |
+---------------------------------------+
| {
  "zip": 1013,
  "town": "NEW TOWN",
  "phone": {
    "home": 111111,
    "work": 222222
  },
  "state": "AZ",
  "street": "97 SIMPLE RD. NW #2",
  "available_in_day_time": true
} |
| {
  "zip": 7630,
  "town": "THIRD TOWN",
  "phone": {
    "home": 222222,
    "work": 666666
  },
  "state": "NY",
  "street": "1 SAMPLE STREET",
  "available_in_day_time": false
}  |
+---------------------------------------+
2 rows in set (0.00 sec)

As you can see from the MySQL results, the JSON functions are working as expected.

Conclusion

In this tutorial, you've created a sample test database and learned all the JSON data types supported by MySQL, including strings, numbers, arrays, objects, and boolean variables. You've also gone through the steps of validating JSON documents against custom schemas. Towards the end, you've parsed and extracted JSON values from the database using an elegant path extraction syntax. Use the knowledge you've gained in this guide next time you work with JSON documents in MySQL.