Merging one-to-many and one-to-one relation in the same JSON object in postgresql

Question

LINK TO FIDDLE: https://www.db-fiddle.com/f/a6FXTqJHpU9smuJTUKqwuV/0

My tables are as follows:

=> \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)
=> \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)
=> \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.

Answer 1

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;

• homer5439: I need to have "extra_info" as a top-level key, and its value should be an object with the two keys "extra_info1" and "extra_info2".