Postgres Pro
Downloads
Home   >   mailing lists

Re: cant get what I want from array_to_json (simple) - Mailing list pgsql-sql

From Michael Moore
Subject Re: cant get what I want from array_to_json (simple)
Date
Msg-id CACpWLjMjPuVM4Ms2owpGCOLr6vyVhP9RsQaOO1ZMnpitUjRfdQ@mail.gmail.com
Whole thread Raw
In response to Re: cant get what I want from array_to_json (simple)  ("David G. Johnston" <david.g.johnston@gmail.com>)
List pgsql-sql
Tree view
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.g.johnston@gmail.com> wrote:
On Wed, Dec 16, 2015 at 2:23 PM, Michael Moore <michaeljmoore@gmail.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.


David J.


pgsql-sql by date:

Previous
From: Adrian Klaver
Date:
Subject: Re: Help with complicated query (total SQL newb!)
Next
From: Michael Moore
Date:
Subject: array of composite type