我的SQL请求有问题,看起来像这个
SELECT
json_build_object('id', u.id,
'personnes_morale', json_build_object('denomination', pm.denomination),
'personne_physique', json_build_object('nom_patronyme', pp.nom_patronyme) ,
'representants', json_agg(json_build_object('name', r.name)),
'observations', json_agg(json_build_object('id', o.id))
)
FROM
dossiers_entreprises u
LEFT JOIN personnes_morales pm ON pm.numero_gestion = u.numero_gestion
LEFT JOIN personnes_physiques pp ON pp.numero_gestion = u.numero_gestion
LEFT JOIN representants r ON r.numero_gestion = u.numero_gestion
LEFT JOIN observations o ON o.numero_gestion = u.numero_gestion
WHERE
u."numero_gestion" = '1955B08131'
GROUP BY
u.id,pp.id,pm.id
LIMIT 1;
它应该给我这样的东西:
{
id:'123',
personne_morale:{denomination: 'test'},
personne_physique:{nom_patronyme: 'Smith'},
representants:[{name:'John'},{name:'Smith'}],
observations:[{id:'1'},{id:'2'}]
}
但因为我有两个json_agg(我只用一个fro representant进行了测试,效果很好(,它给我返回了类似于的东西
{
id:'123',
... <- *personne_morale*
... <- *personne_physique*
representants:[{name:'John'},{name:'John'},{name:'Smith'},{name:'Smith'}]
observations:[{id:'1'},{id:'1'}{id:'2'},{id:'2'}]
}
它重复了我的"代表"one_answers"观察"结果,我不明白为什么
你有什么解决方案吗
提前感谢
这似乎是意料之中的事,您只需通过执行JOIN
来获得笛卡尔乘积。
如果您希望这些数组独立填充,我建议使用子查询:
SELECT
json_build_object('id', u.id,
'personnes_morale', json_build_object('denomination', pm.denomination),
'personne_physique', json_build_object('nom_patronyme', pp.nom_patronyme) ,
'representants', (
SELECT json_agg(json_build_object('name', r.name))
FROM representants r
WHERE r.numero_gestion = u.numero_gestion
),
'observations', (
SELECT json_agg(json_build_object('id', o.id))
FROM observations o
WHERE o.numero_gestion = u.numero_gestion
)
)
FROM
dossiers_entreprises u
LEFT JOIN personnes_morales pm ON pm.numero_gestion = u.numero_gestion
LEFT JOIN personnes_physiques pp ON pp.numero_gestion = u.numero_gestion
WHERE
u."numero_gestion" = '1955B08131'
LIMIT 1;
(顺便说一句,为了避免下一个意外:您可能想在子查询中使用COALESCE(…, '[]')
,因为如果找不到行,它们会返回NULL
而不是空数组(