Re: Postgresql - Json syntax in INSERT RETURNING clause with INTO

i guess you asked for something like this

WITH jx AS ( SELECT * FROM jsonb_to_recordset('{
"action" : "insert",
"items" : [
{
"xid" : "0",
"mykey" : "G",
"myvalue" : "Golf Stuff"
},
{
"xid" : "0",
"mykey" : "F",
"myvalue" : "Football"
}
]
}'::jsonb ->'items') AS jc(xid bigint, mykey text, myvalue text))
, *cte as (insert into jt.testx(mykey, myvalue) select mykey, myvalue from
jx returning * ) *
select json_object_agg(mykey, myvalue) from cte;

json_object_agg
------------------------------------------
{ "G" : "Golf Stuff", "F" : "Football" }

if this is correct, then it just means put your insert into a cte

On Sat, 29 May 2021 at 00:03, Ron Clarke <rclarkeai(at)gmail(dot)com> wrote:

> Hi,
>
> I'm trying to push some values from a json document onto a database table,
> and in the function return the values that have been added in the
> return json document.
>
> I'm having problems 'aggregating' the return value. Probably because I've
> taken the wrong approach.
>
> Here is a simplified expression of what I've been looking at:
>
> Thanks for taking a look, and please let me know where I'm going wrong:-
>
> -- create test schema
> CREATE SCHEMA IF NOT EXISTS jt;
> -- create test table
> CREATE TABLE jt.testx (
> xid bigserial,
> mykey varchar(20),
> myvalue varchar(20),
> CONSTRAINT testx_primary_key PRIMARY KEY( mykey )
> );
>
> -- drop test function
> DROP FUNCTION jt.testx_put (jsonb);
>
> -- create test function
> CREATE FUNCTION jt.testx_put ( jdata jsonb )
> RETURNS jsonb
> AS $function$
> DECLARE
> put_result jsonb[];
> BEGIN
>
> WITH jx AS ( SELECT * FROM jsonb_to_recordset(jdata ->'items')
> as jc(xid bigint, mykey text, myvalue text))
>
> INSERT INTO jt.testx (
> mykey
> , myvalue
> )
> SELECT
> COALESCE(NULLIF(jx.mykey,''),'NEW' )::text AS mykey
> , COALESCE(NULLIF(jx.myvalue,''),'ZZZ' )::text AS myvalue
> FROM jx
> ON CONFLICT ON CONSTRAINT testx_primary_key
> DO UPDATE
> SET
> mykey = EXCLUDED.mykey
> , myvalue = EXCLUDED.myvalue
> RETURNING
> -- so how do I get a single Json Object back if I insert / update multiple
> records ??
> -- This gives me one row for each item I give it...
> jsonb_build_object(
> 'xid', xid,
> 'mykey', mykey,
> 'myvalue', myvalue
> )
> INTO put_result;
> RETURN put_result;
>
> END;
> $function$
> language plpgsql;
>
>
> -- If we call the function with some test data:
> select * from jt.testx_put('{
> "action" : "insert",
> "items" : [
> {
> "xid" : "0",
> "mykey" : "G",
> "myvalue" : "Golf Stuff"
> },
> {
> "xid" : "0",
> "mykey" : "F",
> "myvalue" : "Football"
> }
> ]
> }'::jsonb )
> -- This results in an error
> -- ERROR: query returned more than one row
> -- i.e. I'm trying to return json?
> --
> -- IF I invoke the same query with the same json...this returns two json
> rows...
> --
> WITH jx AS ( SELECT * FROM jsonb_to_recordset('{
> "action" : "insert",
> "items" : [
> {
> "xid" : "0",
> "mykey" : "G",
> "myvalue" : "Golf Stuff"
> },
> {
> "xid" : "0",
> "mykey" : "F",
> "myvalue" : "Football"
> }
> ]
> }'::jsonb ->'items') AS jc(xid bigint, mykey text, myvalue text))
> INSERT INTO jt.testx (
> mykey
> , myvalue
> )
> SELECT
> COALESCE(NULLIF(jx.mykey,''),'NEW' )::text AS mykey
> , COALESCE(NULLIF(jx.myvalue,''),'ZZZ' )::text AS myvalue
> FROM jx
> ON CONFLICT ON CONSTRAINT testx_primary_key
> DO UPDATE
> SET
> mykey = EXCLUDED.mykey
> , myvalue = EXCLUDED.myvalue
> RETURNING
> -- how do I get a single Json Object back ???
> -- This gives me one row for each item I give it...
> jsonb_build_object(
> 'xid', xid,
> 'mykey', mykey,
> 'myvalue', myvalue
> )
>
> -- So how do I effectively merge the output of a returning clause into a
> single json value
> -- is this possible ??
>
>
> Thanks and regards
> Ron
>
>
>

--
Thanks,
Vijay
Mumbai, India