Postgresql json_array_elements

Question

我有两张桌子：

表组- id (大系列)、name (varchar)、mail (json)

表邮件- id (大系列)，name (varchar)

我的数据分组

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

我的邮件数据

1, mail1@gmail.com
2, mail2@gmail.com
3, mail3@gmail.com
4, mail4@gmail.com

我的问题是：

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

我的结果

Array ( [name] => en-mails [mail] => mail1@gmail.com )
Array ( [name] => en-mails [mail] => mail2@gmail.com )
Array ( [name] => fr-mails [mail] => mail3@gmail.com )
Array ( [name] => fr-mails [mail] => mail4@gmail.com )

我的问题-如何返回查询：

Array ( [name] => en-mails [mail] => [mail1@gmail.com, mail2@gmail.com] )
Array ( [name] => fr-mails [mail] => [mail1@gmail.com, mail2@gmail.com] )

提前感谢

Answer 1

使用聚合函数阿格()

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 | {mail1@gmail.com,mail2@gmail.com}
 fr-mails | {mail3@gmail.com,mail4@gmail.com}
(2 rows)