I tried at least a hundred combinations,including json_build_object and json_build_array, but no matter what I did, I always ended up with the name of the TYPE variables ("ckey" and c"value") in my JSON string. I tried to simply by doing:
CREATE TYPE qsn_app.key_value_pair AS(ckey text, cvalue text);
CREATE TYPE qsn_app.key_value_pair_tab as (kv key_value_pair[]);
DO $$declare
arr qsn_app.key_value_pair_tab ;
str text;
begin
arr.kv := array_cat( array_agg( '({''meeting'', ''lunch''})'::key_value_pair),
array_agg( '({''xmeeting'', ''xlunch''})'::key_value_pair)) ;
RAISE NOTICE '#1 %', arr;
end$$;
The result is still:
NOTICE: #1 ("{""({'meeting',\\"" 'lunch'}\\"")"",""({'xmeeting',\\"" 'xlunch'}\\"")""}")
I just want an array of a composite type which can be used in place of the subselect in :
SELECT * FROM json_populate_record(null::qsn_app.tdatacapture,
(SELECT '{"datacapturekey":1,
"dataheader2tdataheader":7777777,
"status2trecordstatus":"A",
"static01":"test"}'::json));
I feel like I'm close.
Regards, Mike