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]
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;