I have a column which its type is character varying.
It contains a json array in it.
Something like below.
[
{"name":"Peter", "information":{"tel":"120391083", "address":"xxx"}},
{"name":"Jane", "information":{"tel":"12302131093", "address":"ooo"}},
{"name":"Pat", "information":{"tel":"123098", "address":"zzz"}}
]
How can I get the address value of json object which name is Pat?
I used -> and ->> operator but it showed operator does not exist.
I'm using pdadmin 4 and postgreSQL version is 13.2
CodePudding user response:
I am not sure what the output is exactly you are looking for.
If you want to get the full information
part, you can use this:
select jsonb_path_query_first(the_column::jsonb, '$[*] ? (@.name == "Pat").information')
from the_table;
If you only what the value of the address
key, you can use:
select jsonb_path_query_first(the_column::jsonb, '$[*] ? (@.name == "Pat").information') ->> 'address'
from the_table;
In any case you should change the column's data type to jsonb
in the long run. Don't (mis)use varchar
for that.