时间戳

首页 > 解决方案 > 使用 Postgres 将 2 个表中的数据作为数组输出

postgresql - 使用 Postgres 将 2 个表中的数据作为数组输出

问题描述

我在 Postgres 数据库中有 2 个表，如果可能的话，需要一些帮助才能将查询放在一起以特定格式输出数据。

表 1（账户）

RowID    Name    AccountNo
1        John    45356333
2        Helen   74562445

表 2（代码）

RowID     AccountNo    Code         Status
1         45356333     csdcwew      Active
2         45356333     gererge      Active
2         74562445     thwfewf      Active

我希望能够获取数据，以便最终获得如下所示的 JSON 对象：

{
  "Accounts": [
    {
      "AccountNo": "45356333",
      "Codes": [
        {
          "Code": "csdcwew",
          "Status": "Active"
        },
        {
          "Code": "gererge",
          "Status": "Active"
        }
      ]
    },
    {
      "AccountNo": "74562445",
      "Codes": [
        {
          "Code": "thwfewf",
          "Status": "Active"
        }
      ]
    }
  ]
}

我尝试了一个查询，该查询使用 array_agg 函数输出所有帐户和代码数组，但这是我所能得到的。真的，我也需要状态和描述。

 SELECT a."Name",
    array_agg(c."Codes")
   FROM ("Accounts" a
     LEFT JOIN "Codes" c ON ((c."AccountNo" = a."AccountNo")))
  GROUP BY a."Name";

标签： postgresql

解决方案

你很接近。您可以继续按照聚合代码的方式聚合状态值。然后你会有两列，你可以按顺序迭代。

另一种选择是 Postgres 有一个非常合理的 JSON 语法，它允许您直接聚合到 JSON。

SELECT json_agg(json_build_object(
    'AccountNo', accountNo,
    'Codes', val ))
FROM (
    select accountNo,
        json_agg (json_build_object(
            'code', code, 
            'status', status))  as val 
        from codes
        group by accountNo) as c;

推荐阅读