Postgres JSONB动态路径查询：提取各产品分类值

假设你的表名为your_table，包含jsonb类型字段products，可以通过以下SQL语句提取每个产品的category值并生成你需要的格式：

生成每行字符串格式的结果 #

SELECT 
  format('"%s": %s', 
    key, 
    CASE 
      WHEN value->>'category' IS NOT NULL THEN format('"%s"', value->>'category')
      ELSE 'NULL'
    END
  ) AS product_category
FROM your_table,
     jsonb_each(products -> 'product');

语句说明： #

• jsonb_each(products -> 'product')：将products字段内的product对象拆分为键值对，key对应产品ID，value对应产品的详情对象。
• value->>'category'：从产品详情对象中提取category字段的值，若该字段不存在则返回NULL。
• format函数：按照你需要的格式拼接字符串，对存在的category值添加双引号，不存在的则显示NULL。

生成JSON格式的结果（可选） #

如果你需要将结果聚合为一个JSON对象而非每行字符串，可以使用jsonb_object_agg：

SELECT jsonb_object_agg(product_id, category) AS product_categories
FROM (
  SELECT 
    jsonb_object_keys(products->'product') AS product_id,
    (products->'product'->product_id)->>'category' AS category
  FROM your_table
) AS sub;

这个查询会返回一个JSON对象，结构与你期望的结果一致，适合直接用于程序处理。

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