title: Format nested JSON object from Postgres query
body: <p>I want to get a JSON object formatted similar to this: </p>
  
  <pre><code>{
    "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
         }
     }]
  }
  </code></pre>
  
  <p>Currently I have everything formatted correctly except the comments. Here is the SQL query I am using: </p>
  
  <pre><code>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 &gt; NOW()::date - 7 
          GROUP BY p.username, t.teamname 
          ORDER BY p.username
  </code></pre>
  
  <p>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). </p>
  
  <p>Also, how could I get this query to return the <code>username</code> and <code>teamname</code> when everything else is null (like when there are no logs in the past week)?</p>