Need json_unnest_as_text()

[David Wheeler]

json_unnest() is great:

david=# select json_unnest('["foo", "bar"]');
 json_unnest 
-------------
 "foo"
 "bar"

But what if I want those values as text rather than JSON? Conversion to text keeps the double-quotes:

david=# select e::text from json_unnest('["foo", "bar"]') e;
   e   
-------
 "foo"
 "bar"

I think we need a way to unnest as text, similar to how we fetch text values:

david=# select json_unnest_as_text('["foo", "bar"]');
 json_unnest_as_text 
---------------------
        foo
        bar

[David Wheeler]

Here's an SQL version in the meantime:

CREATE OR REPLACE FUNCTION json_unnest_as_text(
    JSON
) RETURNS SETOF TEXT LANGUAGE SQL IMMUTABLE AS $$
    SELECT UNNEST(ARRAY(SELECT $1 ->> i FROM generate_series(0, json_array_length($1) -1) i));
$$;

[Andrew Dunstan]

needs to be a separate extension as it's not part of the upstream changes