How to parse serialized json in Postgresql?

Question

I have a data like below and I would like to get a value of sub_key1:

'{"key_1":"val_1", "key_2":"{\"sub_key1\":\"sub_val1\", \"sub_key2\":\"sub_val2\"}"}'

If I run below query, it works fine and gets me the value of key_2.

SELECT ('{"key_1":"val_1", "key_2":"{\"sub_key1\":\"sub_val1\", \"sub_key2\":\"sub_val2\"}"}')::json->'key_2';

But if I run below query, I do not get anything in return.

SELECT (('{"key_1":"val_1", "key_2":"{\"sub_key1\":\"sub_val1\", \"sub_key2\":\"sub_val2\"}"}')::json->'key_2')::json->'sub_key1';

How to get a value of sub_key1?

Answer 1

The value you get out by using -> is a JSON string literal. Casting that to json will do nothing, and accessing a property on a string doesn't work.

You'll need to use ->> instead to get the string as a postgres text which you then can convert to a json object:

SELECT (('{"key_1":"val_1", "key_2":"{\"sub_key1\":\"sub_val1\", \"sub_key2\":\"sub_val2\"}"}')::json->>'key_2')::json->'sub_key1';

But either way, fix the system that generates this JSON not to put serialised JSON strings into JSON.