Postgres : create JSON object from JSON array and add keys

Question

I need to create json object with keys from json array, keys are first element inside json of array. Please check below for clarity.

customer :<br/>
    **id**    |  **email**          | **app_id** <br/>
    1         |  [email protected]      | abc  <br/>


milestone : <br/>
**id** | **milestone_name**    | **app_id** | **raised_at** <br/>
1  | PROFILE_COMPLETED | abc    | 2019-05-06 <br/>
2  | ADDRESS_COMPLETED | abc    | 2019-05-06  <br/>


select email,
 (
    select 
        array_to_json(
          array_agg(d)
        ) 
      from 
        (
          select 
            milestone_name, 
            raised_at, 
          from 
            milestone m 
          where 
            m.app_id = c.app_id
        ) d
    ) as milestones 
from customer c; 

Above query is giving output :

email      | [email protected] <br/>
milestones |  [{"milestone_name":"PROFILE_COMPLETED","raised_at":"2019-05-06"},{"milestone_name":"ADDRESS_COMPLETED","raised_at":"2019-05-06"}]

I need json object with keys :

email      | [email protected]  <br/>
milestones | { "PROFILE_COMPLETED":{"milestone_name":"PROFILE_COMPLETED","raised_at":"2019-05-06"}, "ADDRESS_COMPLETED":{"milestone_name":"ADDRESS_COMPLETED","raised_at":"2019-05-06"}} 

Answer 1

You don't mention what version you are using, but this will work in recent versions:

select email, 
      json_object_agg(
         milestone_name, json_build_object(
                            'milestone_name', milestone_name, 
                            'raised_at', raised_at
                         )
      )
FROM milestone
join customer on customer.app_id = milestone.app_id
GROUP BY email;

email           | [email protected]
json_object_agg | { "ADDRESS_COMPLETED" : {"milestone_name" : "ADDRESS_COMPLETED", "raised_at" : "2019-05-06"}, "PROFILE_COMPLETED" : {"milestone_name" : "PROFILE_COMPLETED", "raised_at" : "2019-05-06"} }