Postgres Subquery - How to create ALIAS in object format from subqueries

Question

I am trying to wrap several queries into 1 complex query.

Here is the scenario. Given a user_id of 'xxxxx-xx-xxxxx' I have to query for several data points within several tables. I have most of the queries below wrapped up with some JOIN(s) but would really like to see if its possible to get the last queries where I am getting the users name and email wrapped into an ALIAS column stored as an object? I am not sure if it's possible but here are the queries broken down..

THE LONG WAY

Query 1:

SELECT * FROM requests WHERE created_by = 'xxxx-xx-xxxxx';

This query will return a row as such:

id   |   created_by   |   created_date ...
1141   xxxx-xx-xxxxx    2019-04-09 19:33:40.889+00 ...

I then have to run another query to find all approvers for the returned id Query 2:

SELECT * FROM approvers WHERE request_id = 1141

This could possibly return many rows as such:

request_id   |   user_id      |   decision    |    type   | .....
1141            xxxxx-xx-xxxxx       approve         approver     ......
1141            aaaaa-aa-aaaaa      approve         approver     ......
1141            bbbbb-bb-bbbbb    under review      watcher     ......
1141            ccccc-cc-ccccc      reject          approver     .......

and finally to find the users name I have to run queries (for each returned row of Query 2) Query 3,4,5,6:

SELECT * FROM users WHERE id = 'xxxx-xx-xxxxx' LIMIT 1

will return:

id           |     given_name     |     family_name     |     email     | .....
xxxx-xx-xxxxx      John                  Doe             [email protected]    ....

Is it possible to run a subquery and create an ALIAS column of approver_info with the associated approvers and users stored as a JSON object as such?

Desired Query / Result:

1 Query:

SELECT * FROM requests
WHERE created_by = 'xxxxx-xx-xxxxx'
SUBQUERY HERE ...

That will produce the following result:

id   |  created_by    |  created_date               |  approver_info    ...
1141    xxxx-xx-xxxxx    2019-04-09 19:33:40.889+00    [{"id" : "xxxxx-xx-xxxxx", "given_name" : "John", "family_name" : "Doe", "email":"[email protected]","decision" : "approve", "type" : "approver"},
                                                        {"id" : "aaaaa-aa-aaaaa", "given_name" : "Sansa", "family_name" : "Stark", "email":"[email protected]","decision" : "approve", "type" : "approver"},
                                                        {"id" : "bbbbb-bb-bbbbb", "given_name" : "Arya", "family_name" : "Stark", "email":"[email protected]","decision" : "under review", "type" : "watcher"},
                                                        {"id" : "ccccc-cc-ccccc", "given_name" : "Ned", "family_name" : "Stark", "email":"[email protected]","decision" : "approve", "type" : "approver"}]

Answer 1

Your query could look like this:

SELECT r.id, r.created_by, r.created_date
    , (SELECT json_agg(au)
       FROM  (
          SELECT u.id, u.given_name, u.family_name, u.email, a.decision, a.type -- , ...
          FROM   approvers a
          JOIN   users     u ON u.id = a.user_id
          WHERE  a.request_id = r.id
          ) au
      ) AS approver_info
FROM   requests  r
WHERE  r.created_by = 'xxxx-xx-xxxxx';

Using a correlated subquery to aggregate 0-n related rows into the JSON array approver_info.

A LATERAL join would be an equivalent alternative:

• What is the difference between LATERAL JOIN and a subquery in PostgreSQL?

The nested subquery is one way to build the JSON array with original column names as keys. There are others (with json_build_object() or row_to_json()):

• Return multiple columns of the same row as JSON array of objects

Answer 2

seems you are looking for join

SELECT * 
FROM requests r
INNER JOIN  approvers a on r.id = a.id 
INNER JOIN users u on u.id = r.user_id 
WHERE r.created_by = 'xxxx-xx-xxxxx';

and if you need some part of the row result as json you could try using row_to_json()

select r.user_id, r.request_id,  row_to_json(row(your_col1,your_col2,.....))
FROM requests r
INNER JOIN  approvers a on r.id = a.id 
INNER JOIN users u on u.id = r.user_id 
WHERE r.created_by = 'xxxx-xx-xxxxx';