How can I search a JSON column for an empty array?
Let's say I have a config column that sometimes has JSON equal to {"tier": []}.
WHERE JSON_CONTAINS(config, '', '$.tier') = 1
or
WHERE JSON_CONTAINS_PATH(config, 'one', '$.tier') = NULL
Aren't returning any results. Is there something I'm missing?
I've looked around for answers to this specifically, but I couldn't find anything on SO or the MySQL docs.
Here are two ways to do it, testing on MySQL 5.7.24:
mysql 5.7.24> select config from mytable
where json_contains(config, cast('[]' as json), '$.tier');
+--------------+
| config |
+--------------+
| {"tier": []} |
+--------------+
mysql 5.7.24> select config from mytable
where json_contains_path(config, 'one', '$.tier');
+--------------+
| config |
+--------------+
| {"tier": []} |
+--------------+
I found another solution, which helps to check strictly for an empty array:
First, see that I have two rows, and one has a non-empty array:
mysql 5.7.24> select config from mytable
where json_contains(config, json_array(), '$.tier');
+----------------------------------------+
| config |
+----------------------------------------+
| {"tier": []} |
| {"tier": [{"name": "BK", "value": 8}]} |
+----------------------------------------+
2 rows in set (0.00 sec)
Now I make sure that the length of the array is 0 as a way of confirming that it is empty:
mysql 5.7.24> select config from mytable
where json_contains(config, json_array(), '$.tier')
and json_length(config, '$.tier') = 0;
+--------------+
| config |
+--------------+
| {"tier": []} |
+--------------+
1 row in set (0.00 sec)
config column also contains {"tier": [{"name": "BK", "value": 8}]} those records show up in the results as well as the empty array ones.If tier is guaranteed to be an array you can check if 0th item exists using JSON_CONTAINS_PATH:
SELECT JSON_CONTAINS_PATH('{"tier": []}', 'one', '$.tier[0]'); -- 0
SELECT JSON_CONTAINS_PATH('{"tier": ["foo"]}', 'one', '$.tier[0]'); -- 1
SELECT JSON_CONTAINS_PATH('{"tier": ["foo", "bar"]}', 'one', '$.tier[0]'); -- 1
Alternately use JSON_LENGTH:
SELECT JSON_LENGTH('{"tier": []}', '$.tier'); -- 0
SELECT JSON_LENGTH('{"tier": ["foo"]}', '$.tier'); -- 1
SELECT JSON_LENGTH('{"tier": ["foo", "bar"]}', '$.tier'); -- 2