PostgreSQL request json_agg duplicate results

Question

I have a problem with my SQL request, it looks like this

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;

and it should give me something like this :

{
id:'123',
personne_morale:{denomination: 'test'},
personne_physique:{nom_patronyme: 'Smith'},
representants:[{name:'John'},{name:'Smith'}],
observations:[{id:'1'},{id:'2'}]
}

but because of i have two json_agg (i've test with just one fro representant and it works good) it return me something like :

{
id:'123',
... <- *personne_morale*
... <- *personne_physique*
representants:[{name:'John'},{name:'John'},{name:'Smith'},{name:'Smith'}]
observations:[{id:'1'},{id:'1'}{id:'2'},{id:'2'}]
}

it duplicate my 'representants' and 'observations' results and i don't understand why

Do you have any solutions

Thanks in advance

Hello everyone* sorry it didn't has take my edits

dc-mpo
– dc-mpo

2021-04-21 14:43:36 +00:00
Commented Apr 21, 2021 at 14:43 — dc-mpo
– dc-mpo, Commented Apr 21, 2021 at 14:43
please provide sample data, also you can use dbfiddle.uk

eshirvana
– eshirvana

2021-04-22 03:06:35 +00:00
Commented Apr 22, 2021 at 3:06 — eshirvana
– eshirvana, Commented Apr 22, 2021 at 3:06

Bergi · Accepted Answer · 2021-04-22 03:24:43Z

This seems expected, you just get the cartesian product from doing a JOIN.

If you want these arrays to be filled independently, I would recomment to use subqueries instead:

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;

(Btw, to avoid the next surprise: you might want to use COALESCE(…, '[]') around the subqueries, as they return NULL not an empty array if no rows are found)

Collectives™ on Stack Overflow

PostgreSQL request json_agg duplicate results

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related