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



  • 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?



  • As the name suggests, https://www.postgresql.org/docs/current/functions-json.html#FUNCTIONS-JSON-PROCESSING-TABLE 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 https://www.postgresql.org/docs/current/functions-json.html#FUNCTIONS-JSON-PROCESSING-TABLE 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 https://www.postgresql.org/docs/current/functions-json.html#FUNCTIONS-JSON-PROCESSING-TABLE

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

    The raw power of https://www.postgresql.org/docs/current/functions-json.html#FUNCTIONS-SQLJSON-PATH , added in Postgres 12. A bit confusing at first, but powerful. Can even use an index. See:

    • https://dba.stackexchange.com/questions/196604/find-rows-containing-a-key-in-a-jsonb-array-of-records/196635#196635

    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 https://dbfiddle.uk/?rdbms=postgres_14&fiddle=ce3934a79ca1c62cf9a980000ca0c231




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2