Re: 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.

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