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.

Err... isn't "Fred said \"Hi.\"" just another way of writing 'Fred said "Hi."'`? — Denis de Bernardy
– Denis de Bernardy, Commented Oct 16, 2013 at 21:39
Have you tried json_each_text instead of json_array_elements ? — Ihor Romanchenko
– Ihor Romanchenko, Commented Oct 16, 2013 at 22:08
json_each_text works with key->value structure, i have an JSON array. — Dan
– Dan, Commented Oct 17, 2013 at 6:52
json_array_elements_text (instead of json_array_elements) does just that. Might not have been available in 2013 :-) — Bergi
– Bergi, Commented Jul 7, 2022 at 0:11

Michael · Accepted Answer · 2022-01-27 12:24:48Z

19

Find something simple on an other website: Source here

Details if link is dead:

select (ingredients #>> '{}')::jsonb->>'cheese' from pizza;

Do this ☝️. When you’ve got a table (pizza) with a jsonb column (ingredients), and you’ve accidentally put an escaped JSON string inside that column, instead of a JSON object. The value might look like this:

"{\"cheese\": \"mozzarella\"}"

See the quotes? That’s just a string at the root level inside of that JSON column. But what you really want looks like this:

{"cheese": "mozzarella"}

That’s better. But we’ve already made the mistake, so we’ve got to write a migration to fix the data. Here’s how we do it:

(ingredients #>> '{}')::jsonb

The #>> operator in Postgres gets a “JSON object at specified path as text” (PostgreSQL: Documentation: 9.3: JSON Functions and Operators). Here we’re passing in an empty path to say that we want Postgres to give us the unescaped string at the root level as a text value.

We can then cast that text value back to JSON like this: (stand-in-for-text-value)::jsonb. And then we have a normal, parsed JSON object at the root level that we can select fields off of.

answered Jan 27, 2022 at 12:24

Michael

1,21316 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

evan.bovie Over a year ago

Similarly, if you stored an unescaped JSON object with a key that has a value containing escaped JSON: select (ingredients ->> 'meats')::jsonb->>'left' from pizza; example stored data for ingredients column: { "meats": "{ \"left\": \"salami\", \"right\": \"guanciale\"}" }

bhirt · Accepted Answer · 2014-05-26 00:45:15Z

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)

expert · Accepted Answer · 2016-01-24 13:49:12Z

2

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

answered Jan 24, 2016 at 13:49

expert

30.4k31 gold badges119 silver badges223 bronze badges

Comments

Dan · Accepted Answer · 2013-10-18 07:37:11Z

0

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 :)

answered Oct 18, 2013 at 7:37

Dan

1931 gold badge2 silver badges8 bronze badges

Collectives™ on Stack Overflow

PostgreSQL unescape JSON string

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related