sql - 从 Postgres 查询格式化嵌套的 JSON 对象

Question

我想得到一个格式类似于这样的 JSON 对象:

{
  "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
       }
   }]
}

目前我的所有格式都正确，除了评论。这是我正在使用的 SQL 查询:

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

我无法获取每个日志的评论。现在，它正在返回每条评论并重复日志(它们没有关联)。

此外，当其他所有内容都为空时(比如过去一周没有日志)，我如何让这个查询返回 username 和 teamname？

Answer 1

如果没有 SQLfiddle，我们不知道您的数据(和结构)是什么，因此很难回答您的问题。对于 NULL 情况——请像这样修改 WHERE 子句(故意不使用 COALESCE)

WHERE t.team_id = 5 AND (l.logdate IS NULL OR l.logdate > NOW()::date - INTERVAL '7 day')