PostgreSQL json_array_elements

Question

我有 2 个表:

表组 - id (bigserial), name (varchar), mails (json)

表邮件 - id (bigserial), 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

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