Postgres 14

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
CREATE TABLE brand(id int, address jsonb);
INSERT INTO brand(id, address) VALUES(1, '
   [
      {
         "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"
      },
      {
         "types":["country","country","political"],
         "long_name":"Burkina Faso",
         "short_name":"BF"
      },
      {
         "types":["country","country","political"],
         "long_name":"Burkina Faso",
         "short_name":"BF"
      }
   ]
');

-- note the added duplicate country "Burkina Faso", with a duplicate "country" type on top!
-- to make the test more meaningful

CREATE TABLE



INSERT 0 1


-- original query works
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]);


country_name


Burkina Faso

Canada



SELECT 2


-- add violating row
INSERT INTO brand(id, address)
VALUES(2, '{}');  -- not a JSON array

INSERT 0 1


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

ERROR:  cannot extract elements from an object


-- original query fixed:
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]);


country_name


Burkina Faso

Canada



SELECT 2


-- equivalent, shorter query
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';


country_name


Burkina Faso

Canada



SELECT 2


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


jsonb_path_query


"Burkina Faso"

"Canada"



SELECT 2


-- Here's the culprit(s)
SELECT *
FROM   brand
WHERE  jsonb_typeof(address) IS DISTINCT FROM 'array';


id
address


2
{}



SELECT 1