PostgreSQL遍历不规则嵌套jsonb数组按指定类型提取对应值

假设存储jsonb数据的表名为test，jsonb字段名为data，可通过以下两种方案实现需求：

方案1：兼容所有PostgreSQL版本（推荐低版本使用） #

通过数组展开+条件聚合实现，不依赖数组顺序，缺失类型自动返回NULL：

SELECT
  MAX(CASE WHEN comp->'types'->>0 = 'route' THEN comp->>'long_name' END) AS route,
  MAX(CASE WHEN comp->'types'->>0 = 'postal_code' THEN comp->>'long_name' END) AS postal_code,
  MAX(CASE WHEN comp->'types'->>0 = 'country' THEN comp->>'long_name' END) AS country
FROM test,
     jsonb_array_elements(data->'result'->'address_components') AS comp
GROUP BY test.id;

如果需要缺失类型返回空字符串而非NULL，给每个字段套上COALESCE(字段, '')即可。

方案2：PostgreSQL 12+ 版本（更简洁） #

使用jsonb路径查询直接定位匹配项，无需展开分组：

SELECT
  COALESCE(jsonb_path_query_first(data, '$.result.address_components[*] ? (@.types[0] == "route").long_name') #>> '{}', '') AS route,
  COALESCE(jsonb_path_query_first(data, '$.result.address_components[*] ? (@.types[0] == "postal_code").long_name') #>> '{}', '') AS postal_code,
  COALESCE(jsonb_path_query_first(data, '$.result.address_components[*] ? (@.types[0] == "country").long_name') #>> '{}', '') AS country
FROM test;

说明 #

两种方案都不依赖address_components数组的元素顺序，仅匹配每个元素types数组第一个值符合要求的项，对应类型不存在时返回空值。

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