MySQL含JSON列的表查询：关联提取多对象及空值处理

要实现你想要的关联式结果，把每个product和对应的port配对，还能处理product为空的情况，MySQL 8.0.4及以上版本提供的JSON_TABLE函数是最优解——它能直接把JSON数组转换成关系型的行记录，让你像操作普通表一样关联字段，性能和可读性都远胜字符串拆分的方法。

核心SQL示例 #

假设你的表是network，包含ip和json_data列，直接用下面的查询就能得到你想要的二维数组格式：

SELECT 
    n.ip,
    JSON_ARRAYAGG(JSON_ARRAY(jt.product, jt.port)) AS product_port_pairs
FROM 
    network n
JOIN JSON_TABLE(
    -- 定位到json_data里的data数组
    n.json_data->>'$.data',
    -- 遍历数组里的每个元素
    '$[*]' COLUMNS(
        -- 提取product字段，不存在则返回NULL
        product VARCHAR(50) PATH '$.product',
        -- 提取port字段
        port INT PATH '$.port'
    )
) jt
GROUP BY 
    n.ip;

效果说明 #

用你提供的示例JSON数据：

{ 
    "key1":"Value", 
    "key2":"Value", 
    "key3":"Value", 
    "data": [ 
        { "product":"ftp", "port":"21" }, 
        { "product":"ssh", "port":"22" },
        { "port":"23" }
    ] 
}

执行后会返回：

完全符合你要的关联结果，其中第三个元素因为没有product字段，自动填充为NULL。

进阶优化（处理空数组场景） #

如果有些行的data是空数组，用LEFT JOIN代替JOIN，并配合COALESCE返回空数组而非NULL：

SELECT 
    n.ip,
    COALESCE(JSON_ARRAYAGG(JSON_ARRAY(jt.product, jt.port)), JSON_ARRAY()) AS product_port_pairs
FROM 
    network n
LEFT JOIN JSON_TABLE(
    n.json_data->>'$.data',
    '$[*]' COLUMNS(
        product VARCHAR(50) PATH '$.product',
        port INT PATH '$.port'
    )
) jt ON TRUE
GROUP BY 
    n.ip;

为什么这是最优解？ #

• 相比用JSON_EXTRACT配合字符串拆分（比如SUBSTRING_INDEX），JSON_TABLE是MySQL官方专为JSON数组转关系表设计的函数，性能更优，尤其是数据量较大时。
• 代码逻辑清晰，后续维护或扩展字段（比如新增protocol字段）时，只需在COLUMNS里加一行即可，非常灵活。
• 原生支持空值处理，不需要额外写复杂的判断逻辑。

内容的提问来源于stack exchange，提问作者Onitsoga