How to Store JSON Data in MySQL

Last updated on August 26th, 2024 at 09:18 am

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.

Why Store JSON Data in MySQL

These days most websites and apps exchange data between client and server in JSON format. This is because it allows you to store diverse and large amount of data in a compact manner. JSON data is commonly used to store information about user attributes, behaviors and events. But since JSON has a very flexible format, it can be tricky to store, retrieve and update this information in databases. Nevertheless, it has become imperative to store JSON data in MySQL. Earlier, developers had to write functions to convert JSON data into DBMS friendly format for storage and retrieval. Luckily, since MySQL 5.7.8, there are readymade functions available for this purpose.

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

First, let us create a MySQL table with JSON column. 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 } }'
);

In the above SQL query, we have simply used JSON strings in values() clause. You can also store JSON strings as variables and then use them in SQL query as shown.

mysql> set @sample = (
      '{ "page": "/" , 
         "name": "Safari", 
         "os": "Mac", 
         "spend": 100, 
         "resolution": { "x": 1920, "y": 1080 } }'
);

mysql> insert into users(details)
    values(@sample);

Also read : How to Get every Nth Row in MySQL

Extract JSON Data in MySQL

There are 3 ways to extract data from JSON column, using -> operator and ->> operator. Let us look at each of them one by one. In the following examples, $. stands for the JSON value of present row. You can use the dot operator to extract values of different keys.

Using -> Operator

We will extract data from JSON columns using column path operator (->). Since MySQL 5.7.9, -> operator is an alias for JSON_EXTRACT() function.

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" |
+----+-----------+

Here is the same query using JSON_EXTRACT() function. JSON_EXTRACT() function takes 2 parameters – name of JSON column and named key.

mysql> select id, json_extract(details, '$.name') details from users;

Using ->> operator

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  |
+----+-----------+

Using $. operator

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 |
+---------+----------------+

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;

Update JSON Data in MySQL

You can also update very specific values in a JSON column, not the entire JSON string, but a specific named key’s value, using JSON_INSERT() or JSON_REPLACE() functions. Both of them take 3 arguments – name of JSON column, named property and new value. Here is an example to update the name key’s value in row #1.

mysql> UPDATE users
SET details = JSON_REPLACE(
        details ,
        '$.name' ,
        'Safari 2'
) WHERE id=1;

JSON_REPLACE() works only if a property to be updated already exists in the JSON column. If it does not exist, and you need to create it newly, then you need to use JSON_INSERT() function. Here is the query to add new property style to the JSON column of row #1.

mysql> UPDATE users
SET details = JSON_INSERT(
        details ,
        '$.style' ,
        'new launch'
) WHERE id=1;

Delete JSON Data

You can also delete specific values in a JSON column using JSON_REMOVE() function and named keys as shown. JSON_REMOVE() takes 2 arguments – name of JSON column and its named key. Here is a query to remove name value of rows.

mysql> UPDATE users SET details = JSON_REMOVE(details , '$.name')

Conclusion

In this article, we have learnt several ways to work with JSON data. We have learnt how to insert JSON data, extract it, update and delete it. The best part is that MySQL provides out of the box functions to easily go deep into your JSON column value and work with it. 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.