Re: json_array_elements_text? - Mailing list pgsql-general

From Marti Raudsepp
Subject Re: json_array_elements_text?
Date
Msg-id CABRT9RBTKzSWvuwd7ztXx=b7Sh0Z4gutdXPeE=j30sfV5acCMA@mail.gmail.com
Whole thread Raw
In response to json_array_elements_text?  (Laurence Rowe <l@lrowe.co.uk>)
Responses Re: json_array_elements_text?  (Marti Raudsepp <marti@juffo.org>)
List pgsql-general
On Fri, Jan 17, 2014 at 10:20 AM, Laurence Rowe <l@lrowe.co.uk> wrote:
> I'm trying to unpack a json array into it's constituent text values so I can
> join them to a table. I can successfully unpack json values, but am having
> trouble converting these to text so I can cast them to the UUIDs needed for
> the join.

You're right, this seems to be a glaring omission in our JSON support.
I can't see any patches queued for 9.4 that would implement this,
either.

> http://dba.stackexchange.com/questions/54283/how-to-turn-json-array-into-postgres-array
> suggest using trim, and this does work:

This trick doesn't take care of properly unescaping the JSON string,
although it will work for UUID values.

This solution is just as hacky, but you can use array extraction
functions to properly convert it to text:

CREATE FUNCTION json_text(json) RETURNS text IMMUTABLE LANGUAGE sql
AS $$ SELECT ('['||$1||']')::json->>0 $$;

Regards,
Marti


pgsql-general by date:

Previous
From: Andreas Lubensky
Date:
Subject: Re: Positional parameters and question mark notation
Next
From: Marti Raudsepp
Date:
Subject: Re: json_array_elements_text?