JSON Extract Nested Associative Arrays into Rows in MySQL 8 using JSON_TABLE

Question

I have my data setup in mysql table in a json column that has nested rows with unique key for each child array. I am using MySQL 8 and tried using json_table function but with no success.

Here is the sample structure and data of my table:

CREATE TABLE tb(json_col JSON);

INSERT INTO tb VALUES (
    '{ "actors": {
        "101": { "name":"Arnold",  "address":"780 Mission St, San Francisco, CA 94103"},
        "102": { "name":"Sylvester",  "address":"75 37th Ave S, St Cloud, MN 94103"}, 
        "103": { "name":"Al Pacino",  "address":"1262 Roosevelt Trail, Raymond, ME 04071"}
    }
    }'
);

If you notice, I have those keys (101, 102, 103...) that are causing issues for me when I use json_table. If I have a structure without those keys, I am able to get values into rows but with those keys in between, I am unable to move forward.

The query I am trying to pass is:

SELECT actors.* 
FROM tb, 
     JSON_TABLE(json_col, '$.actors.*' COLUMNS (
                name VARCHAR(40)  PATH '$.*.name',
                address VARCHAR(100) PATH '$.*.address')
     ) actors;

The response to this is "0 row(s) returned".

My goal is to get the data in this manner:

| name      | address                                 |
|-----------|-----------------------------------------|
| Arnold    | 780 Mission St, San Francisco, CA 94103 |
| Sylvester | 75 37th Ave S, St Cloud, MN 94103       |
| Al Pacino | 1262 Roosevelt Trail, Raymond, ME 04071 |

Kindly help me get this in the right direction. Thanks

Seems like it would be simpler to store the database as if you were using a relational database. Oh you are, so create a `name` and `addr` column — RiggsFolly, Mar 14 '23 at 08:39

Jordy · Accepted Answer · 2023-03-14T09:16:43.803

2

Your query almost works. Just change $.*.name to $.name and $.*.address to $.address to solve the problem.

SELECT actors.* 
FROM tb, 
     JSON_TABLE(json_col, '$.actors.*' COLUMNS (
                name VARCHAR(40) PATH '$.name',
                address VARCHAR(100) PATH '$.address')
    ) actors;

Because

SELECT JSON_EXTRACT(json_col,'$.actors.*') FROM tb;

return array of objects like this:

[
  {"name": "Arnold", "address": "780 Mission St, San Francisco, CA 94103"}, 
  {"name": "Sylvester", "address": "75 37th Ave S, St Cloud, MN 94103"}, 
  {"name": "Al Pacino", "address": "1262 Roosevelt Trail, Raymond, ME 04071"}
]

edited Mar 14 '23 at 09:16

answered Mar 14 '23 at 08:54

Jordy

1,802
2
6
25

*return JSON like this* No, JSON_TABLE extracts not array but 3 separate JSON objects which are treated as 3 separate source rows. And this results in 3 output rows. – Akina Mar 14 '23 at 09:15

JSON Extract Nested Associative Arrays into Rows in MySQL 8 using JSON_TABLE

1 Answers1