DEV Community

Murphy Randle
Murphy Randle

Posted on

So you put an escaped string into a JSON column...

Whoops! Let's pretend we made a mistake a while ago, and our application is writing escaped JSON strings into our Postgres JSON column instead of JSON objects. We can't use any JSON operators to select fields now! What are we to do?

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.

Top comments (9)

Collapse
 
gabrielchuan profile image
Gabriel Chuan

Thanks for this!

For anyone who wants to quickly update all their JSON strings (where it should be JSONB instead), here's a code snippet that I used:

UPDATE table_name
SET jsonb_column_name = (jsonb_column_name #>> '{}')::jsonb
Enter fullscreen mode Exit fullscreen mode
Collapse
 
ddo88 profile image
ddo88

thanks, this help me a lot

Collapse
 
directcode_pl profile image
Dominik Wlazlowski

Omg, just wasted 2h with playing around, and found this solution... God bless you, good man! :-)

Collapse
 
mikewyattdesign profile image
Mike Wyatt II • Edited

Thanks for posting this. Would not have solved my nested JSON problem without you.

Collapse
 
rodolfosilva profile image
Rodolfo Silva

THANKS!!!!!!!!!

Collapse
 
mrmurphy profile image
Murphy Randle

My pleasure!

Collapse
 
alinsky profile image
Vladimir Alinsky

Thank you so much!

Collapse
 
tokha profile image
Тохтар Ахметов

THANK YOU!