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