Combining table under different alias

Question

I have two tables trips and users. Whenever an user posts a trip, it stores id of the user as foreign key. And while fetching, I am joining those two tables the following way.

Query:

SELECT title, starting_point, ending_point, distance, trips.created_on 
FROM trips 
  JOIN users ON (users.id=trips.created_by);

Result:

{
            "title": "lorem ipsum",
            "starting_point": "Location A",
            "ending_point": "Location B",
            "distance": 20,
            "created_on": "2020-07-27T18:15:00.000Z",
            "author": "Test user"
        },

Result needed.

{
            "title": "lorem ipsum",
            "starting_point": "Location A",
            "ending_point": "Location B",
            "distance": 20,
            "created_on": "2020-07-27T18:15:00.000Z",
            "author": {
                       "id":1
                       "name": "Test user",
                       "role": "admin"
                      }
        },

In short, I want to join user table with trips table but I want to separate the content of user table in different alias in trip.

Answer 1

Whole row

You don't mention JSON, but it seems you are after to_json(b)() or similar. Example:

SELECT t.*, to_json(u) AS author
FROM   trips t
JOIN   users u ON u.id = t.created_by;

Or, without involving JSON:

SELECT t.*, u AS author
FROM   trips t
JOIN   users u ON u.id = t.created_by;

Then author is a ROW value, but without column names - that information is only in the definition of the row type.

Selected columns

You later commented:

... if I want id, email and name only from users table?

JSON:

SELECT t.*
     , json_build_object('id', u.id, 'email', u.email, 'name', u.name)) AS author
FROM   trips t
JOIN   users u ON u.id = t.created_by;

Or ROW value:

SELECT t.*
    , (SELECT x FROM (SELECT u.id, u.email, u.name) AS x)) AS author
FROM   trips t
JOIN   users u ON u.id = t.created_by;

See:

• Return as array of JSON objects in SQL (Postgres)
• Select columns inside json_agg
• Raw SQL queries in a REST Api