CODEWITHSUNDEEP
postgresql
lveselinov
lveselinov

Reputation: 23

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

Upvotes: 2

Views: 20968

Answers (1)

klin
klin

Reputation: 121854

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)

Upvotes: 2

Related Questions