Search code examples
sqlpostgresqlduplicatesinner-join

Duplicated inner join results


With postgres I'm trying to get a single user and all their posts, but when I inner join the Users and Posts tables I receive this:

-[ RECORD 1 ]------------------------------------------------------------------------------------------------------------------
PhotoPath   | 
Username    | username
Name        | user
Biography   | 
PicturePath | https://gourmetdemexico.com.mx/wp-content/uploads/2020/05/diferencia-entre-arte-y-cultura-taco-gringo-600x600.jpg
-[ RECORD 2 ]------------------------------------------------------------------------------------------------------------------
PhotoPath   | 
Username    | username
Name        | user
Biography   | 
PicturePath | http://www.travelettes.net/wp-content/uploads/2012/06/mystery2-600x600.jpg

QUERY:

SELECT 
    u."PhotoPath",
    u."Username",
    u."Name",
    u."Biography",
    p."PicturePath"
FROM "Users" as u 
INNER JOIN "Posts" as p ON u."ID" = p."UserID"
WHERE p."UserID" = 9;

I'm also trying to do the same thing but as a json

-[ RECORD 1 ]----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
user | [{"photo" : null, "username" : "username", "name" : "user", "bio" : null}, {"photo" : null, "username" : "username", "name" : "user", "bio" : null}]
pics | {https://gourmetdemexico.com.mx/wp-content/uploads/2020/05/diferencia-entre-arte-y-cultura-taco-gringo-600x600.jpg,http://www.travelettes.net/wp-content/uploads/2012/06/mystery2-600x600.jpg}

QUERY:

SELECT json_agg(json_build_object(
    'photo', u."PhotoPath",
    'username', u."Username",
    'name', u."Name",
    'bio', u."Biografy"
)) as user,
array_agg(p."PicturePath") as pics
FROM "Users" as u
INNER JOIN "Posts" as p
ON u."ID" = p."UserID"
WHERE u."ID" = 9
GROUP BY p."UserID";

I know why but I don't know how to avoid it, can someone help me to get something like this:

user | {"photo" : null, "username" : "username", "name" : "user", "bio" : null}, 
pics | [https://gourmetdemexico.com.mx/wp-content/uploads/2020/05/diferencia-entre-arte-y-cultura-taco-gringo-600x600.jpg,http://www.travelettes.net/wp-content/uploads/2012/06/mystery2-600x600.jpg]

Solution

  • By doing this I resolve the issue:

    WITH cte(j) AS (
        SELECT 
            jsonb_build_object(
                'photo', "PhotoPath",
                'username', "Username",
                'name', "Name",
                'bio', "Biography",
                'posts', array_agg(
                    "Posts"."PicturePath"
                )
            )
            FROM "Users"
            INNER JOIN "Posts" ON "Posts"."UserID" = "Users"."ID"
            WHERE "Users"."ID" = @
            GROUP BY "Users"."ID"
    )
    SELECT json_agg(j) as user
    FROM cte;