Return JSON array with postgres functions

Question

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

What is l? Your query is incomplete.

Gordon Linoff
– Gordon Linoff

2021-01-04 11:36:57 +00:00
Commented Jan 4, 2021 at 11:36 — Gordon Linoff
– Gordon Linoff, Commented Jan 4, 2021 at 11:36

score 1 · Accepted Answer · 2021-01-04 13:19:08Z

1

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

edited Jan 4, 2021 at 13:19

answered Jan 4, 2021 at 11:39

user330315

Sign up to request clarification or add additional context in comments.

1 Comment

Bohdan Myslyvchuk Over a year ago

you are right. Found a solution SELECT coalesce(json_agg(row_to_json(wgc)), '[]'::json) FROM ( SELECT ( SELECT COALESCE(json_agg( ids), ..... Basically one level was redundant

Gordon Linoff · Accepted Answer · 2021-01-04 11:47:00Z

1

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.

answered Jan 4, 2021 at 11:47

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

1 Comment

Bohdan Myslyvchuk Over a year ago

updated a little bit. Sorry I can't a post a real code a the whole query. Thanks for your help. Basically it is nested select inside another inside postgres function.

Collectives™ on Stack Overflow

Return JSON array with postgres functions

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related