Re: cant get what I want from array_to_json (simple)

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

On Wed, Dec 16, 2015 at 1:42 PM, David G. Johnston <
david(dot)g(dot)johnston(at)gmail(dot)com> wrote:

> On Wed, Dec 16, 2015 at 2:23 PM, Michael Moore <michaeljmoore(at)gmail(dot)com>
> wrote:
>
>> CREATE TYPE key_value_pair AS
>> (key text,
>> value text);
>>
>> DO $$declare
>> arr qsn_app.key_value_pair[];
>> pcolumn_values_i json;
>> begin
>> arr[0] := ('this','that');
>> arr[1] := ('another','one');
>> pcolumn_values_i := array_to_json( arr );
>> RAISE NOTICE '#1 %', pcolumn_values_i;
>> end$$;
>>
>> the 'NOTICE' shows:
>> NOTICE: #1
>> [{"key":"this","value":"that"},{"key":"another","value":"one"}]
>> what I want to see is:
>> NOTICE: #1 {"this":"that","another":"one"}
>>
>>
>>
> ​The "json(_build)_object​()" functions seems like they better match your
> need.
>
> http://www.postgresql.org/docs/current/static/functions-json.html
>
> David J.
>
>