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
– Madhur Bhaiya, Commented Nov 11, 2021 at 11:36

Akina · Accepted Answer · 2021-11-11 12:13:20Z

2

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

answered Nov 11, 2021 at 12:13

Akina

43.2k6 gold badges17 silver badges30 bronze badges

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

Comments

slashroot · Accepted Answer · 2021-11-11 12:13:41Z

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, 2021 at 12:13

slashroot

7531 gold badge6 silver badges13 bronze badges

Comments

ProDec · Accepted Answer · 2021-11-11 13:10:43Z

1

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

answered Nov 11, 2021 at 13:10

ProDec

5,4001 gold badge6 silver badges12 bronze badges

Collectives™ on Stack Overflow

Check if boolean value present in nested object

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related