How can I group 3 tables together in Postgres?

Viewed 69

Let's assume that we have 3 tables: product, organization, advert

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)
);

organization.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,
);

In the end I want to have JSON structure like this

{
    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"
            }
       }
    ]
}

And let's say when the offers array is empty, I don't want to get a JSON array like this:

[null]

Thank you very much!

EDIT

After doing some more research and trying to make a prototype in Sequelize, I was able to achieve this:

{
  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'
      }
    }
  ]
}

I have chosen Sequelize to quickly see the logs of the queries that were going on undercover and here's what I discovered:

 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";  

This is the way that Sequelize joined sub-tables, if it's called that way.

1 Answers

Perhaps a combination of jsonb_build_object and some subqueries is what you're looking for:

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;
Related