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"] |
 +---------------------------------+