jsonb_array_elements() fails with "ERROR: cannot extract elements from an object"

Question

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:

{
 "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:

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'))[0].long_name

But I need my db to handle it. What's going wrong?

Answer 1

As the name suggests, jsonb_array_elements() expects a JSON array to unnest. But, according to your error message, at least one row contains a jsonb value in address with a JSON object at the top level. (Anything but an array triggers an error.)
Test for the type with jsonb_typeof() and exclude violating rows:

SELECT DISTINCT x.address ->> 'long_name' AS country_name
FROM (
 SELECT jsonb_array_elements(b.address) AS address
 FROM brand b
 WHERE jsonb_typeof(b.address) = 'array' -- !!!
 ) x
WHERE x.address ->> 'types' ILIKE ANY (ARRAY['%country%'::text]);

Shorter equivalent:

SELECT DISTINCT x.adr->>'long_name' AS country_name
FROM brand b, jsonb_array_elements(b.address) x(adr)
WHERE jsonb_typeof(b.address) = 'array'
AND (x.adr->>'types') ~* 'country';

Much shorter equivalent with jsonb_path_query()

SELECT DISTINCT jsonb_path_query(address, '$[*] ? (@.types[*] == "country").long_name')
FROM brand;

The raw power of SQL/JSON, added in Postgres 12. A bit confusing at first, but powerful. Can even use an index. See:

• Find rows containing a key in a JSONB array of records

And I believe you really wanted to test that types array for an exact match on "country" (like your JS code suggests), stricter than your SQL query.

The culprit(s)?

You may want to have a closer look at violating rows, if you didn't expect that error ...

SELECT * FROM brand
WHERE jsonb_typeof(address) IS DISTINCT FROM 'array';

null values are fine. The rest isn't.

db<>fiddle here

• bob_cobb: Well that definitely was the issue! Thank you for the help, Erwin. :) Thank you for explaining the problematic part. makes perfect sense now. Your IS DISTINCT FROM 'array' tip also helped me realize that the offending rows are just empty objects. I will make them null instead.
• Erwin Brandstetter: @bob_cobb: You already accepted, but I think you'll be interested in the bonus track. :)
• bob_cobb: Wow that's incredible haha. So glad I decided to migrate to Postgres from MySQL for this project. cheers!

Answer 2

It's pretty trivial in PostgreSQL as well :)

select distinct x.addresses->>'long_name' as country_name
from (
 select jsonb_array_elements(t.addresses->'address') as addresses
 from foo t
) x
where x.addresses->>'long_name' ilike any (array['%British Columbia%'])

You may see the full code and run it yourself here https://www.db-fiddle.com/f/agzPjwiyci5ZS8esWnRH8n/3

Also, you're using ::text conversion for no reason, the ->> operator returns a text data type. Same with scalar values manually mentioned in a SQL script like your '%country%'::text, by default it's already text.

• bob_cobb: Thank you @Chessbrain. I took your example and updated it since in my db there are a couple of differences. Namely, the address doesn't have an address key, and it's an array of objects like this db-fiddle.com/f/agzPjwiyci5ZS8esWnRH8n/5. When I run this against my production DB, I'm getting the following error: cannot extract elements from an object. Any idea what is wrong?
• Chessbrain: From what I understand based on your example, this should work db-fiddle.com/f/agzPjwiyci5ZS8esWnRH8n/6 @bob_cobb
• Chessbrain: If t his is still throwing errors, then you're not giving us the full picture :) Since it's obviously working on the examples given.
• bob_cobb: Heh.. I actually am a bit embarrassed myself here trying to figure out what's wrong. imgur.com/wE5ZAto is the table description and an example of an address entry, so it's pretty much what I described.