时间戳

首页 > 解决方案 > 如何使用 json 数组从 JSON 文档中提取不同的值？

mysql - 如何使用 json 数组从 JSON 文档中提取不同的值？

问题描述

我正在使用8.0.18-commercial并编写以下MySQL查询来形成cross join with JSON documents.

请注意，中的条目数与中的条目 数servers[]相同。objectIds[]servers[]objectIds[]

SELECT t1.networkInfo->>"$.*" AS network, 
t1.objectIds,
j.server
FROM table1 t1
CROSS JOIN JSON_TABLE(t1.servers, 
                      "$[*]" COLUMNS (
                        server VARCHAR(20) PATH '$'
                        )
                      ) j
WHERE t1.id = 56

输出

network                             server                  objectIds
["abc.com", "Linux123", "RHEL"]     Server123       ["objectId123", "objectId1231", "objectId1232"]
["abc.com", "Linux123", "RHEL"]     Server1231      ["objectId123", "objectId1231", "objectId1232"]
["abc.com", "Linux123", "RHEL"]     Server1232      ["objectId123", "objectId1231", "objectId1232"]

我想在查询中添加另一个联接，以便输出如下所示：

预期产出

network                             server           objectIds
["abc.com", "Linux123", "RHEL"]     Server123        objectId123
["abc.com", "Linux123", "RHEL"]     Server1231       objectId1231
["abc.com", "Linux123", "RHEL"]     Server1232       objectId1232

标签： mysqlarraysjsonselect

解决方案

您可以通过以与objectIds列相同的方式对列进行解包，为每个使用servers添加行号列并将该行号用于and表来获得所需的结果：JSON_TABLEFOR ORDINALITYJOINserversobjectIds

SELECT t1.networkInfo->>"$.*" AS network, s.server, o.objectId
FROM table1 t1
CROSS JOIN JSON_TABLE(t1.servers, 
                      "$[*]" COLUMNS (
                        rownum FOR ORDINALITY,
                        server VARCHAR(20) PATH '$'
                        )
                      ) s
JOIN JSON_TABLE(t1.objectIds, 
                "$[*]" COLUMNS (
                  rownum FOR ORDINALITY,
                  objectId VARCHAR(20) PATH '$'
                  )
                ) o ON o.rownum = s.rownum
WHERE t1.id = 56

输出：

network                             server      objectId
["abc.com", "Linux123", "RHEL"]     Server123   objectId123
["abc.com", "Linux123", "RHEL"]     Server1231  objectId1231
["abc.com", "Linux123", "RHEL"]     Server1232  objectId1232

dbfiddle 上的演示

推荐阅读