1

I'm having a JSON like: {"a": {"z":true,"y":false}, "b": {"z":false,"y":false} }. I need to obtain in MariaDB if there is any (sub) attribute in this JSON that contains the value "true".

I tried in SQL the following query:

SET @json='{"a": {"z":true,"y":false}, "b": {"z":false,"y":false} }';
SELECT  JSON_CONTAINS(@json,'true'),
        JSON_CONTAINS(@json,'true','$.a'),
        JSON_CONTAINS(@json,'true','$.a.z');

But the first two respond with 0 (zero), only the last one with 1. That means I can check $.a.z and $.a.y seperately and obtain with a case if any of them is true. I hope there is an other way to achieve this?

Improve this question

1 Answer 1

Reset to default
0

JSON_CONTAINS, doesn't support wildcards in the path, but JSON_SEARCH does.

it would also support true% and has even more possibilities

SET @json='{"a": {"z":true,"y":false}, "b": {"z":false,"y":false} }';

SELECT JSON_SEARCH(@json, 'all', 'true', NULL, '$.b') IS NOT  NULL;
JSON_SEARCH(@json, 'all', 'true', NULL, '$.b') IS NOT NULL
0 
SELECT JSON_SEARCH(@json, 'all', 'true', NULL, '$.a') IS NOT  NULL
JSON_SEARCH(@json, 'all', 'true', NULL, '$.a') IS NOT NULL
1

fiddle

Improve this answer
3
  • sounds to me like they just want a boolean result for all attributes: json_search(@json, 'one', 'true') is not null. note that the documentation does not discuss what happens for non-string searches at all.
    – ysth
    Commented May 12 at 3:32
  • No he wants to check children and we simply don't know enough about his jjson
    – nbk
    Commented May 12 at 7:25
  • Thank you @nbk. This is exactly I was looking for: if any of the children of "a" has the value true, the search is true. So the options "all" and "one" are both doing it.
    – John Schols
    Commented May 12 at 8:46

Not the answer you're looking for? Browse other questions tagged or ask your own question.