Search code examples
jsonmariadbcontains

MariaDB JSON: contains specific value in any sub attribute

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?

Solution

  • 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