PostgreSQL 11中如何从JSONB字段的数组中获取指定ID的元素地址值

你当前的查询确实能正确筛选出包含id=20的points元素的行，但它返回的是整个JSONB字段，要拿到目标address值，我们需要先把JSONB数组展开为单独的行，再筛选出目标元素，最后提取字段。

方法一：使用jsonb_array_elements展开数组 #

这是最常用的方式，适合需要对数组元素做进一步处理的场景：

SELECT (point->>'address') AS address
FROM test_json,
     jsonb_array_elements(data->'points') AS point
WHERE point->>'id' = '20';

让我拆解下这个语句的作用：

• jsonb_array_elements(data->'points') AS point：把每一行中data字段里的points数组拆分成独立的JSONB对象，每个对象对应结果集中的一行，我们给这个拆分出的对象起个别名point。
• WHERE point->>'id' = '20'：筛选出id等于20的对象（这里用->>操作符是把JSON值提取为文本类型，所以要和字符串'20'比较；如果用->提取JSON原始值，就得写成point->'id' = '20'::jsonb）。
• (point->>'address') AS address：从筛选后的对象中提取address字段的文本值，作为最终结果列。

执行这个查询后，就能得到你预期的结果：

address
--------
Test 2
Test 222

方法二：使用JSON路径查询（PostgreSQL 12+） #

如果你使用的是PostgreSQL 12及以上版本，可以用更简洁的JSON路径表达式来实现：

SELECT jsonb_path_query(data, '$.points[*] ? (@.id == 20).address') AS address
FROM test_json;

这个语句的逻辑是：

• $.points[*]：遍历data字段下的所有points数组元素。
• ? (@.id == 20)：筛选出id等于20的元素。
• .address：提取这些元素的address字段值。

两种方法都能实现你的需求，你可以根据实际场景选择：如果需要对数组元素做更多复杂处理，优先选第一种；如果只是简单提取字段，第二种更简洁。

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