jsonb_array_elements() 失敗並顯示“錯誤：無法從對像中提取元素”

使用：Postgres 14.2。

目標：在我的表中獲取所有不同國家的列表。

該列address是一種JSONB列類型，包含一個結構如下的數組：

{
  "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"
     }
  ]
}

如何過濾此對像數組，使其僅返回數組索引的"long_name"（例如）值（如果包含） ？Canada``types``"country"

我正在嘗試這樣的事情，但顯然，我只想返回這個國家而不是整個品牌。

SELECT * from brand
where address::text ilike ANY (ARRAY['%country%'::text]);

此查詢失敗：

ERROR:  cannot extract elements from an object

SELECT * from brand
where exists (
  select from jsonb_array_elements(address) e
  where (e ->> 'types')::text = 'country'
  );

顯然，這在 JS 中是微不足道的：

address.filter((part) => part.types.includes('country'))[0].long_name

但我需要我的數據庫來處理它。怎麼了？

顧名思義，jsonb_array_elements()期望 JSON數組不嵌套。但是，根據您的錯誤消息，至少有一行在頂層包含一個帶有 JSON對象jsonb的值。（除數組之外的任何內容都會觸發錯誤。） 測試包含和排除違規行的類型：address
jsonb_typeof()

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]);

較短的等價物：

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';

更短的等效於jsonb_path_query()

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

SQL/JSON的原始功能，在 Postgres 12 中添加。起初有點令人困惑，但功能強大。甚至可以使用索引。看：

• 在 JSONB 記錄數組中查找包含鍵的行

而且我相信您真的想測試該types數組是否與“國家”完全匹配（就像您的 JS 程式碼所建議的那樣），比您的 SQL 查詢更嚴格。

罪魁禍首？

如果您沒有預料到該錯誤，您可能需要仔細查看違規行…

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

null價值觀很好。其餘的不是。

db<>在這裡擺弄

引用自：https://dba.stackexchange.com/questions/311369