Sample Header Ad - 728x90

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