SQL使用GROUP_CONCAT拼接JSON数组如何过滤datediff为负的对象

方案1：聚合前预过滤（优先推荐） #

该方案在数据聚合前就通过WHERE子句过滤掉day为负的行，性能最优，适合当前查询不需要保留负day行用于其他计算的场景，只需在查询的WHERE条件中新增判断规则即可：

-- 原有查询其他部分保持不变
CONCAT('[',GROUP_CONCAT(JSON_OBJECT('value', measured_at, 'day', datediff(measurements.measured_at, athletes.tracking_started_at))), ']') as `values`
-- 新增WHERE过滤条件
WHERE datediff(measurements.measured_at, athletes.tracking_started_at) >= 0

方案2：聚合时条件过滤 #

如果你的查询中还有其他需要用到负day行的计算逻辑，不能全局添加WHERE条件，可以利用GROUP_CONCAT会自动忽略NULL值的特性，仅在拼接JSON时过滤无效行：

CONCAT('[',
  GROUP_CONCAT(
    -- 仅day非负的行才生成JSON对象，否则返回NULL被忽略
    IF(
      datediff(measurements.measured_at, athletes.tracking_started_at) >= 0,
      JSON_OBJECT('value', measured_at, 'day', datediff(measurements.measured_at, athletes.tracking_started_at)),
      NULL
    )
  ),
']') as `values`

兼容优化 #

如果存在所有行都被过滤的极端场景，可以在外层添加IFNULL判断，避免返回异常格式，最终输出空数组：

IFNULL(
  CONCAT('[',
    GROUP_CONCAT(
      IF(
        datediff(measurements.measured_at, athletes.tracking_started_at) >= 0,
        JSON_OBJECT('value', measured_at, 'day', datediff(measurements.measured_at, athletes.tracking_started_at)),
        NULL
      )
    ),
  ']'),
  '[]'
) as `values`

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