如何从MySQL JSON数组内的JSON对象中选取指定值

问题1：获取trees数组第一个元素的tree_id #

你的语句错误在于JSON路径语法——数组索引前多了一个多余的.，正确的路径格式是键名[索引]，不需要额外的点分隔符。

修正后的查询语句：

SELECT trees->'$.trees[0].tree_id' AS tree_id FROM configuration c;

如果想去掉返回结果的双引号（直接返回字符串值），可以使用->>运算符：

SELECT trees->>'$.trees[0].tree_id' AS tree_id FROM configuration c;

问题2：深入nodes数组选取属性 #

根据需求不同，有两种常见的处理方式：

1. 选取特定位置的node属性

比如获取第一个tree下第一个node的node_id和type：

SELECT 
    trees->>'$.trees[0].nodes[0].node_id' AS root_node_id,
    trees->>'$.trees[0].nodes[0].type' AS root_node_type
FROM configuration c;

2. 展开所有node数据（适合批量获取数组元素）

如果需要把nodes数组中的每个元素都作为单独的行返回，可以使用JSON_TABLE函数将JSON数组转换为关系表：

SELECT 
    config.name,
    tree.tree_id,
    node.node_id,
    node.type,
    node.node_position
FROM configuration config
-- 展开trees数组
JOIN JSON_TABLE(
    config.trees->'$.trees',
    '$[*]' COLUMNS (
        tree_id VARCHAR(100) PATH '$.tree_id',
        nodes JSON PATH '$.nodes'
    )
) AS tree
-- 展开每个tree下的nodes数组
JOIN JSON_TABLE(
    tree.nodes,
    '$[*]' COLUMNS (
        node_id VARCHAR(100) PATH '$.node_id',
        type VARCHAR(50) PATH '$.type',
        node_position INT PATH '$.node_position'
    )
) AS node;

关键语法说明 #

• MySQL JSON路径中，访问数组元素使用[索引]，索引从0开始，无需在键名和索引之间加.
• ->运算符返回带双引号的JSON字符串，->>返回原始字符串值
• JSON_TABLE用于将JSON数组转换为关系型数据集，方便批量处理数组元素

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