PostgreSQL中如何用jsonb_path_query获取jsonb文本值及更优方案

获取jsonb_path_query返回的文本类型值 #

jsonb_path_query默认返回jsonb类型的结果，无需嵌套子查询，可以直接通过以下两种方式得到文本类型值：

• 对返回的标量jsonb值使用#>> '{}'运算符，该运算符可直接将顶层jsonb标量值转换为对应文本：

-- 示例：获取text类型的requestId
jsonb_path_query(request :: jsonb, '$.requestId') #>> '{}'

• 在jsonpath路径中添加.string()方法，在查询阶段就将结果转为字符串类型的jsonb值，再转文本即可：

jsonb_path_query(request :: jsonb, '$.requestId.string()') #>> '{}'

如果确认路径只会返回1个匹配结果，也可以用jsonb_path_query_first函数直接取第一个匹配项，避免返回多行结果：

jsonb_path_query_first(request :: jsonb, '$.requestId') #>> '{}'

原多字段查询的优化方案 #

你当前的嵌套子查询写法可以直接简化，不需要额外嵌套层级：

select 
  jsonb_path_query(request :: jsonb, '$.journeys[*].costs.distance.value') #>> '{}' as test1,
  jsonb_path_query(request :: jsonb, '$.journeys[*].costs.distance.unit') #>> '{}' as test2
from json_import ri;

如果要进一步提升性能，同时避免两个jsonb_path_query调用返回元素数量不匹配导致的笛卡尔积问题，可改成单次路径查询后提取字段，效率更高也更稳定：

select 
  (cost_item -> 'distance' ->> 'value') as test1,
  (cost_item -> 'distance' ->> 'unit') as test2
from json_import ri,
-- 单次遍历获取所有journeys下的costs节点，拆分为多行
jsonb_path_query(ri.request :: jsonb, '$.journeys[*].costs') as t(cost_item);

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