Check if boolean value present in nested object-CodePudding

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.

CodePudding user response：

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

CodePudding user response：

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

CodePudding user response：

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