Prevent duplicate rows when using LEFT JOIN in Postgres without DISTINCT

Using correlated subquery instead of LEFT JOIN:

SELECT array_to_json(array_agg(p_values)) FROM 
( 
  SELECT t.item_id, t.purchase_items_ids, t.total_sold, t.discount_amount FROM 
    ( 
      SELECT purchase_items.item_id AS item_id,
             ARRAY_AGG(purchase_items.id) AS purchase_items_ids,
             SUM(purchase_items.sold) as total_sold,
             SUM((SELECT SUM(pd.discount_amount) FROM purchase_discounts pd
                  WHERE pd.purchase_id = purchase.id)) as discount_amount
       FROM items
       INNER JOIN purchase_items ON purchase_items.item_id = items.id
       INNER JOIN purchase ON purchase.id = purchase_items.purchase_id
       WHERE purchase.id = 200
       GROUP by purchase_items.item_id
    ) as t 
  INNER JOIN items i ON i.id = t.item_id 
) AS p_values;

db<>fiddle demo

Output:

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

First I would suggest to remove INNER JOIN items i ON i.id = t.item_id from the query which no reason to be there.

Then instead Left joining Purchase_Discounts table use subquery to get the Discount_amount (as mentioned in Lukasz Szozda's answer)

If there is no discount for any product then Discount_amount column will display NULL. If you want to avoid it then you can use COALESCE() as below instead:

COALESCE(SUM((select sum(discount_amount) from purchase_discounts 
                 where purchase_discounts.purchase_id = purchase.id)),0) as discount_amount

Db-Fiddle:

  SELECT array_to_json(array_agg(p_values)) FROM 
     ( 
       SELECT t.item_id, t.purchase_items_ids, t.total_sold, t.discount_amount FROM 
         ( 
           SELECT purchase_items.item_id AS item_id,
                  ARRAY_AGG(purchase_items.id) AS purchase_items_ids,
                  SUM(purchase_items.sold) as total_sold,
                  SUM((select sum(discount_amount) from purchase_discounts 
                      where purchase_discounts.purchase_id = purchase.id)) as discount_amount
                   FROM items
                   INNER JOIN purchase_items ON purchase_items.item_id = items.id
                   INNER JOIN purchase ON purchase.id = purchase_items.purchase_id              
                  WHERE 
                   purchase.id = 200
                  GROUP by 
                   purchase_items.item_id
         ) as t 
       
     ) AS p_values;

Output:

db<>fiddle here

The core problem is that your LEFT JOIN multiplies rows. See:

• Two SQL LEFT JOINS produce incorrect result

Aggregate discounts to a single row before the join. Or use a (uncorrelated) subquery expression:

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;

Result:

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

db<>fiddle here

I added a couple of additional improvements:

• Assuming referential integrity enforced by FK constraints, we don't need to involve the tables purchase and items at all.

• Removed a subquery level doing nothing.

• Using json_agg() instead of array_to_json(array_agg()).

• Added COALESCE() to output 0 instead or NULL for no discounts.

Since discounts apply to the purchase in your model, not to individual items, it doesn't make sense to output discount_amount for every single item. Consider this query instead to return an array of items and a single, separate discount_amount:

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;

Result:

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

db<>fiddle here

Using json_build_object() to assemble the JSON object.

Your example with a single item in the purchase isn't too revealing. I added a purchase with multiple items and no discount to my fiddle.

If you can have multiple values only in the purchase_discounts table then a subquery that aggregate multiple purchase_discounts rows into one before the join can solve the problem:

SELECT array_to_json(array_agg(p_values)) FROM 
( 
  SELECT t.item_id, t.purchase_items_ids, t.total_sold, t.discount_amount FROM 
    ( 
      SELECT purchase_items.item_id AS item_id,
             ARRAY_AGG(purchase_items.id) AS purchase_items_ids,
             SUM(purchase_items.sold) as total_sold,
             X.discount_amount
             FROM items
              INNER JOIN purchase_items ON purchase_items.item_id = items.id
              INNER JOIN purchase ON purchase.id = purchase_items.purchase_id
              LEFT JOIN (SELECT purchase_id, sum(purchase_discounts.discount_amount) AS discount_amount FROM purchase_discounts GROUP BY purchase_id) X ON X.purchase_id = purchase.id
             WHERE 
              purchase.id = 200
             GROUP by 
              purchase_items.item_id,
              X.discount_amount
    ) as t 
  INNER JOIN items i ON i.id = t.item_id 
) AS p_values;

The LEFT JOIN is not causing your duplicates, I understand why you need it as there may not be any discounts, but for the data provided changing to an inner join produces the same result. You are getting duplicate entries because you use ARRAY_AGG(purchase_items.id). Further, with the data presented, the tables item and purchase are not necessary. You can use the window version of sum and distinct on to reduce the duplication of purchase_id, and eliminate the mentioned tables. Finally the middle select ... ) t can be completely removed. Resulting in: (see demo)

select array_to_json(array_agg(p_values)) 
  from (select distinct on (pi.item_id, pi.id)
                pi.item_id
              , pi.id purchase_items_ids
              , sum(pi.sold) over (partition by pi.item_id) total_sold         
              , sum(pd.discount_amount) over(partition by  pi.item_id)  discount_amount
           from purchase_items pi  
           left join purchase_discounts pd 
             on pd.purchase_id = pi.purchase_id 
          order by pi.item_id, pi.id           
       ) as p_values; 

I think the left join does not cause, because with the Inner Join query result same as the left join, in discount with purchase_id=200 query has 2 results you can use from row_number with the partion_by same as:

ROW_NUMBER() OVER(PARTITION BY purchase_items.id order by purchase_items.id) rn

then select rn=1. you change your query for the sum function, I think that you can use from partion_by.