PostgreSQL unescape JSON string

Question

I'm trying to use the new JSON capabilites in PostgreSQL 9.3 and I'm looking for a function that unescapes JSON, the oposite of to_json(anyelement).

Below is a sample JSON:

{"single_comment": "Fred said \"Hi.\"" , 
"comments_array": ["Fred said \"Hi.\"", "Fred said \"Hi.\"", "Fred said \"Hi.\""]}  

The query:

SELECT json_array_elements(json_column->'comments_array')

returns as descibed in the documentation a SET OF JSON.

"Fred said \"Hi.\""
"Fred said \"Hi.\""
"Fred said \"Hi.\""

Is there a way to unescape the result, so I can have the following result:

Fred said "Hi."
Fred said "Hi."
Fred said "Hi." 

In the documentation I don't see any function that can help me. Unfortunately insalling the PLV8 is not a option for me.

Any ideas are highly appreciated.

Answer 1

I just ran across this issue myself, and here is how I approached it. I created a helper function that iterates over the array and uses the ->> operator using a subscript to get the text value back. If anyone knows a better way, I'm happy to hear about it, because this seems a bit klunky.

CREATE OR REPLACE FUNCTION json_text_array_to_pg_text_array(data json) returns text[] AS $$
DECLARE
    i integer;
    agg text[];
BEGIN
    FOR i IN 0..json_array_length(data)-1 LOOP
        agg := array_append(agg, data->>i);
    END LOOP;

    return agg;
END
$$ language plpgsql;

Then you can do things like:

test=# select json_text_array_to_pg_text_array('[ "hello","the\"re","i''m", "an", "array" ]'::json);
 json_text_array_to_pg_text_array 
----------------------------------
 {hello,"the\"re",i'm,an,array}
(1 row)

You could also make the function just return a setof text if you don't want to do deal with the arrays directly:

CREATE OR REPLACE FUNCTION json_text_array_to_row(data json) returns setof text AS $$
DECLARE
    i integer;
BEGIN
    FOR i IN 0..json_array_length(data)-1 LOOP
        return next data->>i;
    END LOOP;
    return;
END
$$ language plpgsql;

And then do this:

test=# select json_text_array_to_row('{"single_comment": "Fred said \"Hi.\"" ,"comments_array": ["Fred said \"Hi.\"", "Fred said \"Hi.\"", "Fred said \"Hi.\""]}'::json->'comments_array');
 json_text_array_to_row 
------------------------
 Fred said "Hi."
 Fred said "Hi."
 Fred said "Hi."
(3 rows)

Answer 2

select t.comments->>0 from 
(select jsonb_array_elements(your_table.json_column->'comments_array') as comments
from your_table) as t;