json - 使用 row_to_json 的 Postgres 嵌套 JSON 数组

Question

我正在尝试使用 2 个表创建嵌套的 json 数组。

我有 2 个表 journal 和 journaldetail。

架构是 -

日志:journalid，总量

journaldetail : journaldetailid、journalidfk、账户、金额

journal 和 journaldetail 之间的关系是一对多的。

我想要以下格式的输出:

{  journalid : 1,
totalamount : 1000,
journaldetails : [ 
   {
      journaldetailid : j1,
      account : "abc",
      amount : 500 
   },
   {
      journaldetailid : j2,
      account : "def",
      amount : 500 
   }
]}

但是，通过按照此 post 编写此查询查询是:

select j.*, row_to_json(jd) as journal from journal j
inner join (
  select * from journaldetail
) jd on jd.sjournalidfk = j.sjournalid

输出是这样的:

{  journalid : 1,
totalamount : 1000,
journaldetails : 
   {
      journaldetailid : j1,
      account : "abc",
      amount : 500 
   }
}
{  journalid : 1,
totalamount : 1000,
journaldetails : 
   {
      journaldetailid : j2,
      account : "def",
      amount : 500 
   }
}

我希望子表数据作为父级中的嵌套数组。

Answer 1

我从 here 找到了答案:

这是查询:

select row_to_json(t)
from (
  select sjournalid,
    (
      select array_to_json(array_agg(row_to_json(jd)))
      from (
        select sjournaldetailid, saccountidfk
        from btjournaldetail
        where j.sjournalid = sjournalidfk        
      ) jd
    ) as journaldetail
  from btjournal j
) as t

这以数组格式提供输出。