CODEWITHSUNDEEP
sqlpostgresqljoin
Mannaroth
Mannaroth

Reputation: 480

SQL Join Table as JSON data

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

Upvotes: 2

Views: 9224

Answers (1)

Nick
Nick

Reputation: 147166

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

Upvotes: 4

Related Questions