Mysql explode json array to rows-CodePudding

From a table with a column with json array of dictionaries i need to extract all values of key "user_id" one per row. If null or empty array return NULL. Similar to python pandas explode method.

Length of array is unknown.

Original table:

| id | users                                                           |
|----|-----------------------------------------------------------------|
|  1 |[{"id": 2, "mail": "[email protected]"}, {"id": 3, "mail": "[email protected]"}] |
|  2 |[{"id": 5, "email": "[email protected]"}]"                            |
|  3 | []
                                                           |

Processed table:

| id | users    |
|----|----------|
|  1 | 2        |
|  1 | 3        |
|  2 | 5        |
|  3 | NULL     |

CodePudding user response：

select id, j.user_id from mytable left outer join 
json_table(users, '$[*]' columns (user_id int path '$.user_id')) as j on true;

 ------ --------- 
| id   | user_id |
 ------ --------- 
|    1 |       2 |
|    1 |       3 |
|    2 |       5 |
|    3 |    NULL |
 ------ ---------

Read https://dev.mysql.com/doc/refman/8.0/en/json-table-functions.html for more information on the JSON_TABLE() function.