Concatenate multiple json objects into json array

Question

I'm trying to build JSON objects in a loop and store those objects in a JSON array.

I tried to use array_append, json_agg, json_object_agg, but I couldn't get the expected result. Please find the code snippets below.

    RETURNS json
    as
    $$
    DECLARE
    x text;
    obj json;
    result json;
    y text;
    nums json;
    BEGIN
    FOREACH x IN ARRAY arr
    loop
        -- doing some id conversion for x
    obj = json_build_object('id',x);
    result =  json_build_array(obj,result);
    END LOOP;
    return result;
    end;
    $$
    LANGUAGE plpgsql;

The expected output here is :

[
{"id" : "596339663235613739646562"},
{"id" : "506130306666363361633763"},
{"id" : "526362363532646435613438"},
{"id" : "543639333134353433393631"}]

But the actual output I get :

[[[[null, {"id" : "596339663235613739646562"}], 
        {"id" : "506130306666363361633763"}], 
    {"id" : "526362363532646435613438"}], 
    {"id" : "543639333134353433393631"}]

A sample input for function is :

{596339663235613739646562,506130306666363361633763,526362363532646435613438,543639333134353433393631}

Any help is greatly appreciated. Thanks.

Edouard · Accepted Answer · 2023-01-09 10:23:13Z

1

Try this :

SELECT jsonb_agg(jsonb_build_object('id', a :: text))
  FROM unnest('{596339663235613739646562,506130306666363361633763,526362363532646435613438,543639333134353433393631}' :: text[]) AS a

and an attemp to implement your function favoritesArray in sql (but with no guarantee, you have to adapt this suggestion to your exact need, or provide more info about your database schema and the expected result of your function) :

CREATE OR REPLACE FUNCTION favoritesArray(arr text[])
RETURNS json LANGUAGE sql as $$
SELECT json_agg(json_build_object('id', lpad(x , 32, '0') :: text, 'logicalPath', t1.name || t2.path))
  FROM unnest(arr) AS x
 INNER JOIN schema.table2 AS t2
    ON t2.key = x
 INNER JOIN schema.table1 AS t1
    ON t1.key = t2.source ;
$$ ;

see dbfiddle

edited Jan 9, 2023 at 10:23

answered Jan 7, 2023 at 9:49

Edouard

7,1291 gold badge11 silver badges24 bronze badges

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

5 Comments

Vasavi Over a year ago

hi @Edouard, this is working but, is there any way to append the objects inside the above function? because input for this function is a subquery again and this whole thing is embedded inside another query,

Vasavi Over a year ago

CREATE OR REPLACE FUNCTION favoritesArray(arr text[])     RETURNS json     as     $$     DECLARE     BEGIN     FOREACH x IN ARRAY arr     loop     lpath_1 = name from schema.table1 where key = (select source from schema.table2 where key = x);     lpath_2 = path from schema.table2 where key = x;     lpath = path_1 || lpath_2;     y = lpad(x ,32 , '0')     obj = json_build_object('id',y :: text, 'logicalPath',lpath);     --result := json_agg(obj);     result =  json_build_array(result,obj);     END LOOP;     return result;     end;     $$     LANGUAGE plpgsql;

Vasavi Over a year ago

I need to perform some processing on each value of the array, so could you please let me know if there is any way to do this inside the function, to aggregate all objects

Edouard Over a year ago

See my answer updated. I can't be more accurate until you share the details of your database schema and the expected result of your function favoritesArray.

Vasavi Over a year ago

thanks, I made changes according to my schema and table name. and it worked. Thanks

Collectives™ on Stack Overflow

Concatenate multiple json objects into json array

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related