如何在MySQL中筛选JSON数组中符合条件的指定对象？

问题描述 #

我有一个名为jobs的JSON列，结构如下：

[
    {
      "id": "1",
      "done": "100",
      "target": "100",
      "startDate": "123123132",
      "lastAction": "123123132",
      "status": "0"
    },
    {
      "id": "2",
      "done": "10",
      "target": "20",
      "startDate": "2312321",
      "lastAction": "2312321",
      "status": "1"
    }
]

希望筛选满足target > done、status != 0且lastAction为昨天的项，同时希望直接获取原JSON对象，避免手动重构。已知JSON_TABLE()可以提取数据筛选，但之前的用法需要重新构造对象，不够灵活，想问MySQL是否能实现这类数组筛选？

解决方案 #

MySQL完全可以实现这类需求，且无需手动重构JSON对象，以下是两种实用方案：

方案1：JSON_TABLE提取原对象+聚合（推荐） #

利用JSON_TABLE()的JSON PATH '$'直接保留原数组元素的完整JSON结构，筛选后通过JSON_ARRAYAGG()重新组合成符合要求的JSON数组。

假设表名为task_table，示例SQL如下：

SELECT JSON_ARRAYAGG(job_item.obj) AS filtered_jobs
FROM task_table t,
     JSON_TABLE(
         t.jobs,
         '$[*]' COLUMNS (
             obj JSON PATH '$', -- 直接提取原JSON对象
             done INT PATH '$.done', -- 转换为数值用于比较
             target INT PATH '$.target',
             status INT PATH '$.status',
             last_action_ts BIGINT PATH '$.lastAction' -- 提取时间戳
         )
     ) job_item
WHERE 
    job_item.target > job_item.done
    AND job_item.status != 0
    AND DATE(FROM_UNIXTIME(job_item.last_action_ts)) = DATE_SUB(CURDATE(), INTERVAL 1 DAY);

• 优势：既支持复杂的筛选条件（数值比较、时间判断等），又能直接保留原对象的所有字段，无需手动拼接JSON结构，灵活性拉满。
• 注意：如果lastAction是字符串格式的日期而非时间戳，需要调整日期转换逻辑（比如用STR_TO_DATE()）。

方案2：利用JSON路径表达式与序数关联 #

如果需要更贴近原生JSON操作的方式，可以结合JSON_TABLE的ORDINALITY（元素下标）来匹配原数组中的元素：

SELECT JSON_EXTRACT(t.jobs, CONCAT('$[', job_item.pos - 1, ']')) AS filtered_job
FROM task_table t,
     JSON_TABLE(
         t.jobs,
         '$[*]' COLUMNS (
             pos FOR ORDINALITY, -- 获取数组元素的位置（从1开始）
             done INT PATH '$.done',
             target INT PATH '$.target',
             status INT PATH '$.status',
             last_action_ts BIGINT PATH '$.lastAction'
         )
     ) job_item
WHERE 
    job_item.target > job_item.done
    AND job_item.status != 0
    AND DATE(FROM_UNIXTIME(job_item.last_action_ts)) = DATE_SUB(CURDATE(), INTERVAL 1 DAY);

如果需要将结果合并为一个JSON数组，外层套上JSON_ARRAYAGG()即可。

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