sql - 返回连接作为 json 与 postgres 10

Question

我有以下结构

task -> hasMany links -> hasOne user

我希望看到返回的是

task.id | [
   { linkType: 'foo', userid:1, taskid:1, 
        user : { name: 'jon', lastname: 'smith'}, 
   ... ]

作为一个 sql 菜鸟，我已经做到了这一点

select task.id, json_agg(links.*) as links from task 
    left outer join links on task.id = links.taskid 
    join "user" on "user".id = links.userid 
    group by task.id ;

这给了我

task.id | [{ linkType: 'foo', userid:1, taskid:1}, ... ]

但显然缺少用户

我现在有点卡在如何将 user 属性添加到每个链接数组项上

我已经阅读了几个文档，但它们似乎总是在第一次加入时就停止了

模式设计是

create table task (id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY)
create table "user" (id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, username text)

create table links (taskid integer references task (id), userid integer references "user" (id) );

Answer 1

您的示例没有引用您提供的表结构，因为 "user" 有一个 name 和一个 lastname 列后者中只有一个 username 列，但您确实提供了足够的信息来给出答案。我正在使用两列用户，因为它允许展示如何构造嵌套的 JSON 对象。

SELECT task.id, 
       json_agg(
         json_build_object(
           'linkType', 'foo',
           'taskid', links.taskid, 
           'userid', links.userid, 
           'user', json_build_object('name', u.name, 'lastname', u.lastname)
         )
       ) AS links 
  FROM task 
  LEFT OUTER JOIN links ON task.id = links.taskid 
  JOIN "user" u ON u.id = links.userid 
 GROUP BY task.id;

在 this excellent DBA.SE answer 中讨论了使用适当的键值构建 json 对象的一般问题。 .我采用了那里列出的解决方案 3，因为我认为它是最灵活和最易读的。您的口味可能会有所不同。