I want to get a JSON object formatted similar to this:
{
"username": "USERNAME",
"teamname": "TEAMNAME",
"logs": [
{
"log": {
"log_id": 29,
"person_id": 3,
"activity_id": 3,
"shoe_id": null,
"logdate": "2016-11-29",
"distance": null,
"activitytime": null,
"sleep": null,
"heartrate": null,
"logtitle": null,
"description": null
},
"activity": "Swim",
"comments": {
"comment_id": 1,
"description": "This is a comment",
"person_id": 1,
"log_id": 29
}
}]
}
Currently I have everything formatted correctly except the comments. Here is the SQL query I am using:
SELECT p.username, t.teamname, json_agg(json_build_object('log', l.*, 'comments', c.*, 'activity', a.activity)) as logs
FROM person_tbl p
INNER JOIN log_tbl l ON p.person_id = l.person_id
INNER JOIN activity_tbl a ON l.activity_id = a.activity_id
INNER JOIN comment_tbl c ON c.log_id = l.log_id
INNER JOIN person_team_tbl pt ON p.person_id = pt.person_id
INNER JOIN team_tbl t on t.team_id = pt.team_id
WHERE t.team_id = 5
AND l.logdate > NOW()::date - 7
GROUP BY p.username, t.teamname
ORDER BY p.username
I'm having trouble getting the comments of each log. Right now, it is returning every comment and repeating the logs (they are not associated).
Also, how could I get this query to return the username
and teamname
when everything else is null (like when there are no logs in the past week)?
NULL
useCOALESCE(first_not_null,first_not_null)
regarding the rest, it would be nice to have code build somewhere in sandbox, like sqlfiddle – Vao Tsun Dec 1 at 8:28