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

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.