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?
CodePudding user response:
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.