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.

CodePudding user response：

SELECT p.id, 
       coalesce(json_agg(
            json_build_object('id', sp.id)
                ) filter (where sp.id is not null ), '[]')AS subProducts
  FROM product p
 left JOIN sub_product sp
    ON sp.product_id = p.id
 GROUP BY p.id

filter and coalesce are your friends

CodePudding user response：

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