[SOLVED] Mysql json fields - filter inside by paramter

Mysql json fields - filter inside by paramter

I have in mysql json field with the values:

{"a": true, "b":true, "c":false, "d":true}

I want to retrieve in SQL, for each row, only the keys and values that are true.

For example in the row:

{"a": true, "b":true, "c":false, "d":true}

the result will be:

{"a": true, "b":true, "d":true}

How can I do it?

Thank you!

Solution

Using string (regex) functions:

SELECT id,
       val, 
       REGEXP_REPLACE(REGEXP_REPLACE(val, '(, *)?"[^"]+": *false', ''), '\\{ *, *', '\\{') without_false
FROM test

Using recursive CTE:

WITH RECURSIVE
cte AS ( SELECT id, val src, val FROM test
         UNION ALL
         SELECT id, 
                src,
                JSON_REMOVE(val, JSON_UNQUOTE(JSON_SEARCH(REPLACE(val, 'false', '"false"'), 'one', 'false')))
         FROM cte
         WHERE JSON_SEARCH(REPLACE(val, 'false', '"false"'), 'one', 'false') IS NOT NULL
         )
SELECT id, src val, val without_false
FROM cte
WHERE JSON_SEARCH(REPLACE(val, 'false', '"false"'), 'one', 'false') IS NULL

fiddle