'How to get json value from json array column in postgresql

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

sqljsonpostgresql


Solution 1:[1]

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.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 a_horse_with_no_name

Related Questions