Postgresql json_array_elements

I have 2 tables:

table groups - id (bigserial), name (varchar), mails (json)

table mails - id (bigserial), name (varchar)

My Data in groups

1, en-mails, [{"id" : 1}, {"id" : 2}]
2, fr-mails, [{"id" : 3}, {"id" : 4}]

My Data in mails

1, [email protected]2, [email protected]3, [email protected]4, [email protected]

My Query:

SELECT tg.name, tm.mail FROM groups as tg CROSS JOIN LATERAL json_array_elements (tg.mails :: json) group_mails LEFT OUTER JOIN mails as tm ON (group_mails ->> 'id') :: BIGINT = tm.c_id

My Result

Array ( [name] => en-mails [mail] => [email protected] )
Array ( [name] => en-mails [mail] => [email protected] )
Array ( [name] => fr-mails [mail] => [email protected] )
Array ( [name] => fr-mails [mail] => [email protected] )

My Question - how query return:

Array ( [name] => en-mails [mail] => [[email protected], [email protected]] )
Array ( [name] => fr-mails [mail] => [[email protected], [email protected]] )

Thanks in advance

1 Answer

Use the aggregate function array_agg():

SELECT tg.name, array_agg(tm.mail) as mail
FROM groups as tg
CROSS JOIN LATERAL json_array_elements (tg.mails :: json) group_mails
LEFT OUTER JOIN mails as tm ON (group_mails ->> 'id') :: BIGINT = tm.id
GROUP BY 1 name | mail
----------+----------------------------------- en-mails | {[email protected],[email protected]} fr-mails | {[email protected],[email protected]}
(2 rows)