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

Question

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

Answer 1

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