Question 89511 in PostgreSQL Question posted in
The official documentation can be found here.

Getting an [null] response from SQL query using json_agg – Postgresql

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?

Tags:

2

Answers


  1. 0

    Add a FILTER clause:

    json_agg(...) FILTER (WHERE socials.id IS NOT NULL)
    Login or Signup to reply.
  2. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search