MySQL查询嵌套JSON字段返回匹配JSON子集实现方法

以下方案基于MySQL 8.0及以上版本编写，用到原生JSON处理函数，假设你的业务表名为product_records，字段为id、product_logs（建议字段类型设为JSON，存字符串也可通过类型转换兼容）、created_at。

返回形式1：直接返回匹配到的产品对象 #

通过JSON_TABLE将products数组拆分为多行，匹配指定product_id后直接返回对应的产品对象，同时保留原行其他字段：

SELECT 
  t.id,
  j.matched_product AS product_logs,
  t.created_at
FROM product_records t
JOIN JSON_TABLE(
  t.product_logs,
  '$.products[*]' COLUMNS(
    matched_product JSON PATH '$',
    product_id INT PATH '$.product_id'
  )
) j ON j.product_id = 124 -- 这里替换为你要搜索的product_id值
WHERE t.id = 1; -- 可按需调整筛选条件，不需要限定行id可删除该条件

执行后product_logs字段返回值即为目标结构：{"logs":[],"product_id":124,"time":"2022-07-13 01:31:00"}

返回形式2：保留原JSON外层结构，仅保留匹配的数组元素 #

匹配到对应产品对象后，通过JSON_ARRAYAGG重新组装数组，再包装为和原结构一致的JSON对象：

SELECT 
  t.id,
  JSON_OBJECT('products', JSON_ARRAYAGG(j.matched_product)) AS product_logs,
  t.created_at
FROM product_records t
JOIN JSON_TABLE(
  t.product_logs,
  '$.products[*]' COLUMNS(
    matched_product JSON PATH '$',
    product_id INT PATH '$.product_id'
  )
) j ON j.product_id = 124 -- 这里替换为你要搜索的product_id值
WHERE t.id = 1 -- 可按需调整筛选条件，不需要限定行id可删除该条件
GROUP BY t.id, t.created_at;

执行后product_logs字段返回值即为目标结构：

{
    "products": [
        {
            "logs": [],
            "product_id": 124,
            "time": "2022-07-13 01:31:00"
        }
    ]
}

注意事项：

• MySQL 5.x版本无JSON_TABLE函数，无法通过原生语句直接实现该需求，建议升级版本或在业务层做JSON过滤
• 若product_logs字段为普通字符串类型，将语句中所有t.product_logs替换为CAST(t.product_logs AS JSON)即可正常运行
• 若单条记录的products数组中存在多个相同product_id的元素，查询会返回所有匹配项，第二种形式会将所有匹配元素放入products数组

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