How to query nested json strings in jsonb field in postgres?

In our database, one JSONB column contains an object which has a property which is a string encoded JSON object. I need to deserialize this string during the query and inspect its values.

create table datas (id int, data jsonb);
insert into datas (id, data) values (1, '{"key1": "{\"foo\": \"bar\"}"}');

I can select the string value, but then casting that into a JSONB object doesn't work. These queries all return null for the foo.

select data->'key1'->'foo' from datas;
select to_json(data->'key1')->'foo' from datas;
select (data->'key1')::jsonb->'foo' from datas;

dbfiddle: https://dbfiddle.uk/?rdbms=postgres_9.6&fiddle=9c0a9f7f1323a35051daba5177e52f57

You need extract the value as text using the ->> operator, only then can you cast it back to a json or jsonb value:

select (data ->> 'key1')::json ->> 'foo'
from datas

But the correct solution is to not store the value in a way that you need to cast back and forth every time you access it.

This is because when you cast a json (or jsonb) to text all quotes are kept. And if you cast '"{\"foo\": \"bar\"}"' back to JSON it's still a single JSON string, not a key/value pair. See here
create table datas (id int, data jsonb);

insert into datas (id, data)
values
(1, '{"key1": "{\"foo\": \"bar\"}"}'),
(2, '{"key1": "some thing"}');

select (data->'key1')::text,
((data->'key1')::text)::json,
data->>'key1'
from datas;


text
json
?column?

"{\"foo\": \"bar\"}"
"{\"foo\": \"bar\"}"
{"foo": "bar"}

"some thing"
"some thing"
some thing

StackExchange Database Administrators Q#305041, answer score: 9