2

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

lveselinov
  • 23
  • 1
  • 1
  • 3

1 Answers1

2

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)
klin
  • 112,967
  • 15
  • 204
  • 232
  • I have another problem - is there a way to paginate json (group_mails ->> 'id') - offcet 0 limit count(group_mails)? –  lveselinov Aug 18 '17 at 08:13
  • I'm not sure I understand the issue. Maybe ask a new question with more detailed description of the problem. – klin Aug 18 '17 at 09:18
  • Thanks for answer. If in the table 'groups', column 'mails' i have 1000 json elements - [{"id" : 1}, {"id" : 2}] ..... {"id" : 1000} , is there a way to select only 100 and get 'name' from table 'mails' only for this 100 elements? –  lveselinov Aug 18 '17 at 09:28
  • There is no simple way to do that in the actual query. You'd have to unnest the array in a subquery, add a sort order, offset and limit and use this as a derived table in the main query. A bit complicated. If I had to do this, I would probably create an auxiliary function that gives a limited number of array elements. – klin Aug 18 '17 at 10:16
  • What about **[PostgreSql : Json Array to Rows using Lateral Join](https://stackoverflow.com/questions/61709474/postgresql-json-array-to-rows-using-lateral-join)**? –  May 10 '20 at 09:15