Postgresql - Json syntax in INSERT RETURNING clause with INTO

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