2

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

4
  • '{"key1": "{\"foo\": \"bar\"}"}' is "wrong". I guess it should be: '{"key1": {"foo": "bar"}}' Dec 16, 2021 at 15:58
  • @a_horse_with_no_name it's not wrong, it's the way the data is modelled in our database. It's not a great model, but I'm stuck with it for now.
    – Segfault
    Dec 16, 2021 at 17:40
  • Well, in the value '{"key1": "{\"foo\": \"bar\"}"}' the value of key1 is a single string, not a JSON structure. That's why data->'key1'->'foo' doesn't work. As you seem to expect the value of key1 to be a nested JSON you have to get rid of the additional double quotes. '{"key1": {"foo": "bar"}}' -> 'key1' -> 'foo'` will work as expected. Your value '{"key1": "{\"foo\": \"bar\"}"}' is essentially the same structure as '{"key1": "some string with the words foo and bar"}' Dec 16, 2021 at 17:48
  • @a_horse_with_no_name yes, I realize this is a string and not jsonb. The question is how to deserialize the string (which does contain valid json within it). If the answer is "it can't be done" well, that's an answer at least.
    – Segfault
    Dec 16, 2021 at 18:21

1 Answer 1

7

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
2
  • Thank you! This is an existing database I've inherited, and we'll fix the schema when we can. Can you explain a little bit in your answer why the ->> operator works but the -> operator doesn't? This query that I thought would be equivalent doesn't work: select ((data->'key1')::text)::json->>'foo' from datas;?
    – Segfault
    Dec 17, 2021 at 0:03
  • 1
    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 Dec 17, 2021 at 6:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.