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

All we need is an easy explanation of the problem, so here it is.

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?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 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:

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

Method 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.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply