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?

Duplicate of stackoverflow.com/questions/49960156/…

Barmar
– Barmar

2018-12-28 00:55:43 +00:00
Commented Dec 28, 2018 at 0:55 — Barmar
– Barmar, Commented Dec 28, 2018 at 0:55

Barmar · Accepted Answer · 2018-12-28 00:50:33Z

32

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;

answered Dec 28, 2018 at 0:50

Barmar

789k57 gold badges554 silver badges669 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Ragu Natarajan Over a year ago

Working Perfect.. THanks

Alex Kogan Over a year ago

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! )

Muhammad Ali Over a year ago

This also worked for me

sticky bit · Accepted Answer · 2018-12-28 01:00:12Z

7

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;

answered Dec 28, 2018 at 1:00

sticky bit

37.7k12 gold badges34 silver badges46 bronze badges

Comments

lemon · Accepted Answer · 2022-05-17 18:21:50Z

1

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.

edited May 17, 2022 at 18:21

lemon

15.6k6 gold badges23 silver badges42 bronze badges

answered May 17, 2022 at 10:33

whup

111 bronze badge

Collectives™ on Stack Overflow

add condition to mysql json_arrayagg function

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related