when use json_agg(json_build_object(.......)) ,how to ensure json_build_object return empty list

Question

As title. I write some SQL code like below :

SELECT p.id, 
       json_agg(
                json_build_object('id', sp.id)
                ) AS subProducts
  FROM product p
  LEFT JOIN sub_product sp
    ON sp.product_id = p.id
 GROUP BY p.id

as will be observed within

the demo

but i get an issue, whenever product id = 2, then subProducts would be [{"id" : null}]

how to i ensure if nothing match, subProducts to be [],
i have a idea, add column count(subPrdocts) as childNum,
and check childNum is 0.
But not grace.

thanks all.

Answer 1

Try this :

SELECT p.id, 
       CASE WHEN jsonb_agg(sp.id) = '[null]' :: jsonb
         THEN '[]' :: jsonb 
         ELSE jsonb_agg(jsonb_build_object('id', sp.id)) 
       END AS subProducts
  FROM product p
  LEFT JOIN sub_product sp
    ON sp.product_id = p.id
 GROUP BY p.id

see dbfiddle