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
.