how to extract and zip values from json with SQL, mysql

I struggle to extract the required values from the JSON column in MySQL. Imagine the variable:

SET @j = '[{"a": 1, "b": 2},{"a": 3, "b": 4, "c": {"d": 4}}, {"a": 5}]';

and I need to extract and return a new JSON array containing only selected key-value pairs, eg.: only a’s and b’s and if the required key does not exist I want to return null for that key:

[{"a": 1, "b": 2},{"a": 3, "b": 4},{"a": 5, "b": null}]

thanks

Answer

Here’s a solution using MySQL 8.0’s JSON_TABLE() function:

mysql> SET @j = '[{"a": 1, "b": 2},{"a": 3, "b": 4, "c": {"d": 4}}, {"a": 5}]';
Query OK, 0 rows affected (0.00 sec)

mysql> select j.* from json_table(@j, '$[*]' 
  columns (a int path '$.a', b int path '$.b')) as j;
+------+------+
| a    | b    |
+------+------+
|    1 |    2 |
|    3 |    4 |
|    5 | NULL |
+------+------+
3 rows in set (0.00 sec)

Based on this result, we can build a new JSON array of objects:

mysql> select json_arrayagg(json_object('a', j.a, 'b', j.b)) as new 
  from json_table(@j, '$[*]' 
  columns (a int path '$.a', b int path '$.b')) as j;
+-----------------------------------------------------------+
| new                                                       |
+-----------------------------------------------------------+
| [{"a": 1, "b": 2}, {"a": 3, "b": 4}, {"a": 5, "b": null}] |
+-----------------------------------------------------------+

If you are using MySQL 5.x, I would recommend just fetch the whole JSON document to a client application where you can deserialize it into data structures to manipulate in code.