WITH data AS (
SELECT 10312 AS id_obs, 'tetrax tetrax' as espece, 'mathieu' as observateur, '{"mâles": 12, "femelles": 3, "oeufs": 7}'::jsonb AS effectif
UNION
SELECT 15021 AS id_obs, 'zerynthia rumina' as espece, 'paul' as observateur, '{"mâles": 3, "immatures": 3, "larves": 7}'::jsonb AS effectif
UNION
SELECT 15897 AS id_obs, 'tetrax tetrax' as espece, 'paul' as observateur, '{"mâles": 4, "immatures": 15}'::jsonb AS effectif
)
/* les données qui contiennent des obs de mâles */
--SELECT id_obs, observateur, effectif->'larves' AS nb_larves, effectif->'mâles' AS nb_males from data --WHERE effectif ? 'larves'
/* les effectifs des différents objets observés (mâles, femelle, immature, oeufs) en colonne */
--SELECT id_obs, observateur, effectif, (effectif->>'larves')::integer AS nb_larves, (effectif->>'mâles')::integer AS nb_males, (effectif->>'femelles')::integer AS nb_femelles, (effectif->>'oeufs')::integer AS nb_oeufs from data --WHERE effectif ? 'larves'
/* des agrégations */
SELECT count(id_obs), espece, string_agg(DISTINCT observateur,',' ORDER BY observateur), sum((effectif->>'larves')::integer) AS nb_larves, sum((effectif->>'mâles')::integer) AS nb_males from data GROUP BY espece --WHERE effectif ? 'larves'