Thread: json_array_elements_text?

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

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.

Here's another approach to implement json_array_elements_text, using
the field extraction operator to extract all fields one-by-one. But
for large arrays this is likely slower, as it needs to parse the whole
JSON string for each array element, leading to O(n^2) complexity.

CREATE FUNCTION json_array_elements_text(json) RETURNS SETOF text
IMMUTABLE LANGUAGE sql
AS $$ SELECT $1->>i FROM generate_series(0, json_array_length($1)-1) i; $$;

db=# select json_array_elements_text('["hello",1.3,"\u2603"]');
 json_array_elements_text
--------------------------
 hello
 1.3
 ☃

Regards,
Marti