postgresql function - how to push new json object to json array?

Question

I need to push new json object to existing json.

response json ='{"success":[{"aaa":"bbb"}]}'::json;
newitem json ='{"ccc":"ddd"}'::json;

Final response json should like below

{"success":[{"aaa":"bbb"},{"ccc":"ddd"}]}

Here is the complete code:

DROP FUNCTION orgname.testfunction();
CREATE OR REPLACE FUNCTION orgname.testfunction()
RETURNS json
    LANGUAGE 'plpgsql'
    VOLATILE
AS $RESPONSE$
DECLARE
    response json ='{"success":[]}'::json;
    newitem json ='{"ccc":"ddd"}'::json;  
BEGIN 
    with c(response,newitem) as (values('{"success":[{"aaa":"bbb"}]}'::json,'{"ccc":"ddd"}'::json))
, m as (select json_array_elements(response->'success') from c union all select newitem from c)
select concat('{"success":',json_agg(json_array_elements),'}')::json from m;
    return '{"expecting":"result"}'::json;
END;
$RESPONSE$

Vao Tsun · Accepted Answer · 2018-04-23 12:08:48Z

1

with 9.4 solution will not look neat. smth like:

t=# with c(response,newitem) as (values('{"success":[{"aaa":"bbb"}]}'::json,'{"ccc":"ddd"}'::json))
, m as (select json_array_elements(response->'success') from c union all select newitem from c)
select concat('{"success":',json_agg(json_array_elements),'}')::json from m;
                   concat
--------------------------------------------
 {"success":[{"aaa":"bbb"}, {"ccc":"ddd"}]}
(1 row)

update

for your function:

t=# CREATE OR REPLACE FUNCTION orgname.testfunction()
RETURNS json
    LANGUAGE 'plpgsql'
    VOLATILE
AS $RESPONSE$
DECLARE
    response json ='{"success":[{"aaa":"bbb"}]}'::json;
    newitem json ='{"ccc":"ddd"}'::json;
BEGIN
  return (with c(_response,_newitem) as (values(response,newitem))
, m as (select json_array_elements(_response->'success') from c union all select _newitem from c)
select concat('{"success":',json_agg(json_array_elements),'}')::json from m);
END;
$RESPONSE$
;
CREATE FUNCTION
t=# select * from orgname.testfunction();
                testfunction
--------------------------------------------
 {"success":[{"aaa":"bbb"}, {"ccc":"ddd"}]}
(1 row)

edited Apr 23, 2018 at 12:08

answered Apr 23, 2018 at 11:28

Vao Tsun

52.4k13 gold badges114 silver badges149 bronze badges

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

2 Comments

Nadhas Over a year ago

ERROR: column reference "response" is ambiguous LINE 2: , m as (select json_array_elements(response->'success') from...

Vao Tsun Over a year ago

@Udhaya I rewrote the asnwer for you

Collectives™ on Stack Overflow

postgresql function - how to push new json object to json array?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related