0

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.

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:

4
  • I am sorry, I didn't quite know how to phrase my question in this case. Thank you very much for the answer. Now I know where to look for any further issues. Have a nice day good sir. – Zero Live Jul 30 '20 at 16:09
  • Do you happen to know how do I select the element on to_json function? like if I want id, email and name only from users table. – Zero Live Jul 30 '20 at 17:20
  • @ZeroLive: I added another answer. Also consider the linked answers. Please remember to ask new questions as new question. Comments are not the place. – Erwin Brandstetter Jul 30 '20 at 21:57
  • Sure. I will keep that in mind. Thank you very much. – Zero Live Jul 31 '20 at 1:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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