Getting an [null] response from SQL query using json_agg

Question

Still learning SQL/PostgreSQL. I am struggling to figure out how to stop returning an array with a null value as [null] and instead just return an empty array.

COALESCE(
  json_agg(
    CASE 
      WHEN socials.id IS NULL THEN NULL 
      ELSE json_build_object('id', socials.id, 'name', socials.social_id, 'url', socials.url)
    END
  ), '[]'
) AS socials

What am I missing or doing wrong? From what I understood, COALESCE is used to basically replace when the return value is null/false. Am I wrong in thinking this way? Also what might be the issue with my SQL query?

Answer 1

The outer COALESCE does not do what you want since json_agg() never returns null, unless no input row qualifies. And COALESCE only kicks in for null input - not null/false like you put it! (An array containing a single null value as array element is not the same as null!)

Use an aggregate FILTER clause instead:

SELECT COALESCE(json_agg(json_build_object('id', socials.id, 'name', socials.social_id, 'url', socials.url))
                FILTER (WHERE socials.id IS NOT NULL)
              , '[]') AS socials

Now, COALESCE can make sense, since you can get result rows, but the added FILTER clause can make the result of json_agg() null.

Depending on your undisclosed query, you might instead add an outer WHERE clause:

SELECT json_agg(json_build_object('id', socials.id, 'name', socials.social_id, 'url', socials.url)) AS socials
FROM   ...
WHERE  socials.id IS NOT NULL

In this case, there is no point in adding an outer COALESCE, since you would get no row if the added WHERE clause filters all input rows.

Answer 2

Add a FILTER clause:

json_agg(...) FILTER (WHERE socials.id IS NOT NULL)