PostgreSQL unescape JSON string

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)

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

I've managed to achieve the result with little modification of the JSON:

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

Instead of having array of strings, now we use array of objects, and the following query works as i wanted to:

SELECT (json_array_elements(json_column->'comments_array'))->>'comment'

For now this is going to suite my needs, but if somebody knows a way how we can achieve the output from array of strings, please share it :)