I have two tables called user and post which they have data like this:

Users Table

 id | first_name | second_name 
  1     John          Doe
  2     Ellis         Mount               

Posts Table

 id |   topic        |    body        |   author_id
  1    Internet       ...some text          1
  2    Web 3.0        ...some text          2      

So what my question is how to how to query posts with the author user inside of it as object referring to the user who created the post.

[
   { 
     id:1,
     topic: Internet,
     body: ...some text, 
     author_id: 1
     author: {
       first_name: John,
       last_name: Doe
     }
   },

   { 
     id: 2,
     topic: Web 3.0,
     body: ...some text, 
     author_id: 2
     author: {
       first_name: Ellis,
       last_name: Mount
     }
   }
]

So, in a way to reach this, I tried something like this with `inner join:

SELECT *
FROM post 
INNER JOIN user
ON post.author_id = user.id;

unfortunately, this not fetching the data I want... so if any know how to query this please help!

CodePudding user response：

You can use JSON format and create JSON filed.

Demo

SELECT
  post.*,
  json_build_object(
    'first_name',
    first_name,
    'last_name',
    second_name
  ) as author
FROM post 
INNER JOIN "user"
ON post.author_id = "user".id;