我有一个PostgreSQL映射表,它保存公司员工-团队领导和团队成员之间的关系。名为leaders的表如下所示:

leader_id | employee_id
    1     |      15
    1     |      21
    1     |      26
    2     |      76
    2     |      41

这两列都是person表的外键,该表包含namedob等信息。
目标是创建一个包含以下数据的JSON:
{
  1: {name: "John Doe",
      person_id: 1,
      employees: {
        15: {name: "Oliver Queen",
            person_id: 15
        },
        21: {name: "Barry Alan",
            person_id: 21
        },
        26: {name: "Solomon Rondon",
            person_id: 26
        },
      }
  },
  2: {name: "Papi Hans",
      person_id: 2,
      employees: {
        76: {name: "Ashley Young",
            person_id: 76
        },
        41: {name: "Amberly Smith",
            person_id: 41
        }
      }
  },
}

通过编写一个带有两个连接的查询,将person表中的数据分别连接到leader_idemployee_id表中,然后逐行迭代结果来创建JSON,这样做非常容易。
我的问题是,是否有一种方法来编写查询,以便它以准备好的JSON格式显示结果,这样我就不必逐行迭代来构建它了?如果有的话,效率会更高吗?

最佳答案

demo:db<>fiddle

SELECT
    json_object_agg(person_id,                                           -- 5
        -- 4
        json_build_object('name', name, 'person_id', person_id, 'employees', employees)
    )
FROM (
    SELECT
        l.leader_id as person_id,
        p1.name as name,
        json_object_agg(l.employee_id,                                   -- 3
            json_build_object('name', p2.name, 'person_id', p2.id)       -- 2
        ) as employees
    FROM
        leader l
    JOIN person p1 ON l.leader_id = p1.id                                -- 1
    JOIN person p2 ON l.employee_id = p2.id
    GROUP BY l.leader_id, p1.name
) s

加入person表以获取名称
为每个员工创建JSON对象
通过将这些json对象按其领导者分组为一个json对象来聚合这些json对象
使用employee为领导创建JSON对象
将所有leader对象聚合为一个JSON对象
在这种情况下(4)可以缩短为
row_to_json(s)

进一步阅读:Postgres JSONPostgres JSON aggregates

09-10 08:34
查看更多