How to Query JSON column in MySQL

Sometimes you may need to search JSON array of objects, extract JSON data or retrieve JSON data in MySQL. In this article, we will look at how to query JSON column in MySQL.

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.

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 } }'
);

Also read : How to Avoid Inserting Duplicate Records in MySQL

How to Retrieve data from JSON column in MySQL

MySQL 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 ->>'$.name'="Chrome";
+----+--------------+--------------+
| id | browser_str  | browser_name |
+----+--------------+--------------+
|  2 | "Chrome"     |  Chrome      |
+----+--------------+--------------+

Also read : How to Get Multiple Counts in Single Query in MySQL

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

Also read : How to Convert datetime to UTC in MySQL

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

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.