Search code examples
mysqlmysql-json

Check if boolean value present in nested object

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.

Solution

  • 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