PostgreSQL如何编写按深层嵌套JSONB字段排序的查询语句

你可以通过LATERAL横向连接配合jsonb_array_elements逐层展开嵌套的JSON数组，定位到目标字段后进行排序，完整查询语句如下：

SELECT a.*
FROM availability a
-- 逐层展开嵌套JSON数组
, LATERAL jsonb_array_elements(a.bookableitems) AS b(bitem)
, LATERAL jsonb_array_elements(b.bitem -> 'seasons') AS s(season)
, LATERAL jsonb_array_elements(s.season -> 'pricingRecords') AS pr(record)
, LATERAL jsonb_array_elements(pr.record -> 'pricingDetails') AS pd(detail)
WHERE 
    a.currency = 'USD'
    -- 保留原筛选条件，可利用JSONB GIN索引提升性能
    AND a.bookableitems @> '[{"productOptionCode": "TG11"}]'
    AND b.bitem ->> 'productOptionCode' = 'TG11'
    AND pd.detail ->> 'ageBand' = 'CHILD'
ORDER BY 
    -- 将JSON数值转为数字类型后倒序排序
    (pd.detail -> 'price' -> 'original' -> 'recommendedRetailPrice')::numeric DESC
-- 若单条原记录存在多个符合条件的儿童价格，只需返回每条原记录一次，可开启下面的去重逻辑（假设productcode为唯一主键）
-- DISTINCT ON (a.productcode)

关键逻辑说明 #

• jsonb_array_elements函数用于将JSONB类型的数组展开为多行数据，配合LATERAL连接可以基于上一级展开的结果继续访问下一层嵌套结构
• 保留bookableitems @> '[{"productOptionCode": "TG11"}]'条件的目的是如果你的表对bookableitems字段建了GIN索引，可以大幅提升筛选效率
• 如果一条原记录对应多个符合条件的儿童定价，你可以根据业务需求调整：
  • 保留所有匹配的定价行：直接使用上述语句即可
  • 每条原记录仅返回1次，按最高儿童定价排序：开启DISTINCT ON配置，同时保证ORDER BY中排序字段在前、主键字段在后
  • 需要按最低/平均儿童定价排序：可将多层展开逻辑放入子查询，先按主键分组聚合得到对应价格后再排序

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