时间戳

首页 > 解决方案 > Postgres 子查询 - 如何从子查询创建对象格式的别名

sql - Postgres 子查询 - 如何从子查询创建对象格式的别名

问题描述

我正在尝试将几个查询包装成 1 个复杂查询。

这是场景。给定一个user_id'xxxxx-xx-xxxxx'，我必须在几个表中查询几个数据点。我将下面的大多数查询都包含了一些 JOIN，但真的很想看看是否有可能获得最后一个查询，我将用户名和电子邮件包装到ALIAS存储为对象的列中？我不确定这是否可能，但这里是分解的查询..

漫长的道路

查询一：

SELECT * FROM requests WHERE created_by = 'xxxx-xx-xxxxx';

此查询将返回如下一行：

id   |   created_by   |   created_date ...
1141   xxxx-xx-xxxxx    2019-04-09 19:33:40.889+00 ...

然后我必须运行另一个查询来查找返回的 id 查询 2 的所有批准者：

SELECT * FROM approvers WHERE request_id = 1141

这可能会返回许多行：

request_id   |   user_id      |   decision    |    type   | .....
1141            xxxxx-xx-xxxxx       approve         approver     ......
1141            aaaaa-aa-aaaaa      approve         approver     ......
1141            bbbbb-bb-bbbbb    under review      watcher     ......
1141            ccccc-cc-ccccc      reject          approver     .......

最后要找到用户名，我必须运行查询（对于查询 2 的每个返回行） 查询 3、4、5、6：

SELECT * FROM users WHERE id = 'xxxx-xx-xxxxx' LIMIT 1

将返回：

id           |     given_name     |     family_name     |     email     | .....
xxxx-xx-xxxxx      John                  Doe             johndoe@email.com    ....

是否可以运行子查询并创建一个ALIAS列，approver_info其中关联的批准者和用户存储为 JSON 对象？

所需查询/结果：

1个查询：

SELECT * FROM requests
WHERE created_by = 'xxxxx-xx-xxxxx'
SUBQUERY HERE ...

这将产生以下结果：

id   |  created_by    |  created_date               |  approver_info    ...
1141    xxxx-xx-xxxxx    2019-04-09 19:33:40.889+00    [{"id" : "xxxxx-xx-xxxxx", "given_name" : "John", "family_name" : "Doe", "email":"john@email.com","decision" : "approve", "type" : "approver"},
                                                        {"id" : "aaaaa-aa-aaaaa", "given_name" : "Sansa", "family_name" : "Stark", "email":"sansa@.com","decision" : "approve", "type" : "approver"},
                                                        {"id" : "bbbbb-bb-bbbbb", "given_name" : "Arya", "family_name" : "Stark", "email":"arya@email.com","decision" : "under review", "type" : "watcher"},
                                                        {"id" : "ccccc-cc-ccccc", "given_name" : "Ned", "family_name" : "Stark", "email":"ned@email.com","decision" : "approve", "type" : "approver"}]

标签： sqljsonpostgresqljoin

解决方案

您的查询可能如下所示：

SELECT r.id, r.created_by, r.created_date
    , (SELECT json_agg(au)
       FROM  (
          SELECT u.id, u.given_name, u.family_name, u.email, a.decision, a.type -- , ...
          FROM   approvers a
          JOIN   users     u ON u.id = a.user_id
          WHERE  a.request_id = r.id
          ) au
      ) AS approver_info
FROM   requests  r
WHERE  r.created_by = 'xxxx-xx-xxxxx';

使用相关子查询将 0-n 相关行聚合到 JSON 数组approver_info中。

连接LATERAL将是等效的替代方案：

嵌套子查询是使用原始列名作为键构建 JSON 数组的一种方法。还有其他（带json_build_object()或row_to_json()）：

推荐阅读