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"

Answer 1

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:

1. Database Research & Development: PostgreSQL: How to convert Table Data into JSON formatted Data?
2. StackOverflow: Create nested json from sql query postgres 9.4