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

TL;DR

-- Use this operator #>>
(escaped_string #>> '{}')::jsonb

Not Quite TL;DR

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?

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

>>> select inventory #>>'{}' from Restaurants;
{"chairs": 30}
>>> 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.