Check if boolean value present in nested object

Question

I have a JSON column and the data stored looks like:

{"results":{"made":true,"cooked":true,"eaten":true}}
{"results":{"made":true,"cooked":true,"eaten":false}}
{"results":{"made":true,"eaten":true,"a":false,"b":true,"c":false}, "more": {"ignore":true}}

I need to find all rows where 1+ values in $.results is false.

I tried using JSON_CONTAINS() but didn't find a way to get it to compare to a boolean JSON value, or to look at all values in $.results.

This needs to work with MySQL 5.7 but if it's not possible I will accept a MySQL 8+ answer.

So, what is the Expected result as per the given sample data in your Problem ? — Madhur Bhaiya, Nov 11 '21 at 11:36

score 2 · Accepted Answer · answered Nov 11 '21 at 12:13

I don't know the way for to search for a JSON true/false/null value using JSON functions - in practice these values are treated as string-type values during the search with JSON_CONTAINS, JSON_SEARCH, etc.

Use regular expression for the checking. Something like

SELECT id, 
       JSON_PRETTY(jsondata)
FROM test
WHERE jsondata REGEXP '"results": {[^}]+: false.*}';

DEMO

score 1 · Answer 2 · answered Nov 11 '21 at 12:13

1

You could simply search the JSON_EXTRACT using the LIKE condition this way.

SELECT * FROM table1 WHERE JSON_EXTRACT(json_dict, '$.results') LIKE '%: false%';

Check this DB FIDDLE

answered Nov 11 '21 at 12:13

slashroot

773
1
4
13

score 1 · Answer 3 · answered Nov 11 '21 at 13:10

An alternative to the pattern matching in other answers, is to extract all values from $.results and check each entry with a helper table with running numbers

SELECT DISTINCT v.id, v.json_value
FROM (
 SELECT id, json_value, JSON_EXTRACT(json_value, '$.results.*') value_array
 FROM json_table
) v
JOIN seq ON seq.n < JSON_LENGTH(v.value_array)
WHERE JSON_EXTRACT(v.value_array, CONCAT('$[', seq.n, ']')) = false

Here is the demo

Check if boolean value present in nested object

3 Answers3