Why are multiple joins resulting in duplicated data-CodePudding

We have four tables: Product, SpecialOffers, SpecialOfferProducts and ProductPurchases. Fidddle is here and here is my question.

Products:

Id	Name	Price
1	Product A	10
2	Product B	20
3	Product C	30
4	Product D	40
5	Product E	50

SpecialOffers:

Id	Name
1	Offer 1
2	Offer 2
3	Offer 3

SpecialOfferProducts (products included in offers):

Id	SpecialOfferId	ProductId
1	1	1
2	1	2
3	1	3
4	2	1
5	2	2
6	3	4

ProductsPurchases (number of purchases of each product, linked to offer):

Id	SpecialOfferId	ProductId	Quantity
1	1	1	1
2	1	1	1
3	1	1	1
4	2	2	1
5	2	2	1
6	3	3	1

So, we have 4 products, three special offers. Product can be offered in zero, one or more special offers. In this case, Offer 1 contains products A, B, C, Offer 2 contains products A and B and Offer 3 has only product D. Product E is not in any offer. Also, sales are tracked in table ProductPurchases. In fiddle, I've tried to write a query to return a list of products containing both offer and purchase data in JSON format as columns. Problem is, result (incorrectly) outputs that product A is in six offers and purchases, product B in four offers and purchases and the rest of products are ok - products A and B's offers and purchases are doubled. Query is like this:

SELECT P.*, JSON_AGG(JSON_BUILD_OBJECT('offer.id', SO.ID, 'offer.name', SO.NAME)) AS OFFERS, JSON_AGG(JSON_BUILD_OBJECT('purchase.id', PP.ID, 'purchase.specialOfferId', PP.SPECIAL_OFFER, 'purchase.specialOfferName', SO.NAME)) AS PURCHASES
FROM PRODUCTS P
LEFT JOIN SPECIAL_OFFER_PRODUCTS SOP ON SOP.PRODUCT_ID = P.ID
LEFT JOIN SPECIAL_OFFERS SO ON SO.ID = SOP.SPECIAL_OFFER_ID
LEFT JOIN PRODUCT_PURCHASE PP ON PP.PRODUCT_ID = P.ID
GROUP BY P.ID

What I want is list of products with all product columns plus two columns containing JSON with purchases of that product (so A has 3, B has 2, C has one and rest have zero) and special offers they are included in (same idea as purchases).

Apparently, the number of offers and purchases is double the number of purchases - if we add additional purchase so there is four of them (instead of three as in example), number of reported offers and purchases will be 8. Why is this happening?

CodePudding user response：

You can use a DISTINCT but only with jsonB_build_object

select p.*, 
    json_agg(DISTINCT jsonB_build_object(
    'offer.id', so.id,
    'offer.name', so.name)) as offers,
    json_agg(DISTINCT jsonB_build_object(
    'purchase.id', pp.id,
    'purchase.specialOfferId', pp.special_offer,
    'purchase.specialOfferName', so.name)) as purchases
    from products p
left join special_offer_products sop on sop.product_id=p.id
left join special_offers so on so.id=sop.special_offer_id
left join product_purchase pp on pp.product_id=p.id
GROUP BY 
p.id
--sop.product_id 
--pp.product_id

Sample fiddle

CodePudding user response：

That is happening because of your left joins. Try this one:

select p.*, so.offers, sop.PURCHASES
from products p,
     lateral (select JSON_AGG(JSON_BUILD_OBJECT('offer.id', SO.ID, 'offer.name', SO.NAME))
              from specialoffers so
                       inner join specialofferproducts sop on sop.specialofferid = so.id
              where sop.productid = p.id) as so(offers),
     lateral (select JSON_AGG(JSON_BUILD_OBJECT('purchase.id', PP.ID, 'purchase.specialOfferId', PP.SpecialOfferId,
                                                'purchase.specialOfferName', SO.NAME) order by pp.id)
              from ProductsPurchases PP
                       inner join specialoffers so on pp.specialofferid = so.id
              where PP.ProductId = P.id ) AS sop(PURCHASES);

DBFiddle demo

EDIT: Result:

id	name	price	offers	purchases
1	Product A	10	[{"offer.id" : 1, "offer.name" : "Offer 1"}, {"offer.id" : 2, "offer.name" : "Offer 2"}]	[{"purchase.id" : 1, "purchase.specialOfferId" : 1, "purchase.specialOfferName" : "Offer 1"}, {"purchase.id" : 2, "purchase.specialOfferId" : 1, "purchase.specialOfferName" : "Offer 1"}, {"purchase.id" : 3, "purchase.specialOfferId" : 1, "purchase.specialOfferName" : "Offer 1"}]
2	Product B	20	[{"offer.id" : 1, "offer.name" : "Offer 1"}, {"offer.id" : 2, "offer.name" : "Offer 2"}]	[{"purchase.id" : 4, "purchase.specialOfferId" : 2, "purchase.specialOfferName" : "Offer 2"}, {"purchase.id" : 5, "purchase.specialOfferId" : 2, "purchase.specialOfferName" : "Offer 2"}]
3	Product C	30	[{"offer.id" : 1, "offer.name" : "Offer 1"}]	[{"purchase.id" : 6, "purchase.specialOfferId" : 3, "purchase.specialOfferName" : "Offer 3"}]
4	Product D	40	[{"offer.id" : 3, "offer.name" : "Offer 3"}]	null
5	Product E	50	null	null