Postgres 13

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
CREATE TABLE purchase (
    id serial PRIMARY KEY
);

CREATE TABLE items (
    id serial PRIMARY KEY,
    TYPE int
);

CREATE TABLE purchase_items (
    id serial PRIMARY KEY,
    purchase_id int,
    item_id int,
    price decimal,
    sold int
);

CREATE TABLE purchase_discounts (
    id serial PRIMARY KEY,
    purchase_id int,
    discount_amount int
);

INSERT INTO items (id, TYPE) VALUES
  (1, 5)
, (2, 5);
INSERT INTO purchase (id) VALUES (200);
INSERT INTO purchase_discounts (id, purchase_id, discount_amount) VALUES 
  (1, 200, 10)
, (2, 200, 2);
INSERT INTO purchase_items (id, purchase_id, item_id, price, sold) VALUES
  (1234, 200, 1, 10, 1);

-- added example: purchase without discouts
INSERT INTO purchase (id) VALUES (201);
-- no discounts!

CREATE TABLE



CREATE TABLE



CREATE TABLE



CREATE TABLE



INSERT 0 2



INSERT 0 1



INSERT 0 2



INSERT 0 1



INSERT 0 1



INSERT 0 2


-- your query, fixed & optimized
SELECT json_agg(items)
FROM  (
   SELECT pi.item_id
        , array_agg(pi.id) AS purchase_items_ids
        , sum(pi.sold) AS total_sold
        ,(SELECT COALESCE(sum(pd.discount_amount), 0)
          FROM   purchase_discounts pd
          WHERE  pd.purchase_id = 200) AS discount_amount
   FROM   purchase_items pi
   WHERE  pi.purchase_id = 200
   GROUP  BY 1
   ) AS items;


json_agg


[{"item_id":1,"purchase_items_ids":[1234],"total_sold":1,"discount_amount":12}]



SELECT 1


-- the same for example without discounts
SELECT json_agg(items)
FROM  (
   SELECT pi.item_id
        , array_agg(pi.id) AS purchase_items_ids
        , sum(pi.sold) AS total_sold
        ,(SELECT COALESCE(sum(pd.discount_amount), 0)
          FROM   purchase_discounts pd
          WHERE  pd.purchase_id = 201) AS discount_amount
   FROM   purchase_items pi
   WHERE  pi.purchase_id = 201
   GROUP  BY 1
   ) AS items;


json_agg


[{"item_id":1,"purchase_items_ids":[1235],"total_sold":1,"discount_amount":0}, 
 {"item_id":2,"purchase_items_ids":[1236],"total_sold":2,"discount_amount":0}]



SELECT 1


-- result format that makes more sense
SELECT json_build_object(
         'items'
       , json_agg(items)
       , 'discount_amount'
       , (SELECT COALESCE(sum(pd.discount_amount), 0)
          FROM   purchase_discounts pd
          WHERE  pd.purchase_id = 200)
       )
FROM  (
   SELECT pi.item_id
        , array_agg(pi.id) AS purchase_items_ids
        , sum(pi.sold) AS total_sold
   FROM   purchase_items pi
   WHERE  pi.purchase_id = 200
   GROUP  BY 1
   ) AS items;


json_build_object


{"items" : [{"item_id":1,"purchase_items_ids":[1234],"total_sold":1}], "discount_amount" : 12}



SELECT 1


-- the same for example without discounts
SELECT json_build_object(
         'items'
       , json_agg(items)
       , 'discount_amount'
       , (SELECT COALESCE(sum(pd.discount_amount), 0)
          FROM   purchase_discounts pd
          WHERE  pd.purchase_id = 201)
       )
FROM  (
   SELECT pi.item_id
        , array_agg(pi.id) AS purchase_items_ids
        , sum(pi.sold) AS total_sold
   FROM   purchase_items pi
   WHERE  pi.purchase_id = 201
   GROUP  BY 1
   ) AS items;


json_build_object


{"items" : [{"item_id":1,"purchase_items_ids":[1235],"total_sold":1}, 
 {"item_id":2,"purchase_items_ids":[1236],"total_sold":2}], "discount_amount" : 0}



SELECT 1