MySQL 5.7中如何按JSON数组元素type值获取对应status及存在状态

由于MySQL 5.7不支持JSON_TABLE函数，我们需要通过数字辅助表+JSON提取+行转列的方式实现需求，具体步骤如下：

1. 构建数字辅助表 #

用来遍历JSON数组的索引位置，数组长度不确定时，生成足够覆盖最大可能长度的数字序列（示例生成1-10的序列）：

SELECT 1 AS idx UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL
SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL
SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10

2. 拆分JSON数组并提取目标字段 #

将TEXT类型的JSON字段转为JSON类型，结合数字索引拆分数组元素，提取每个元素的type和status：

SELECT
    t.id, -- 原表主键，关联用
    JSON_UNQUOTE(JSON_EXTRACT(CAST(t.json_data AS JSON), CONCAT('$.brandedFares[', n.idx-1, '].type'))) AS fare_type,
    JSON_UNQUOTE(JSON_EXTRACT(CAST(t.json_data AS JSON), CONCAT('$.brandedFares[', n.idx-1, '].status'))) AS fare_status
FROM your_table t
CROSS JOIN (
    SELECT 1 AS idx UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL
    SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL
    SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10
) n
WHERE JSON_EXTRACT(CAST(t.json_data AS JSON), CONCAT('$.brandedFares[', n.idx-1, ']')) IS NOT NULL

• 用CAST(t.json_data AS JSON)将TEXT字段转为JSON类型，确保JSON函数可用
• CONCAT('$.brandedFares[', n.idx-1, ']')生成数组元素的JSON路径（JSON数组索引从0开始）
• WHERE条件过滤掉超出数组长度的空元素

3. 行转列生成汇总表 #

通过MAX(CASE...)将指定的type转为列，展示是否存在及对应status：

SELECT
    t.id,
    -- 处理name1的存在性和status
    CASE WHEN MAX(CASE WHEN fare_type = 'name1' THEN 1 END) IS NOT NULL THEN '存在' ELSE '不存在' END AS name1_exists,
    MAX(CASE WHEN fare_type = 'name1' THEN fare_status END) AS name1_status,
    -- 处理name2的存在性和status
    CASE WHEN MAX(CASE WHEN fare_type = 'name2' THEN 1 END) IS NOT NULL THEN '存在' ELSE '不存在' END AS name2_exists,
    MAX(CASE WHEN fare_type = 'name2' THEN fare_status END) AS name2_status
FROM (
    SELECT
        t.id,
        JSON_UNQUOTE(JSON_EXTRACT(CAST(t.json_data AS JSON), CONCAT('$.brandedFares[', n.idx-1, '].type'))) AS fare_type,
        JSON_UNQUOTE(JSON_EXTRACT(CAST(t.json_data AS JSON), CONCAT('$.brandedFares[', n.idx-1, '].status'))) AS fare_status
    FROM your_table t
    CROSS JOIN (
        SELECT 1 AS idx UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL
        SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL
        SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10
    ) n
    WHERE JSON_EXTRACT(CAST(t.json_data AS JSON), CONCAT('$.brandedFares[', n.idx-1, ']')) IS NOT NULL
) tmp
GROUP BY t.id

关键说明 #

• 如果数组最大长度超过10，只需在数字辅助表中增加对应的数字即可
• 若status可能为JSON类型而非字符串，去掉JSON_UNQUOTE即可
• 原表若无主键，可替换为唯一标识行的字段

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