I use json_build_object and it shows me this:

[{"id" : null, "name" : null, "tag_id" : null}]

but I want to show an empty array when nothing is found and to remove all null values

How can I do it ?

SELECT 
l.id,
JSON_AGG(JSON_BUILD_OBJECT('id', la.id, 'name', la.name, 'lag_id', la.lag_id)) as attr_groups

FROM location l

LEFT JOIN location_attr_groups lag
ON lag.id = l.l_attr_group_id

LEFT JOIN location_attr la
ON la.lag_id = lag.id

I tried json_strip_nulls but it shows this

[{}]

this is not an empty array

I use json_build_object and it shows me this:

[{"id" : null, "name" : null, "tag_id" : null}]

but I want to show an empty array when nothing is found and to remove all null values

How can I do it ?

SELECT 
l.id,
JSON_AGG(JSON_BUILD_OBJECT('id', la.id, 'name', la.name, 'lag_id', la.lag_id)) as attr_groups

FROM location l

LEFT JOIN location_attr_groups lag
ON lag.id = l.l_attr_group_id

LEFT JOIN location_attr la
ON la.lag_id = lag.id

I tried json_strip_nulls but it shows this

[{}]

this is not an empty array

• postgresql

• Don't use a LEFT join? – Bergi Commented Mar 14 at 22:47
• Do you use WHERE l.id = $1 or GROUP BY l.id? Otherwise the SELECT l.id wouldn't work afaics – Bergi Commented Mar 14 at 22:51

3 Answers 3

I recommend using jsonb instead of json. Then you can write:

jsonb_agg(NULLIF(jsonb_strip_nulls(jsonb_build_object(...)), '{}'))

NULLIF doesn't work with json, because there is no equality operator for that type.

1. If that's all fields of location_attr, you don't need to manually construct the object. The jsonb_agg() function will convert whole rows automatically.
2. Since you're left joining, you can filter out the empty rows from the input of the aggregate function.

SELECT l.id
     , COALESCE(JSONB_AGG(la)FILTER(WHERE la.lag_id IS NOT NULL),'[]') AS attr_groups
FROM location AS l
LEFT JOIN location_attr_groups AS lag
  ON lag.id = l.l_attr_group_id
LEFT JOIN location_attr AS la
  ON la.lag_id = lag.id;

Another example:

select v.id
     , coalesce( jsonb_agg(v2)
                 filter(where v2.id is not null)
                ,'[]')
from(values(1)
          ,(2)
          ,(3))as v(id)
left join(values(1,11)
               ,(1,12)
               ,(3,31)
               ,(3,32))as v2(id,x)
  on v.id=v2.id
group by v.id;

fiddle

I would recommend to use a subquery with normal joins, not a LEFT join with aggregates and grouping:

SELECT 
  l.id,
  (
    SELECT JSON_AGG(JSON_BUILD_OBJECT(
      'id', la.id,
      'name', la.name,
      'lag_id', la.lag_id
    ))
    FROM location_attr_groups lag
    JOIN location_attr la ON la.lag_id = lag.id
    WHERE lag.id = l.l_attr_group_id
  ) AS attr_groups
FROM location l

This will not run the JSON_AGG over one row despite no location_attr being found. However, notice that it will return NULL instead of an empty array when there is no location_attr row.

To avoid that, you can either COALESCE it to an empty array:

SELECT 
  l.id,
  COALESCE((
--^^^^^^^^^
    SELECT JSON_AGG(JSON_BUILD_OBJECT(
      'id', la.id,
      'name', la.name,
      'lag_id', la.lag_id
    ))
    FROM location_attr_groups lag
    JOIN location_attr la ON la.lag_id = lag.id
    WHERE lag.id = l.l_attr_group_id
  ), '[]'::json) AS attr_groups
--   ^^^^^^^^^^^
FROM location l

or use the ARRAY constructor instead of an aggregate function:

SELECT 
  l.id,
  ARRAY(
--^^^^^
    SELECT JSON_BUILD_OBJECT(
      'id', la.id,
      'name', la.name,
      'lag_id', la.lag_id
    )
    FROM location_attr_groups lag
    JOIN location_attr la ON la.lag_id = lag.id
    WHERE lag.id = l.l_attr_group_id
  ) AS attr_groups
FROM location l

which will however return a postgres array (json[]) not a json array value. Wrap it in to_json if that's an issue.