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!
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
