PostgreSQL用jsonb_array_elements提取JSON字段时缺失数组如何保留行

你当前的写法是通过隐式内连接多次展开address_components数组，同时在WHERE子句中强制匹配所有类型，只要缺少任意一个目标类型的数组项，整行就会被过滤，同时多次展开数组会产生笛卡尔积，执行效率也很低。

推荐改用标量子查询逐个提取目标字段，不需要多表连接，缺失类型的字段会自动返回NULL，天然保留所有原始行：

SELECT 
  full_response,
  (SELECT elem->>'long_name' 
   FROM jsonb_array_elements(full_response -> 'result' -> 'address_components') elem 
   WHERE elem -> 'types' ->> 0 = 'premise') as aug_premise,
  (SELECT elem->>'long_name' 
   FROM jsonb_array_elements(full_response -> 'result' -> 'address_components') elem 
   WHERE elem -> 'types' ->> 0 = 'street_number') as aug_street_number,
  (SELECT elem->>'long_name' 
   FROM jsonb_array_elements(full_response -> 'result' -> 'address_components') elem 
   WHERE elem -> 'types' ->> 0 = 'route') as aug_street_name,
  (SELECT elem->>'long_name' 
   FROM jsonb_array_elements(full_response -> 'result' -> 'address_components') elem 
   WHERE elem -> 'types' ->> 0 IN ('sublocality', 'sublocality_level_1')) as aug_sublocality,
  (SELECT elem->>'long_name' 
   FROM jsonb_array_elements(full_response -> 'result' -> 'address_components') elem 
   WHERE elem -> 'types' ->> 0 = 'locality') as aug_locality,
  (SELECT elem->>'long_name' 
   FROM jsonb_array_elements(full_response -> 'result' -> 'address_components') elem 
   WHERE elem -> 'types' ->> 0 = 'postal_town') as aug_town_city,
  (SELECT elem->>'long_name' 
   FROM jsonb_array_elements(full_response -> 'result' -> 'address_components') elem 
   WHERE elem -> 'types' ->> 0 = 'administrative_area_level_2') as aug_city_county,
  (SELECT elem->>'long_name' 
   FROM jsonb_array_elements(full_response -> 'result' -> 'address_components') elem 
   WHERE elem -> 'types' ->> 0 = 'administrative_area_level_1') as aug_region,
  (SELECT elem->>'long_name' 
   FROM jsonb_array_elements(full_response -> 'result' -> 'address_components') elem 
   WHERE elem -> 'types' ->> 0 = 'country') as aug_country,
  (SELECT elem->>'long_name' 
   FROM jsonb_array_elements(full_response -> 'result' -> 'address_components') elem 
   WHERE elem -> 'types' ->> 0 = 'postal_code') as aug_post_code
FROM augmentation.g_placesapi_responses;

额外说明 #

• 如果某个类型存在多个匹配的数组项，上述写法会返回第一个匹配的long_name，如果需要合并多个值，可以将子查询里的elem->>'long_name'替换为string_agg(elem->>'long_name', ',')。
• 该方案兼容所有支持jsonb_array_elements的PostgreSQL版本，不需要升级数据库。

内容的提问来源于stack exchange，提问作者ML_Engine