1

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": "u1@ab.com"}, {"id": 3, "mail": "u2@ab.com"}] |
|  2 |[{"id": 5, "email": "user3@hi.com"}]"                            |
|  3 | []
                                                           |

Processed table:

| id | users    |
|----|----------|
|  1 | 2        |
|  1 | 3        |
|  2 | 5        |
|  3 | NULL     |
Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
Emilio Carrasco Moreno
  • 13
  • 3

1 Answers1

0
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.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828