Fetch 1:Many relation in postgreSQL as (object-relational)?

Question

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!

@JonathanWillcock yes you can say that or like MongoDB works. — Lafoune, Dec 21 '21 at 18:02
Does [this answer](https://stackoverflow.com/questions/24006291/postgresql-return-result-set-as-json-array) help you? — Jonathan Willcock, Dec 21 '21 at 18:13

score 1 · Accepted Answer · answered Dec 21 '21 at 18:45

1

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;

answered Dec 21 '21 at 18:45

Pooya

2,968
2
12
18

Thank you bro this did the trick. – Lafoune Dec 21 '21 at 19:18

Fetch 1:Many relation in postgreSQL as (object-relational)?

1 Answers1