如何从PostgreSQL的jsonb列提取动态键对应的库存值？

要动态提取jsonb列中所有仓库的库存并聚合成目标格式，你可以利用PostgreSQL的jsonb_each和jsonb_object_agg函数组合实现：

基础查询（返回jsonb格式的库存对象） #

SELECT 
  jsonb_object_agg(warehouse_name, (warehouse_data->>'stock')::integer) AS warehouse_stocks
FROM 
  "general".inventory,
  jsonb_each(warehouses) AS wh(warehouse_name, warehouse_data)
GROUP BY 
  id; -- 替换为你的表主键或唯一标识列，确保每行数据独立聚合

生成指定字符串格式的结果 #

如果需要输出warehouse: {"Warehouse1": 100, ...}这种字符串格式，可调整为：

SELECT 
  'warehouse: ' || jsonb_object_agg(warehouse_name, (warehouse_data->>'stock')::integer)::text AS result
FROM 
  "general".inventory,
  jsonb_each(warehouses) AS wh(warehouse_name, warehouse_data)
GROUP BY 
  id;

关键函数说明 #

• jsonb_each(warehouses)：将warehouses列的jsonb对象拆解为多行数据，每行包含仓库名称（warehouse_name）和对应的仓库详情对象（warehouse_data）。
• (warehouse_data->>'stock')::integer：从仓库详情对象中提取stock字段的文本值，并转换为整数类型（适配库存的数值属性）。
• jsonb_object_agg(...)：将拆解后的仓库名称和库存值重新聚合为一个jsonb对象，实现动态收集所有仓库的库存数据。

注意事项 #

• 必须替换id为你的表中唯一标识每行数据的字段（如主键），避免不同行的仓库数据被错误合并。
• 如果stock字段本身是jsonb数值类型，也可以直接用warehouse_data->'stock'代替类型转换语句，聚合后会保留原生数值类型。

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