sql - 如何在 Postgres 中将 3 个表组合在一起？

Question

假设我们有 3 个表:产品、组织、广告

product.sql

CREATE TABLE product (
    name             VARCHAR   NOT NULL,
    id               BIGSERIAL NOT NULL PRIMARY KEY,
    advert_id        BIGINT    NOT NULL REFERENCES advert(id),
    organization_id  BIGINT    NOT NULL REFERENCES organization(id)
);

组织.sql

CREATE TABLE organization (
    name VARCHAR NOT NULL,
    id   BIGSERIAL NOT NULL PRIMARY KEY,
);

advert.sql

CREATE TABLE advert (
    title  VARCHAR,
    id     BIGSERIAL NOT NULL PRIMARY KEY,
);

最后我想要这样的JSON结构

{
    id: "ADVERT_ID",
    title: "ADVERT_TITLE",
    products: [
        { 
            id: "PRODUCT_ID",
            advert_id: "ADVERT_ID",
            organization: { 
                id: "ORGANIZATION_ID", 
                name: "ORGANIZATION_NAME" 
            }, 
            product: { 
                id: "PRODUCT_ID", 
                name: "PRODUCT_NAME"
            },
       },
       { 
            id: "PRODUCT_ID", 
            advert_id: "ADVERT_ID",
            organization: { 
                id: "ORGANIZATION_ID", 
                name: "ORGANIZATION_NAME" 
            }, 
            product: { 
                id: "PRODUCT_ID", 
                name: "PRODUCT_NAME"
            }
       }
    ]
}

假设当优惠数组为空时，我不想获得这样的 JSON 数组:

[null]

非常感谢!

编辑

在做了更多研究并尝试在 Sequelize 中制作原型(prototype)后，我实现了这一目标:

{
  id: 1,
  name: 'Buy from Hot Fries Inc. today!',
  createdAt: '2021-07-07T18:17:39.723Z',
  updatedAt: '2021-07-07T18:17:39.723Z',
  Products: [
    {
      id: 1,
      name: 'French Fries',
      org_id: 1,
      adv_id: 1,
      createdAt: '2021-07-07T18:17:39.726Z',
      updatedAt: '2021-07-07T18:17:39.726Z',
      Organization: {
        id: 1,
        name: 'Hot Fries Inc.',
        createdAt: '2021-07-07T18:17:39.713Z',
        updatedAt: '2021-07-07T18:17:39.713Z'
      }
    }
  ]
}

我选择了 Sequelize 来快速查看正在 secret 进行的查询的日志，这是我发现的:

 SELECT "Advert".*,
       "products"."id"                      AS "Products.id",
       "products"."name"                    AS "Products.name",
       "products"."org_id"                  AS "Products.org_id",
       "products"."adv_id"                  AS "Products.adv_id",
       "products"."createdat"               AS "Products.createdAt",
       "products"."updatedat"               AS "Products.updatedAt",
       "Products->Organization"."id"        AS "Products.Organization.id",
       "Products->Organization"."name"      AS "Products.Organization.name",
       "Products->Organization"."createdat" AS "Products.Organization.createdAt"
       ,
       "Products->Organization"."updatedat" AS
       "Products.Organization.updatedAt"
FROM   (SELECT "Advert"."id",
               "Advert"."name",
               "Advert"."createdat",
               "Advert"."updatedat"
        FROM   "adverts" AS "Advert"
        LIMIT  1) AS "Advert"
       LEFT OUTER JOIN "products" AS "Products"
                    ON "Advert"."id" = "products"."adv_id"
       LEFT OUTER JOIN "organizations" AS "Products->Organization"
                    ON "products"."org_id" = "Products->Organization"."id";  

这就是 Sequelize 连接子表的方式，如果它是这样调用的话。

Answer 1

也许 jsonb_build_object 和一些子查询的组合就是您正在寻找的:

SELECT 
  jsonb_build_object(
    'id',a.id,
    'title',a.title,
    'products',(SELECT json_agg(
                  jsonb_build_object(
                    'id',p.id,
                    'advert_id',a.id,
                    'organization',jsonb_build_object(
                                      'id',o.id,
                                      'name',o.name),
                     'product',json_build_object(
                                 'id',p.id,
                                 'name',p.name)                   
                    ))
                FROM product p
                JOIN organization o ON o.id = p.organization_id
                WHERE p.advert_id = a.id)
  )
FROM advert a;