add condition to mysql json_arrayagg function

Question

I have a json query that gives me json of a joined table of person and pets:

SELECT json_object(
  'personId', p.id,
  'pets', json_arrayagg(json_object(
    'petId', pt.id,
    'petName', pt.name
  ))
  )
FROM person p LEFT JOIN pets pt
ON p.id = pt.person_id
GROUP BY p.id;

my issue is that person can have 0 or more pets, and when a person have 0 pets I get list with 1 empty pet, and what I would like to get in that case is empty list.

this is what I get:

{
  "personId": 1,
  "pets": [
    {
      "petId": null,
      "petName": ""
    }
  ]
}

and I need:

{
  "personId": 1,
  "pets": []
}

is that possible?

Answer 1

The problem is that LEFT JOIN still returns columns from the table you're joining with, it just sets their values to NULL.

You can use IF to test COUNT(pt.id), as this won't count null values.

SELECT json_object(
  'personId', p.id,
  'pets', IF(COUNT(pt.id) = 0, JSON_ARRAY(),
             json_arrayagg(json_object(
                'petId', pt.id,
                'petName', pt.name
                )
            ))
  )
FROM person p LEFT JOIN pets pt
ON p.id = pt.person_id
GROUP BY p.id;