mysql表中的嵌套JSON

Question

我的mysql数据库中有一个表mysql，其中有一个名为FORM的列，该列包含json字符串，所有这些类型如下：

{"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}}

我正在寻找的是取消嵌套，创建一个包含列：type, x,y,floor的表。现在，如果是这样的话，字符串的外观如下：

{"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}

我会做以下事情：

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;

但是，在实际情况下，这显然是行不通的，因为我没有处理json字符串的"type":"polygon“部分。它还空着一张桌子。

我需要在我的代码中添加什么来处理这个问题？感谢你的洞察力。

Answer 1

也许你需要加入

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