Return JSON array with postgres functions

I’m new to Postgres functions. I’m trying to return part of JSON response similar to:

"ids":[
        
          "9f076580-b5f5-4e73-af08-54d5fc4b87c0",
          "bd34cfad-53c7-4443-bf48-280e34d76881"
           
       ]

This ids is stored in table unit and I query them as a part of subquery and then transform into JSON with the next query

SELECT coalesce(json_agg(row_to_json(wgc)), '[]'::json)
FROM (
         SELECT
            (
                SELECT COALESCE(json_agg(row_to_json(ids)), '[]'::json)
                FROM (SELECT json_agg(l.ids) as "id"
                      FROM unit
                     ) as ids
            )  as "ids",
             ......
         FROM companies c
               
 ) AS wgc;

The problem is that this query gives me extract object which I want to omit. “ids”:[ { “id”:[ “9f076580-b5f5-4e73-af08-54d5fc4b87c0”, “bd34cfad-53c7-4443-bf48-280e34d76881” ] } ]

How can omit this “id” object??

Answer

It’s a bit hard to tell how your table looks like, but something like this should work:

select jsonb_build_object('ids', coalesce(jsonb_agg(id), '[]'::jsonb))
from unit

---

I think you are overcomplicating things. You only need a single nesting level to get the IDs as an array. There is no need to use row_to_json on the array of IDs. The outer row_to_json() will properly take care of that.

SELECT coalesce(json_agg(row_to_json(wgc)), '[]'::json)
FROM (
  SELECT (SELECT json_agg(l.ids) FROM unit ) as ids
         .... 
  FROM companies c
) AS wgc;

The fact that the select ... from unit is not a co-related sub-query is a bit suspicious though. This means you will get the same array for each row in the companies table. I would have expected something like (select .. from unit u where u.??? = c.???) as ids