Nested JSON in mysql table

Question

I have a table db.Geometry in my mysql database that has a column called FORM containing json strings all of this type:

{"type":"polygon","corners":[{"x":43.790000915527344,"y":2.6940000057220463,"floor":0},{"x":44.884937766905495,"y":8.128635658848992,"floor":0},{"x":24.52993631732053,"y":12.229635729420892,"floor":0},{"x":23.434999465942386,"y":6.795000076293945,"floor":0}],"floor":0}}

What I am looking for is to unnest this and create instead a table with the columns: type, x,y,floor. Now, if it had been so that the string had had the following appearance:

{"corners":[{"x":43.790000915527344,"y":2.6940000057220463,"floor":0},{"x":44.884937766905495,"y":8.128635658848992,"floor":0},{"x":24.52993631732053,"y":12.229635729420892,"floor":0},{"x":23.434999465942386,"y":6.795000076293945,"floor":0}],"floor":0}

I would have done the following thing:

SELECT ID, Tab.*
    FROM db.Geometry 
        CROSS JOIN JSON_TABLE(
            db.Geometry.FORM-> '$.corners',
            '$[*]' COLUMNS (
                x VARCHAR(255) PATH '$.x',
                y VARCHAR(255) PATH '$.y',
                floor VARCHAR(255) PATH '$.floor'
            )
    ) Tab;

But this obviously does not work in the situation I actually have because I do not handle the "type":"polygon" part of the json string. It return an empty table.

What do I need to add to my code to deal with this? Thankful for any insight.

It return an empty table. ?? fiddle (your data value is edited, excess closing } is removed). Show desired output for shown source data. — Akina
– Akina, Commented May 17, 2021 at 7:28

Akina · Accepted Answer · 2021-05-17 07:35:44Z

1

Maybe you need in

SELECT Geometry.ID, jsontable1.type, jsontable1.object_floor, jsontable2.*
FROM Geometry 
CROSS JOIN JSON_TABLE( Geometry.FORM,
                       '$' COLUMNS ( type VARCHAR(255) PATH '$.type',
                                     corners JSON PATH '$.corners',
                                     object_floor INT PATH '$.floor' ) ) jsontable1
CROSS JOIN JSON_TABLE( jsontable1.corners,
                       '$[*]' COLUMNS ( ROWID FOR ORDINALITY,
                                        x VARCHAR(255) PATH '$.x',
                                        y VARCHAR(255) PATH '$.y',
                                        corner_floor VARCHAR(255) PATH '$.floor' ) ) jsontable2

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=5d95a6a3392409ebf851fc9a87840926

answered May 17, 2021 at 7:35

Akina

43.2k6 gold badges17 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Serge de Gosson de Varennes Over a year ago

This is so nice! A big thank you to you, sir!

Collectives™ on Stack Overflow

Nested JSON in mysql table

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related