Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

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?
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;
                Thank you so much, IF( COUNT(...), ... , ... ) is a really nice trick, it had to be an aggregated function but I couldn't figure out which and how to use! )
– Alex Kogan
                Jul 7, 2021 at 13:41
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;
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.
        
Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.