How To Convert an Escaped JSON String in PostgreSQL to a JSON Object

How can we convert an escaped JSON string to a JSON object in PostreSQL?

Escaped JSON strings are tough to work with

Suppose we run a select query on a table and receive the following as a response:

"{\"chairs\": 30}"

Since the above is a string, we can’t operate on it as a JSON object.

So how can we query the table to get the value 30?

Convert escaped string to JSON with #>>

In order to access the object as {"chairs": 30}, we can use the #>> operator.

select inventory #>>'{}' from Restaurants;
// {"chairs": 30}

We can then target the value 30 using a type cast (::) to jsonb along with the JSON query operator (->>), which will return the JSON object field as text.

select (inventory #>>'{}')::jsonb->>'chairs' from Restaurants;
// 30

According to PostgreSQL documentation, #>> gets a JSON object at specified path as text.

What we’re doing is passing in an empty path to obtain the unescaped string at the root level as a text value, which we can then convert to json or jsonb.