Join the Stack Overflow Community
Stack Overflow is a community of 6.4 million programmers, just like you, helping each other.
Join them; it only takes a minute:
Sign up

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)?

share|improve this question
    
to fight NULL use COALESCE(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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.