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) 
