Postgresql – JSONB ARRAY – Obtaining results after manipulation as single JSON ARRAY

We are using postgresql 10.5. I have the following schema and data.

 create table contacts (
    contact_seq text,
    address jsonb
);

insert into contacts values ('1', '[
{ "name": "Jagan",  "email_address": "[e-mail]"} , 
 { "name": "raja",  "email_address": "[e-mail]"}  ]')

 select contact_seq, address.email_address from contacts contacts, 
 jsonb_to_recordset(contacts.address) as address(email_address text) where contact_seq = '1'

The above select query returns the email address correctly. However, it returns the result as 2 different rows. Is it possible to have the result in one row as json array instead? Or should I use only subquery to achieve this?

Answer

If you know there are only two addresses, you can access the array elements directly:

select contact_seq, 
       (address -> 0) ->> 'email_address' as email_one,
       (address -> 1) ->> 'email_address' as email_two
from contacts
where contact_seq = '1';

To get them as a JSON array, you could use a sub-select:

select contact_seq, 
       (select jsonb_agg(t -> 'email_address') 
        from jsonb_array_elements(address) as x(t)) as emails
from contacts
where contact_seq = '1';