If statement in-CodePudding

I am aggregating sql from a select query into a json array like this:

CONCAT('[',GROUP_CONCAT(JSON_OBJECT('value', measured_at, 'day', datediff(measurements.measured_at, athletes.tracking_started_at))), ']') as `values`

At times the json object returns a negative value from the datediff. What I'd like to do is not return a json object at all in that case.

eg. what I get now:

[{ "value": 23, "day": 12 }, { "value": 23, "day": -25 }, { "value": 23, "day": 40 }]

what I'd like to get:

[{ "value": 23, "day": 12 }, { "value": 23, "day": 40 }]

How can I do this?

CodePudding user response：

Use a CASE expression inside GROUP_CONCAT():

GROUP_CONCAT(
  CASE 
    WHEN datediff(measurements.measured_at, athletes.tracking_started_at) >= 0 
      THEN JSON_OBJECT('value', measured_at, 'day', datediff(measurements.measured_at, athletes.tracking_started_at))
  END
)