add condition to mysql json_arrayagg function

user_6568751 0
Viewed 6215

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?

mysql
3 Answers

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;
user_14918950

Another possibility is to put the aggregation in a correlated subquery and use coalesce() to replace it with an empty array if no rows exist.

SELECT json_object('personID', p.id,
                   'pets', coalesce((SELECT json_arrayagg(json_object('petId', t.id,
                                                                      'petName', t.name))
                                            FROM pets t
                                            WHERE t.person_id = p.id),
                                    json_array()))
       FROM person p;
user_96614240

Adding another option:

select IFNULL( /*expression of select ...JSON_ARRAYAGG(
                                    JSON_OBJECT(....*/,JSON_ARRAY()) jarrayaggAlias

Cleaner below:

IFNULL( expression, ,JSON_ARRAY()) jarrayaggAlias

Result:

/* { jarrayaggAlias: []  }*/

If you swap alt_value for IFNULL to select [], then all your results will be stringyfied.

user_176049130
Related