How to Query JSON column in MySQL

Last updated on June 28th, 2024 at 09:03 am

JSON is a popular data exchange format used widely in websites and apps. It is also supported by many RESTful APIs. Sometimes you may need to store and search JSON array of objects, extract JSON data or retrieve JSON data in MySQL. MySQL stores JSON as strings so these operations can be tricky. Luckily, there are several ways to do this. In this article, we will look at how to query JSON column in MySQL.

Table of Contents

Why Query JSON in MySQL

These days most websites and apps write their data as JSON strings and store them in databases, since JSON strings are structured but very flexible. But this poses a problem. Since JSON strings can have multiple nesting and varying structure it is difficult to use traditional MySQL operators to filter rows based on JSON values. Also, since each JSON entity follows the key-value format, it can be difficult to retrieve JSON value of an item using its key.

How to Query JSON column in MySQL

Let us say you have the following table users(id, details) where id is an integer and primary key while details is a JSON data type column. The details column stores all details of users such as their OS name, web browser, resolution, etc. as a JSON string.

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

We will insert the following JSON data in our table.

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

How to Retrieve data from JSON column in MySQL

MySQL offers path operators as well as json_extract() function for this purpose. Lastly, you can search it just as you would search a string for a substring. We will look at each of these solutions one by one.

1. Using Path Operators

Since MySQL 5.7.8, it provides two operators ( -> and ->> ) to extract data from JSON columns. ->> will get the string value while -> will fetch value without quotes.

Here is the SQL query to extract browser name from details column

mysql> select id, 
       details->>'$.name' as browser_str,
       details->'$.name' as browser_name
       from users;
+----+--------------+--------------+
| id | browser_str  | browser_name |
+----+--------------+--------------+
|  1 | "Safari"     |  Safari      |
|  2 | "Chrome"     |  Chrome      |
|  3 | "Firefox"    |  Firefox     |
+----+--------------+--------------+

As you can see ->> returns output as quoted strings, while -> returns values as they are.

You can also use these operators in WHERE clause as shown below.

mysql> select id, 
       details->>'$.name' as browser_str,
       details->'$.name' as browser_name
       from users
       where details->>'$.name'="Chrome";
+----+--------------+--------------+
| id | browser_str  | browser_name |
+----+--------------+--------------+
|  2 | "Chrome"     |  Chrome      |
+----+--------------+--------------+

If you want to retrieve resolution.x value, that is, from an object, then you can do so using the dot (.) operator, as shown below.

mysql> select details->'$.resolution.x' as  width
       from users;
+----+-----------+
| id |  width    |
+----+-----------+
|  1 | 1920      |
|  2 | 1680      |
|  3 | 1280      |
+----+-----------+

Similarly, if you want to extract an array element from a JSON, then you can access them using square brackets ‘[ ]’ and index of array element. Here is the SQL query to extract first element of array spend.

mysql> select details->>'$.spend[0]' as spend
       from users;
+----+-----------+
| id |  spend    |
+----+-----------+
|  1 | 100       |
|  2 | 150       |
|  3 | 200       |
+----+-----------+

2. Using json_extract() function

MySQL >= 5.7 features json_extract() function. Here is its syntax.

select json_extract(json, '$.key')
from table
where json_extract(json, '$.key') = 'value'

Here is a query to get row where browser name is Chrome.

mysql> select id, 
       json_extract(details,'$.name') as browser_str,
       json_extract(details,'$.name') as browser_name
       from users
       where json_extract(details, '$.name')="Chrome";
+----+--------------+--------------+
| id | browser_str  | browser_name |
+----+--------------+--------------+
|  2 | "Chrome"     |  Chrome      |
+----+--------------+--------------+

3. Using Like Operator

If you are using MySQL < 5.7 then you will need to search your JSON column for key-value pair just as you would search for a substring in a string.

mysql> SELECT * FROM users WHERE details LIKE '%"name": "Chrome"%';
+----+-----------------------------------------------------------------------------------------------------------------------------------------------------------+
| id | details                                                                                                                                                   |
+----+-----------------------------------------------------------------------------------------------------------------------------------------------------------+
|  2 | { "page": "/products", "name": "Chrome","os": "Windows", "spend": [150, 250]         "resolution": { "x": 1680, "y": 1050 } } |
+----+-----------------------------------------------------------------------------------------------------------------------------------------------------------+

Conclusion

In this article, we have learnt how to extract JSON values from MySQL columns with JSON data type. Please note, MySQL >=5.7.8 supports JSON data type. All prior versions store JSON as blob or texts. Similarly, since version 5.7.8, MySQL also supports path operators(-> and ->>) as well as json_extract() function to retrieve JSON values from a json column. If you are using MySQL<5.7.8, you will need to look for JSON value just as we search for substring in a text or blob column.

Extract JSON using Ubiq

Ubiq Reporting tool supports all the above SQL queries and makes it easy to visualize SQL results in different ways. Here is the first SQL query mentioned above, in Ubiq.