Query mysql json column array using AND NOT CONTAINS (mysql 5.7)

Question

I have a table with a json column that contains an array of objects, like the following:

create table test_json (json_id int not null primary key, json_data json not null) select 1 as json_id, '[{"category":"circle"},{"category":"square", "qualifier":"def"}]' as json_data union select 2 as json_id, '[{"category":"triangle", "qualifier":"xyz"},{"category":"square"}]' as json_data;

+---------+----------------------------------------------------------------------------------------+
| json_id | json_data                                                                              |
+--------------------------------------------------------------------------------------------------+
| 1       | [{"category":"circle"},   {"category":"square", "qualifier":"def"}]                    |
| 2       | [{"category":"triangle", "qualifier":"xyz"}, {"category":"square"}]                    |
+---------+----------------------------------------------------------------------------------------+

I'd like to be able to query this table to look for any rows (json_id's) that contain a json object in the array with both a "category" value of "square" and no "qualifier" property.

The sample table above is just a sample and I'm looking for a query that would work over hundreds of rows and hundreds of objects in the json array.

Bill Karwin · Answer 1 · 2020-09-17T18:34:39.487

In MySQL 8.0, you would use JSON_TABLE() for this:

mysql> select json_id, j.* from test_json, json_table(json_data, '$[*]' columns (
    category varchar(20) path '$.category',
    qualifier varchar(10) path '$.qualifier')) as j 
  where j.category = 'square' and j.qualifier is null;
+---------+----------+-----------+
| json_id | category | qualifier |
+---------+----------+-----------+
|       2 | square   | NULL      |
+---------+----------+-----------+

It's not clear why you would use JSON for this at all. It would be better to store the data in the normal manner, one row per object, with category and qualifier as individual columns.

A query against normal columns is a lot simpler to write, and you can optimize the query easily with an index:

select * from mytable where category = 'square' and qualifier is null;

I found another solution using only MySQL 5.7 JSON functions:

select json_id, json_data from test_json 
where json_extract(json_data,
  concat(
    trim(trailing '.category' from 
      json_unquote(json_search(json_data, 'one', 'square'))
    ),
  '.qualifier')
) is null

This assumes the value 'square' only occurs as a value for a "category" field. This is true in your simple example, but I don't know if it will be true in your real data.

Result:

+---------+------------------------------------------------------------------------+
| json_id | json_data                                                              |
+---------+------------------------------------------------------------------------+
|       2 | [{"category": "triangle", "qualifier": "xyz"}, {"category": "square"}] |
+---------+------------------------------------------------------------------------+

I still think that it's a CodeSmell anytime you reference JSON columns in a condition in the WHERE clause. I understood your comment that this is a simplified example, but regardless of the JSON structure, if you need to do search conditions, your queries will be far easier to develop if your data is stored in conventional columns in normalized tables.

Oh, my understanding was that JSON_TABLE was only available in mysql 8. I need a query for mysql 5.7. — Regular User, Sep 17 '20 at 18:02
Correct, JSON_TABLE() is only in MySQL 8.0. I would store the data in normal columns. — Bill Karwin, Sep 17 '20 at 18:04
My data structure is much more complicated than the sample I have included here. If it really was that simple, I agree, I wouldn't have used json. — Regular User, Sep 17 '20 at 18:04
Even if the data were more complex, I would still avoid using JSON for any columns I wanted to reference in conditions in a WHERE clause. Depending on the complexity of your data, you may need more than one extra table. — Bill Karwin, Sep 17 '20 at 18:06
The reason is that there's no way to optimize the query with an index if you use a JSON array. You are bound to do a table-scan, which is costly. There is a new index type in MySQL 8.0 for indexing JSON arrays, but as you said, you're still using 5.7. — Bill Karwin, Sep 17 '20 at 18:07
This query is not necessary in the normal course of the application and does not need to be optimized. It merely a way to run a check against the database to see if any data is missing. — Regular User, Sep 17 '20 at 18:11

score 0 · Answer 2 · answered Sep 17 '20 at 16:15

Your request is not clear. Both of your SQL records has not such properties but your JSON object has. Maybe you try to find any record that has such object. So the following is your answer:

create table test_json (json_id int not null primary key, json_data json not null) select 1 as json_id, '[{"category":"circle", "qualifier":"abc"},{"category":"square", "qualifier":"def"}]' as json_data union select 2 as json_id, '[{"category":"triangle", "qualifier":"xyz"},{"category":"square"}]' as json_data;
select * from test_json;
select * from test_json where 'square' in (JSON_EXTRACT(json_data, '$[0].category'),JSON_EXTRACT(json_data, '$[1].category'))
AND (JSON_EXTRACT(json_data, '$[0].qualifier') is NULL || JSON_EXTRACT(json_data, '$[1].qualifier') is NULL);

See Online Demo

Also see JSON Function Reference

My apologies, I've updated the question to be more specific. — Regular User, Sep 17 '20 at 17:49
Your query will match on a row with a json value of [{"category":"circle"}, {"category":"square", "qualifier":"def"}], which is shouldn't. — Regular User, Sep 17 '20 at 17:56

Query mysql json column array using AND NOT CONTAINS (mysql 5.7)

2 Answers2