SQL Join Table as JSON data

Question

I am trying to join reviews and likes onto products, but it seems, for some reason that the output of "reviews" column is duplicated by the length of another foreign table, likes, the output length of "reviews" is

amount of likes * amount of reviews

I have no idea why this is happening

My desired output is that the "reviews" column contains an array of JSON data such that one array is equal to one row of a related review

Products

Title        Image
----------------------
Photo        photo.jpg
Book         book.jpg
Table        table.jpg

Users

Username
--------
Admin
John
Jane

Product Likes

product_id    user_id
---------------------
1             1
1             2
2             1
2             3

Product Reviews

product_id    user_id    review
-------------------------------------
1             1          Great Product!
1             2          Looks Great
2             1          Could be better

This is the query

SELECT "products".*, 
array_to_json(array_agg("product_review".*)) as reviews,
EXISTS(SELECT * FROM product_like lk
JOIN users u ON u.id = "lk"."user_id" WHERE u.id = 4
AND "lk"."product_id" = products.id) AS liked,
COUNT("product_like"."product_id") AS totalLikes from "products"
LEFT JOIN "product_review" on "product_review"."product_id" = "products"."id" 
LEFT JOIN "product_like" on "product_like"."product_id" = "products"."id" 
group by "products"."id"

Query to create schema and insert data

CREATE TABLE products
    (id SERIAL, title varchar(50), image varchar(50), PRIMARY KEY(id))
;

CREATE TABLE users
    (id SERIAL, username varchar(50), PRIMARY KEY(id))
;

INSERT INTO products
    (title,image)
VALUES
    ('Photo', 'photo.jpg'),    
    ('Book', 'book.jpg'),
    ('Table', 'table.jpg')
;

INSERT INTO users
    (username)
VALUES
    ('Admin'),    
    ('John'),
    ('Jane')
;






CREATE TABLE product_review
    (id SERIAL, product_id int NOT NULL, user_id int NOT NULL, review varchar(50), PRIMARY KEY(id), FOREIGN KEY (product_id) references products, FOREIGN KEY (user_id) references users)
;

INSERT INTO product_review
    (product_id, user_id, review)
VALUES
    (1, 1, 'Great Product!'),
    (1, 2, 'Looks Great'),
    (2, 1, 'Could be better')
;



CREATE TABLE product_like
    (id SERIAL, product_id int NOT NULL, user_id int NOT NULL, PRIMARY KEY(id), FOREIGN KEY (product_id) references products, FOREIGN KEY (user_id) references users)
;

INSERT INTO product_like
    (product_id, user_id)
VALUES
    (1, 1),
    (1, 2),
    (2, 1),
    (2, 3)

fiddle with the schema and query: http://sqlfiddle.com/#!15/dff2c/1

Thanks in advance

You don't clearly say what the desired output is in terms of the input or give an example value. minimal reproducible example PS A common aggregation problem is that people want a join of some tables including some aggregation(s) (which in turn might involve other joins) but they wrongly try to do all the joining first & then all the aggregating. (Typically those wrong joins are not on the same key so they produce spurious pairings of rows whose duplicated subrows produce spurious aggregations.) Related: Can you give correct queries for tables relevant to your final result? — philipxy
– philipxy, Commented Feb 24, 2019 at 3:46
My bad, I added a desired output, that problem you are describing sounds exactly like mine, there is an answer now, thanks for your help — Mannaroth
– Mannaroth, Commented Feb 24, 2019 at 4:04

Nick · Accepted Answer · 2019-02-24 03:55:22Z

The reason you are getting multiple results is because of the one-to-many relationships between product_id and product_review and product_like causing duplication of rows prior to aggregation. To work around that, you need to perform the aggregation of those tables in subqueries and join the derived tables instead:

SELECT "products".*, 
"pr"."reviews",
EXISTS(SELECT * FROM product_like lk
JOIN users u ON u.id = "lk"."user_id" WHERE u.id = 4
AND "lk"."product_id" = products.id) AS liked,
COALESCE("pl"."totalLikes", 0) AS totalLikes
FROM "products"
LEFT JOIN (SELECT product_id, array_to_json(array_agg("product_review".*)) AS reviews
           FROM "product_review"
           GROUP BY product_id) "pr" on "pr"."product_id" = "products"."id" 
LEFT JOIN (SELECT product_id, COUNT(*) AS "totalLikes"
           FROM "product_like"
           GROUP BY product_id) "pl" on "pl"."product_id" = "products"."id"

Output:

id  title   image       reviews                                                                                                                     liked   totallikes
1   Photo   photo.jpg   [{"id":1,"product_id":1,"user_id":1,"review":"Great Product!"},{"id":2,"product_id":1,"user_id":2,"review":"Looks Great"}]  f       2
2   Book    book.jpg    [{"id":3,"product_id":2,"user_id":1,"review":"Could be better"}]                                                            f       2
3   Table   table.jpg                                                                                                                               f       0

Demo on dbfiddle

Collectives™ on Stack Overflow

SQL Join Table as JSON data

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related