Postgres如何嵌套多个json_agg子查询

Question

我目前有以下postgres查询。使用一个聚合子查询。我不是一个经验丰富的开发人员，如果我没有很好地解释这一点，我很抱歉。

select row_to_json(x)
from (
select  i.intel_id,
i.created_date,
i.title,
(select json_agg(adv) from (select c.company_name, 
cr.company_role_name,
(select json_agg(ppl) from (select  p.person_first_name||' '||p.person_last_name AS person,
    iprt.person_role_name,
    p.person_linkedin)ppl )as people

from intel.intel_company_role icr 
join intel.intel_person_company_role ipcr on ipcr.company_id = icr.company_id
join entities.companies c on c.company_id = ipcr.company_id
join intel.company_roles cr on cr.company_role_id = icr.company_role_id
join entities.person p on p.person_id = ipcr.person_id
join intel.intel_person_role_type iprt on iprt.person_role_id = ipcr.person_role_id
where intel_id = i.intel_id  and icr.company_role_id in(1,2))adv)
as advisors
from intel.intel i
)x;

它产生以下输出

{
"intel_id": 1,
"created_date": "2021-02-14T01:16:11.403117",
"title": "This is an article headline  CompanyA expands brands in acquisition",
"advisors": [
{
"company_name": "CompanyName 1",
"company_role_name": "Legal Advisor",
"people": [
{
"person": "Brad Blunder",
"person_role_name": "Partner",
"person_linkedin": "https://linkedin.com/brad_blunder"
}
]
},
{
"company_name": "CompanyName 1",
"company_role_name": "Legal Advisor",
"people": [
{
"person": "Jane Summers",
"person_role_name": "Partner",
"person_linkedin": "https://linkedin.com/jane_summers"
}
]
},
{
"company_name": "CompanyName 2",
"company_role_name": "Financial Advisor",
"people": [
{
"person": "Greg Gander",
"person_role_name": "Leader",
"person_linkedin": "https://linkedin.com/greg_gander"
}
]
},
{
"company_name": "CompanyName 2",
"company_role_name": "Financial Advisor",
"people": [
{
"person": "Helen Hodges",
"person_role_name": "Leader",
"person_linkedin": "https://linkedin.com/helen_hodges"
}
]
}
]
}

我试图嵌套第二个聚合子查询，使其产生以下json输出其中每个人都被聚合到一个单独的公司。

{
"id": 1,
"date": "2021-02-14T01:16:11.403117",
"title": "This is an article headline  CompanyA expands brands in acquisition",
"author": "Joe Bloggs",
"text": "This is the full html article text here",
"advisors": [
{
"company_name": "CompanyName 1",
"company_role": "legal",
"side": "to the target",
"people": [
{
"name": "Brad Blunder",
"person_role": "partner",
"linkedin": "https://linkedin.com/brad_blunder"
},
{
"name": "Jane Summers",
"person_role": "partner",
"linkedin": "https://linkedin.com/jane_summers"            
}
]
},
{
"company_name": "CompanyName 2",
"company_role": "accountancy",
"side": "to the seller",
"people": [
{
"name": "Greg Gander",
"person_role": "partner",
"linkedin": "https://linkedin.com/greg_gander"
},
{
"name": "Helen Hodges",
"person_role": "partner",
"linkedin": "https://linkedin.com/helen_hodges"            
}
]
}

]
}

感谢任何可以帮助新手做正确事情的人，非常感谢

Answer 1

可能有一种更干净的方式来写这个，如果有人想改进它，请这样做，因为我渴望学习

select row_to_json(outerq)--::jsonb
from (
select  i.intel_id,
i.created_date,
i.title,
a.first_name||' '||a.last_name as author,
(select json_agg(subqadv) from (select  
c1.company_name, 
cr.company_role_name,
tst.transaction_side_type_name,
(select json_agg(subqp) from (select            
                              p.person_first_name||' '||p.person_last_name as person,
                              iprt.person_role_name,
                              p.person_linkedin
                              from entities.person p
                              join intel.intel_person_company_role ipcr on ipcr.person_id = p.person_id
                              join intel.intel_person_role_type iprt on iprt.person_role_id = ipcr.person_role_id
                              where ipcr.company_id = c1.company_id                                           
)subqp) as people
from intel.intel_company_role icr1 
join intel.transaction_side_types tst on tst.transaction_side_type_id = icr1.transaction_side_type_id
join entities.companies c1 on c1.company_id = icr1.company_id
join intel.company_roles cr on cr.company_role_id = icr1.company_role_id
where  icr1.company_role_id in(1,2))
subqadv) as advisors,

(select json_agg(subqfin) from (select 
f.reported_year,
f.turnover,
f.gross_profit,
f.num_employees
from intel.intel_financials f)
subqfin ) as financials
--------------------
from intel.intel i
join intel.authors a on a.author_id = i.author_id
)outerq;