`How can I get SQL join query that the results in json format using postgres? I believe this can be done but I am struggling to figure it out.

Here is the SQL code to create tables and insert test data.

CREATE TABLE backend.product (
id          Integer     NOT NULL,
category        text        NOT NULL,
title           text        NOT NULL,
price           money       NOT NULL
);

CREATE TABLE backend.product_details (
id          Integer     NOT NULL,
type            text        NOT NULL,
description     text        NOT NULL
);

CREATE TABLE backend.shipping (
id          Integer     NOT NULL,
description     text        NOT NULL,
price           money       NOT NULL
);

INSERT INTO backend.product (id, category, title, price) VALUES (1, 'sweatshirts', 'hoodie', '$50.00');
INSERT INTO backend.product_details (id, type, description) VALUES (1, 'color', 'red');
INSERT INTO backend.product_details (id, type, description) VALUES (1, 'color', 'blue');
INSERT INTO backend.product_details (id, type, description) VALUES (1, 'color', 'green');
INSERT INTO backend.product_details (id, type, description) VALUES (1, 'size', 'small');
INSERT INTO backend.product_details (id, type, description) VALUES (1, 'size', 'large');
INSERT INTO backend.shipping (id, description, price) VALUES (1, 'standard box', '$17.05');

I ran this query:

SELECT p.id, p.category, p.title, p.price, s.description as shipping_box, s.price as shipping_cost, pd.type, pd.description AS choice
FROM backend.product p, backend.shipping s, backend.product_details pd
WHERE p.id = s.id
AND p.id = 1
AND p.id IN (
    SELECT pd.id
    FROM backend.product_details pd
    WHERE pd.id = 1
    GROUP BY pd.id, pd.type, pd.description
);

Results:

I want the results in json format like this:

[
    {
      "id": 1,
      "category": "sweatshirts",
      "title": "hoodie",
      "price": "$50.00",
      "shipping": { 
          "box": "standard box",
          "cost": "$17.05"
        },
      "choices": [ {
          "color": [ "red", "blue", "green" ],
          "size": [ "small", "large" ]
        }  ]
    }
]

So the results will really be one row. Thank you for any help.`

CodePudding user response：

I suggest using json_build_object

for example

 SELECT json_build_object('id',p.id,'category',p.category,'title', p.title,'price' p.price)
    FROM backend.product p, backend.shipping s, backend.product_details pd
    WHERE p.id = s.id
    AND p.id = 1
    AND p.id IN (
        SELECT pd.id
        FROM backend.product_details pd
        WHERE pd.id = 1
        GROUP BY pd.id, pd.type, pd.description
    );

and also you can do nested objects, by nesting json_build_objects inside.

CodePudding user response：

Here is the demo with the details.

I started with the deepest json layers - "shipping" and "choices" and finally have built the whole json (note: added the jsonb_pretty() for the last select, remove it if necessary):

--build json object for "shipping" field
with shipping_data as (
  select id, json_build_object('box', description, 'cost', price) as json
  from shipping
),
--build json object for "choices" field
choices_data as (
  --build json aggregated by product 'id' column
  with details_data as (
    --build json with "type" and "description" fields - grouped by product 'id' and 'type' columns
    select id, json_build_object(type, json_agg(description)) as type_json
    from product_details
    group by id, type)
  select id, json_agg(type_json) as json
  from details_data
  group by id
)
--build final json with all fields
select product.id, 
       jsonb_pretty(json_build_object('id', product.id, 'category', category, 'title', title, 'price', price,
                         'shipping', shipping_data.json,
                         'choices', choices_data.json)::jsonb
                   ) as product_json
from product
join shipping_data on shipping_data.id = product.id
join choices_data on choices_data.id = product.id;