PostgreSQL中如何查询JSONB对象数组内m2值最高的对象

实现方案 #

假设你所使用的表名为 building_records，可通过以下SQL查询得到期望结果：

SELECT DISTINCT ON (t.id)
  t.id,
  elem AS data
FROM 
  building_records t
LEFT JOIN LATERAL 
  jsonb_array_elements(t.data) elem ON true
ORDER BY 
  t.id, 
  (elem ->> 'm2')::numeric DESC NULLS LAST;

逻辑说明

• LEFT JOIN LATERAL jsonb_array_elements(t.data) elem ON true 会将每行的dataJSONB数组拆分为单个JSON对象行，使用左连接可以保证原表中data为空数组的行不会被过滤，对应返回的elem值为null，符合预期输出
• 排序逻辑中按id分组，每组内将elem的m2属性转为数值后倒序排列，NULLS LAST保证空值排在最后，避免空数组或者无m2属性的对象干扰排序
• DISTINCT ON (t.id) 会取每个id分组下排序后的第一行，也就是m2值最高的对象

如果存在多个对象m2值相同且同为最大值的场景，需要返回所有符合条件的对象，可调整为窗口函数的写法：

WITH ranked_elems AS (
  SELECT
    t.id,
    elem,
    RANK() OVER (PARTITION BY t.id ORDER BY (elem ->> 'm2')::numeric DESC NULLS LAST) AS rnk
  FROM building_records t
  LEFT JOIN LATERAL jsonb_array_elements(t.data) elem ON true
)
SELECT 
  id,
  elem AS data
FROM ranked_elems
WHERE rnk = 1;

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