如何从MySQL JSON列的数组对象中过滤提取指定字段？

嘿，我来帮你搞定这个JSON提取需求！你已经知道用$.info[*]获取整个数组元素，但要只保留每个元素里存在的sex和age字段（没有的字段直接不显示），可以用两种方法实现，下面分别说明：

方法一：使用JSON_TABLE展开后重新构造（精准匹配你的需求） #

这个方法兼容性更好，还能自动移除值为null的字段，完全贴合你想要的输出结果：

SELECT JSON_ARRAYAGG(
    CASE
        WHEN sex IS NOT NULL AND age IS NOT NULL THEN JSON_OBJECT('sex', sex, 'age', age)
        WHEN sex IS NOT NULL THEN JSON_OBJECT('sex', sex)
        WHEN age IS NOT NULL THEN JSON_OBJECT('age', age)
        ELSE JSON_OBJECT()
    END
) AS result
FROM your_table,
     JSON_TABLE(
         your_json_column,
         '$.info[*]' COLUMNS (
             sex VARCHAR(10) PATH '$.sex',
             age INT PATH '$.age'
         )
     ) AS jt
WHERE ...; -- 这里填你的记录筛选条件

步骤说明： #

• JSON_TABLE把info数组里的每个元素拆分成单独的行，提取sex和age字段（不存在的字段会返回null）
• CASE语句判断每个元素的字段存在情况，只构造包含非null字段的JSON对象
• JSON_ARRAYAGG把所有构造好的对象重新聚合成一个数组，最终输出就是你要的：

  [{"sex": "male", "age": 20}, {"sex": "female"}, {"age": 26}]
  

方法二：使用JSON路径对象投影（MySQL 8.0.17+适用） #

如果你的MySQL版本在8.0.17及以上，可以用更简洁的JSON路径语法直接投影，但要注意：缺失的字段会以null值保留（如果能接受这一点的话）：

SELECT JSON_QUERY(your_json_column, '$.info[*].{"sex": "$.sex", "age": "$.age"}') AS result
FROM your_table
WHERE ...;

执行后结果会是：

[{"sex": "male", "age": 20}, {"sex": "female", "age": null}, {"sex": null, "age": 26}]

示例验证 #

假设你的表名为person_records，JSON列名为profile_data，完整的测试SQL（用方法一）如下：

SELECT JSON_ARRAYAGG(
    CASE
        WHEN sex IS NOT NULL AND age IS NOT NULL THEN JSON_OBJECT('sex', sex, 'age', age)
        WHEN sex IS NOT NULL THEN JSON_OBJECT('sex', sex)
        WHEN age IS NOT NULL THEN JSON_OBJECT('age', age)
        ELSE JSON_OBJECT()
    END
) AS result
FROM person_records,
     JSON_TABLE(
         profile_data,
         '$.info[*]' COLUMNS (
             sex VARCHAR(10) PATH '$.sex',
             age INT PATH '$.age'
         )
     ) AS jt;

执行后就能得到你期望的输出啦！

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