How to Store JSON Data in MySQL

MySQL allows you to store JSON data in native JSON data type since version 5.7.8. This enables you to store JSON documents easily, and extract JSON data elements without data conversion. In this article, we will look at how to store JSON data in MySQL.

MySQL JSON data type allows you to store JSON data such that you can easily read or search values in it using key or array index, making it really fast. There is no need to convert text to JSON or vice versa for querying data.

How to Store JSON Data in MySQL

We will look at how to store JSON data in MySQL and also how to query columns with JSON data type.

Here’s the syntax to define a JSON column

column_name JSON

Please note, a JSON data type column cannot have default value, nor it can have an index.

Also Read : How to Compare Null Values in MySQL

MySQL JSON Data Type Example

Here’s an example of a table users(id, details) where id is an integer and primary key while details is a JSON data type column.

create table users(
    id int auto_increment primary key,
    details json
   );

Also read : How to Compare Two Rows from Same Table

How to Insert JSON into MySQL

Here’s the SQL query to store JSON document in MySQL.

insert into users(details)
    values(
      '{ "page": "/" , 
         "name": "Safari", 
         "os": "Mac", 
         "spend": 100, 
         "resolution": { "x": 1920, "y": 1080 } }'
),
(
      '{ "page": "/products", 
         "name": "Chrome", 
         "os": "Windows", 
         "spend": 150,
         "resolution": { "x": 1680, "y": 1050 } }'
),
(
     '{ "page": "/shoes", 
        "name": "Firefox", 
        "os": "Windows", 
        "spend": 200,
        "resolution": { "x": 1280, "y": 800 } }'
);

Also read : How to Get every Nth Row in MySQL

We will extract data from JSON columns using column path operator (->)

Here’s an example to retrieve browser names for each user.

mysql> select id, details->'$.name' details from users;
+----+-----------+
| id | details   |
+----+-----------+
|  1 | "Safari"  |
|  2 | "Chrome"  |
|  3 | "Firefox" |
+----+-----------+
3 rows in set (0.00 sec)

You will see that the details columns have double quotes. If you want to remove double quotes, use ->> operator instead

mysql> select id, details->>'$.name' details from users;
+----+-----------+
| id | details   |
+----+-----------+
|  1 |  Safari   |
|  2 |  Chrome   |
|  3 |  Firefox  |
+----+-----------+
3 rows in set (0.00 sec)

You can also aggregate JSON data GROUP BY clause and aggregation functions. Here’s an example to count number of browsers in our data.

mysql> SELECT details->>'$.name' detail,
count(detail)
FROM users
GROUP BY details->>'$.name';

You will see the following output

+---------+----------------+
| details | count(details) |
+---------+----------------+
| Safari  |              1 |
| Firefox |              1 |
| Chrome  |              1 |
+---------+----------------+
3 rows in set (0.02 sec)

Similarly, you can also use JSON data in WHERE clause. Here’s an example to select data with spend > 100

mysql> SELECT id, SUM(details->>'$.spend') spend
       FROM users
       WHERE details->>'$.spend' > 100
       GROUP BY id;

Hopefully, this article will help you work with JSON data in MySQL. Ubiq makes it easy to visualize data, and monitor them in real-time dashboards. Try Ubiq for free.