如何对jsonb嵌套字段多父键下的price字段提取并排序

需求说明 #

我有一个jsonb类型的data字段，结构如下：

{
    "prices": {
        "[parent key]": {
            "price": 20
        }
    }
}

其中[parent key]有5种可能取值，需要实现：

• 提取每个条目的price值，若单条数据包含多个price父键，取其中最大值
• 最终结果按price降序排列

示例数据 #

create table my_table(
  id int generated by default as identity primary key,
  data jsonb);
insert into my_table select * from jsonb_populate_recordset(null::my_table,
'[{
  "id": 1,
  "data": {
    "prices": {
      "x": {"price": 20}
    }
  }
},
{
  "id": 2,
  "data": {
    "prices": {
      "y": {"price": 86}
    }
  }
},
{
  "id": 3,
  "data": {
    "prices": {
      "z": {"price": 21},
      "b": {"price": 41}
    }
  }
}]');

解决方案SQL #

基础查询（返回结构化数据） #

SELECT 
  id,
  MAX((value->>'price')::int) AS price
FROM my_table,
     jsonb_each(data->'prices')
GROUP BY id
ORDER BY price DESC;

返回JSON格式结果 #

如果需要直接返回JSON数组，使用以下SQL：

SELECT json_agg(row_to_json(t)) AS result
FROM (
  SELECT 
    id,
    MAX((value->>'price')::int) AS price
  FROM my_table,
       jsonb_each(data->'prices')
  GROUP BY id
  ORDER BY price DESC
) t;

期望结果 #

[
  {
    "id": 2,
    "price": 86
  },
  {
    "id": 3,
    "price": 41
  },
  {
    "id": 1,
    "price": 20
  }
]

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