2

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.

1 Answer 1

0

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;
1
  • 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".
    – homer5439
    Feb 26, 2020 at 10:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.