jsonb_array_elements() fails with "ERROR: cannot extract elements from an object"
14
votes
2
answers
27101
views
Using: Postgres 14.2.
Objective: To get a list of all distinct countries in my table.
The column
address
is a JSONB
column type and contains an array structured like:
~~~pgsql
{
"address":[
{
"types":["route"],
"long_name":"20203 113B Ave",
"short_name":"20203 113B Ave"
},
{
"types":["locality","political"],
"long_name":"Maple Ridge",
"short_name":"Maple Ridge"
},
{
"types":["administrative_area_level_3","political"],
"long_name":"Maple Ridge",
"short_name":"Maple Ridge"
},
{
"types":["administrative_area_level_2","political"],
"long_name":"Greater Vancouver",
"short_name":"Greater Vancouver"
},
{
"types":["administrative_area_level_1","political"],
"long_name":"British Columbia",
"short_name":"BC"
},
{
"types":["country","political"],
"long_name":"Canada",
"short_name":"CA"
},
{
"types":["postal_code"],
"long_name":"V2X 0Z1",
"short_name":"V2X 0Z1"
}
]
}
~~~
How can I filter this array of objects such that it only returns the value of "long_name"
(e.g. Canada
) for the array index if types
contains "country"
?
Something like this is what I'm trying but, obviously, I want to return only the country and not the entire brand.
SELECT * from brand
where address::text ilike ANY (ARRAY['%country%'::text]);
This query fails with:
> ~~~none
> ERROR: cannot extract elements from an object
> ~~~
SELECT * from brand
where exists (
select from jsonb_array_elements(address) e
where (e ->> 'types')::text = 'country'
);
Obviously, this is trivial to do in JS:
address.filter((part) => part.types.includes('country')).long_name
But I need my db to handle it. What's going wrong?
Asked by bob_cobb
(243 rep)
Apr 26, 2022, 08:49 PM
Last activity: Aug 16, 2022, 10:38 PM
Last activity: Aug 16, 2022, 10:38 PM