A Little Noise

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?

A 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 wordier.

We’ll get the keys in one table:

SELECT ord, keyname 
FROM JSON_TABLE(JSON_KEYS(@j), '$[*]' COLUMNS (
    ord FOR ORDINALITY,
    keyname VARCHAR(100) PATH '$')
) AS keyTable;
 +------+----------+
 | ord  | keyname  |
 +------+----------+
 |    1 | RETRO    |
 |    2 | BOOSTER  |
 |    3 | SURGEON  |
 |    4 | GUIDANCE |
 |    5 | RECOVERY |
 +------+----------+

And the values in a second table:

SELECT ord, keyvalue
FROM JSON_TABLE(@j, '$.*' COLUMNS (
    ord FOR ORDINALITY,
    keyvalue INT PATH '$')
) AS valueTable;
+------+----------+
| ord  | keyvalue |
+------+----------+
|    1 | 2        |
|    2 | 1        |
|    3 | 1        |
|    4 | 0        |
|    5 | 0        |
+------+----------+

And then join those two together:

SELECT keyname, keyvalue
 FROM JSON_TABLE(JSON_KEYS(@j), '$[*]' COLUMNS (
    ord FOR ORDINALITY,
    keyname VARCHAR(100) PATH '$')) AS keyTable
 JOIN JSON_TABLE(@j, '$.*' COLUMNS (
    ord FOR ORDINALITY,
    keyvalue INT PATH '$')
) AS valueTable USING (ord);
 +----------+----------+
 | keyname  | keyvalue |
 +----------+----------+
 | RETRO    | 2        |
 | BOOSTER  | 1        |
 | SURGEON  | 1        |
 | GUIDANCE | 0        |
 | RECOVERY | 0        |
 +----------+----------+

Now you can use ordinary operations like a WHERE clause to do your filtering, and then JSON_ARRAYAGG() to put them all back into JSON at the end.

SELECT JSON_ARRAYAGG(keyname) AS j
 FROM JSON_TABLE(JSON_KEYS(@j), '$[*]' COLUMNS (
    ord FOR ORDINALITY,
    keyname VARCHAR(100) PATH '$')) AS keyTable
 JOIN JSON_TABLE(@j, '$.*' COLUMNS (
    ord FOR ORDINALITY,
    keyvalue INT PATH '$')
) AS valueTable USING (ord)
 WHERE keyvalue != 0;
 +---------------------------------+
 | j                               |
 +---------------------------------+
 | ["RETRO", "BOOSTER", "SURGEON"] |
 +---------------------------------+

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.