Merging one-to-many and one-to-one relation in the same JSON object in postgresql LINK TO FIDDLE: https://www.db-fiddle.com/f/a6FXTqJHpU9smuJTUKqwuV/0

My tables are as follows:

=&gt; \d ops;
                                                   Table "public.ops"
          Column           |           Type           | Collation | Nullable |                      Default                       
---------------------------+--------------------------+-----------+----------+----------------------------------------------------
 op_id                     | integer                  |           | not null | nextval('ops_op_id_seq'::regclass)
 name                      | text                     |           |          | 
Indexes:
    "ops_pkey" PRIMARY KEY, btree (op_id)

=&gt; \d events;
                                                   Table "public.events"
         Column         |         Type         | Collation | Nullable |                        Default                         
------------------------+----------------------+-----------+----------+--------------------------------------------------------
 event_id               | integer              |           | not null | nextval('events_event_id_seq'::regclass)
 desc                   | text                 |           |          | 
 op_id                  | integer              |           |          | 
Indexes:
    "events_pkey" PRIMARY KEY, btree (event_id)

=&gt; \d op_extra_info;
                                   Table "public.op_extra_info"
      Column       |  Type   | Collation | Nullable |                      Default                       
-------------------+---------+-----------+----------+----------------------------------------------------
 op_extra_info_id  | integer |           | not null | nextval('op_extra_info_op_extra_info_id_seq'::regclass)
 op_id             | integer |           |          | 
 extra_info1       | text    |           |          | 
 extra_info2       | text    |           |          | 
Indexes:
    "op_extra_info_pkey" PRIMARY KEY, btree (op_extra_info_id)


For each op, there can be many events (each event refers to its corresponding op by means of the "op_id" field) but only a single extra info (again, using "op_id" to refer to the op it's related to). My goal is to return a JSON result as follows:

{ "name": ....,
  "extra_info": { "extra_info1": ....., "extra_info2": ..... },
  "events": [ { "desc": .... }, { "desc": .... }, ... ]
}


I've been able to get partway through with this query:

SELECT to_json(x) FROM (
  SELECT ops.name, array_agg(events.descr) as events
    FROM ops
    LEFT JOIN (select event_id, op_id, descr FROM events) events USING(op_id)
    GROUP BY ops.name) AS x;


But I can't find a way to:


get a real object for each element of the "events" array (ie, a real { "descr": .... } object instead of just the value of the "descr" column)
add the extra_info data as sub-object of the "extra_info" key at the top level.
See the composite types documentation for how to handle entire rows as a single value (which ends up as an object in JSON).

Apparently, the only way to get the correct field names for the extra info is to create a new type for that subobject:

CREATE TYPE extra_info_object AS (
  extra_info1 TEXT,
  extra_info2 TEXT
);

SELECT to_json(data)
FROM (SELECT name,
             ROW(extra_info1,
                 extra_info2)::extra_info_object AS extra_info,
             (SELECT array_agg(events)
              FROM events
              WHERE events.op_id = ops.op_id
             ) AS events
      FROM ops
      LEFT JOIN op_extra_info USING (op_id)
) AS data;