CODEWITHSUNDEEP
sqljsonpostgresql
Bohdan Myslyvchuk
Bohdan Myslyvchuk

Reputation: 1817

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??

Upvotes: 0

Views: 458

Answers (2)

user330315
user330315

Reputation:

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

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271003

I don't fully understand your question. This code:

SELECT (
            SELECT COALESCE(json_agg(row_to_json(ids)), '[]'::json)
            FROM (SELECT json_agg(l.ids) as "id"
                  FROM unit l
                 ) as ids
        )  as "ids"

Returns:

[{"id":["9f076580-b5f5-4e73-af08-54d5fc4b87c0", "bd34cfad-53c7-4443-bf48-280e34d76881as"]}]

which seems to be what you want.

Here is a db<>fiddle.

Something else in your query is returning a JSON object that has ids as a field. You seem to want to construct the object you want.

Upvotes: 1

Related Questions