Given a JSON object:SET @j := '{"BOOSTER": 1, "RETRO": 2, "GUIDANCE": 0, "SURGEON": 1, "RECOVERY": 0}';
How do you find the keys which do NOT contain a value of 0?
One approach is to find the keys which do match 0, and then remove them.
Sounds like JSON_SEARCH and JSON_REMOVE? But there’s a gotcha: JSON_SEARCH works only with strings. Ok, so we REPLACE(@j, ‘0’, ‘”0″‘) – but that doesn’t help, because JSON_REMOVE can’t accept an array of paths like JSON_SEARCH would return; it requires each path as a separate parameter.
Instead, JSON_MERGE_PATCH will suffice. It has the feature that values from the second parameter are preserved only if they are not NULL. So, the approach becomes turning the 0s into NULLs, and then merging that document into an empty one.
SELECT REPLACE(@j, 0, 'null') AS j; -- case matters! null, not NULL
+------------------------------------------------------------------------------+
| j |
+------------------------------------------------------------------------------+
| {"BOOSTER": 1, "RETRO": 2, "GUIDANCE": null, "SURGEON": 1, "RECOVERY": null} |
+------------------------------------------------------------------------------+
SELECT JSON_MERGE_PATCH('{}', REPLACE(@j, 0, 'null')) AS j;
+------------------------------------------+
| j |
+------------------------------------------+
| {"RETRO": 2, "BOOSTER": 1, "SURGEON": 1} |
+------------------------------------------+
And since I technically asked for just the keys, not the key/value pairs,
SELECT JSON_KEYS(JSON_MERGE_PATCH('{}', REPLACE(@j, 0, 'null'))) AS j;
+---------------------------------+
| j |
+---------------------------------+
| ["RETRO", "BOOSTER", "SURGEON"] |
+---------------------------------+
But, there’s a cleaner way.
Technically NULL does not match 0, so if you have keys that have NULL values then what do you do? Can JSON keys have NULL values?
Comment by Justin Swanhart — May 5, 2020 @ 10:35 pm
They can (although, at least in MySQL, it seems case matters and they have to be null instead of NULL).
If you need to preserve such values, I suppose you could REPLACE() them with something else – like the string ‘”null”‘ instead of the actual null value – first, do the merge patch, and then REPLACE() them back again. Icky, isn’t it? So perhaps use a better approach: https://thenoyes.com/littlenoise/?p=614
Comment by snoyes — May 6, 2020 @ 10:34 am
[…] second approach to finding the non-0 values from a JSON object is to turn the keys and values into separate columns of a table. This is cleaner, but a little […]
Pingback by JSON in MySQL: Keys which do NOT match a value (the more elegant way) « A Little Noise — May 6, 2020 @ 12:33 pm