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: QUERY: I'm also trying to do the same thing but as a json QUERY: I know why but I don't know how to avoid it, can someone help me to get something like this:

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]

Answer

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;

Advertisement

Answer