时间戳

首页 > 解决方案 > 在 PostgreSQL 中生成具有多个连接且不重复的 JSON

sql - 在 PostgreSQL 中生成具有多个连接且不重复的 JSON

问题描述

我有一个简单的产品商店架构

    CREATE TABLE optgroups (
      id SERIAL PRIMARY KEY,
      title VARCHAR(22) UNIQUE NOT NULL CHECK (title <> '')
    );
    CREATE TABLE options (
      id SERIAL PRIMARY KEY,
      title VARCHAR(22) UNIQUE NOT NULL CHECK (title <> ''),
      optgroup_id INTEGER NOT NULL REFERENCES optgroups(id)
    );

    CREATE TABLE products (
      id SERIAL PRIMARY KEY,
      title VARCHAR(125) UNIQUE NOT NULL CHECK (title <> '')  
    );

    CREATE TABLE products_opts (
      id SERIAL PRIMARY KEY,
      price REAL NOT NULL CHECK (price >= 0),
      product_id INTEGER NOT NULL REFERENCES products(id),
      option_id INTEGER NOT NULL REFERENCES options(id),
      UNIQUE (product_id, option_id)
    );

insert into optgroups (title) values ('Size'), ('Color');
insert into options (title, optgroup_id) values ('XS', 1), ('S', 1), ('Red', 2);
insert into products (title) values ('Product 1'), ('Product 2');
insert into products_opts(price, product_id, option_id) values (10, 1, 1), (34, 1, 2);

并尝试生成这样的 JSON 数组：

[
   {
      "product_id": 1,
      "optgroups": [
         {
           "title": "",
           "options": [
             "title": "",
             "price": 0
           ]
         }
      ]
   }
]

使用多个 with-queries 和 json_agg's 但接收重复项。谷歌搜索没有给出任何可比较的想法，而它应该是一个非常简单的查询。有什么想法吗？

标签： sqlarraysjsonpostgresql

解决方案

首先获取 json 文档所需的所有列：

with prod_data as (
  select p.id as product_id,
         og.title as ogtitle,
         o.title as otitle,
         po.price as oprice
    from products p
    join products_opts po 
      on po.product_id = p.id
    join options o 
      on o.id = po.option_id
    join optgroups og
      on og.id = o.optgroup_id
),

options使用jsonb_build_object()and聚合个体jsonb_agg：

 aggopts as (
  select product_id, ogtitle, 
         jsonb_agg(
           jsonb_build_object(
             'title', otitle, 'price', oprice
           )
         ) as options
    from prod_data
   group by product_id, ogtitle
)

再次聚合optgroups：

 aggogrps as (
  select product_id, 
         jsonb_agg(
           jsonb_build_object(
             'title', ogtitle, 'options', options
           )
         ) as optgroups
    from aggopts
   group by product_id
)

主查询聚合所有 json 行以返回最终数组：

select jsonb_agg(
         to_jsonb(aggogrps) order by product_id) 
  from aggogrps;

在这里工作小提琴。

推荐阅读