Postgres nested JSON array using row_to_json

问题:

I am trying to create nested json array using 2 tables.

I have 2 tables journal and journaldetail.

Schema is -

journal : journalid, totalamount

journaldetail : journaldetailid, journalidfk, account, amount

Relation between journal and journaldetail is one-to-many.

I want the output in following format :

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

However, by writing this query as per this post the query is:

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

and the output is like this :

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

I want the child table data as nested array in the parent.

回答1:

I found the answer from here:

Here is the query :

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

This gives output in array format.