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 · Accepted Answer · 2020-09-17 18:34:39Z

2

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.

edited Sep 17, 2020 at 18:34

answered Sep 17, 2020 at 17:57

Bill Karwin

567k87 gold badges709 silver badges869 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Regular User Over a year ago

Oh, my understanding was that JSON_TABLE was only available in mysql 8. I need a query for mysql 5.7.

Bill Karwin Over a year ago

Correct, JSON_TABLE() is only in MySQL 8.0. I would store the data in normal columns.

Regular User Over a year ago

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.

Bill Karwin Over a year ago

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 Over a year ago

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.

|

Majid Hajibaba · Accepted Answer · 2020-09-17 16:15:10Z

0

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

answered Sep 17, 2020 at 16:15

Majid Hajibaba

3,2806 gold badges29 silver badges59 bronze badges

2 Comments

Regular User Over a year ago

My apologies, I've updated the question to be more specific.

Regular User Over a year ago

Your query will match on a row with a json value of [{"category":"circle"}, {"category":"square", "qualifier":"def"}], which is shouldn't.

Collectives™ on Stack Overflow

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

2 Answers 2

6 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related