Single query that return data of two tables and multiple rows of a third one, on PostgreSQL

Question

I need to make a SELECT query in PostgreSQL that return data of two tables and multiple rows of a third one.

Table A:

id	serviceId	requestId	description
1	1	4	row
2	2	5	row

Table B:

serviceId	title
1	row
2	row

Table C:

requestId	seq	description
4	1	row
4	2	row
5	1	row
5	2	row

I need the following kind of return:

[{
  id: 1, 
  serviceId: 1, 
  requestId:4,
  description: 'row',
  title: 'row',
  request: [{
      requestId: 4,
      seq:1,
      description: 'row'
    },
    {
      requestId: 5,
      seq:2,
      description: 'row'
    }]
}]

The point is that i need to get multiple rows from the "C" table nested in the response. The JSON is an example to illustrate the case, It doesn't have to be exactly that way. I'm in nodejs with pg library.

It would be possible to make it on a single query? Or databases doesn't response that way of nested rows and i've to do it by two querys?

I have this (incomplete) query:

        SELECT A.* , B.*
        FROM A
        INNER JOIN service ON A."serviceId"= B."serviceId"
        WHERE A.id=1

The condition for the "C" table would be:

        A."requestId" = C."requestId"

Sorry, I'm in nodejs and pg(library for connect to db) return an array of objects, I will edit the post to make it universal. — Tomas1234, Feb 18 '22 at 12:52

score 0 · Answer 1 · answered Feb 18 '22 at 14:07

To be honest, I'm not used to working with JSON in PostgreSQL, but I think this might do it:

drop table if exists a;
create temp table a (id int, serviceid int, requestid int, description text);
insert into a
values
(1,1,4,'row'),
(2,2,5,'row');

drop table if exists b;
create temp table b (serviceid int, title text);
insert into b
values
(1,'row'),
(2,'row');

drop table if exists c;
create temp table c (requestid int, seq int, description text);
insert into c
values
(4,1,'row'),
(4,2,'row'),
(5,1,'row'),
(5,2,'row');

SELECT json_agg(
        row_to_json(
                    (
                    select t from (select a.id,a.serviceid,a.requestid,a.description,b.title,c.request) as t(id,serviceid,requestid,description,title,request)
                    )
                    )
                )
FROM a
INNER JOIN b ON a.serviceid = b.serviceid
INNER JOIN (SELECT requestid, json_agg(
                                  row_to_json(
                                              (
                                              select ct from (select c.requestid,c.seq,c.description) as ct(requestid,seq,description)
                                              )
                                              )
                                          ) as request
            FROM c
            GROUP BY 1
) c
ON a.requestid = c.requestid;

References:

Single query that return data of two tables and multiple rows of a third one, on PostgreSQL

1 Answers1