how to get sql result as objects

I have three tables:

Table USER 
id
name
email

Table CAMPUS
id
user_id (foreign key)
name 

Table POST
id
title
user_id (foreign key)

A User belongs to a Campus, and a Post belong to a User. I want to write a query to fetch posts inner join with user inner join with campus.

The result i get:

[{ 
id
username
campus name
title
... 
}]

Everything is in a single object. Instead, I want nested objects like this:

[{
post_title:
post_id:
...
User: {id name... }
campus:{id name ...}

}]

This way, User and Campus are inherited in the post according to foreign keys. How can i achieve it with raw sql? Should I have to parse it later?

Answer

You can use join and use json functions to generate the result that you expect.

Your question is not very accurate about the table structures, but this should look like:

select 
    json_object(
        'id',     p.id
        'title',  p.title
        'user',   json_object('id', u.id, 'name', u.name),
        'campus', json_object('id', c.id, 'name', c.name)
    ) post_object
from posts p
inner join users u on u.id = p.user_id
inner join campus c on c.user_id = u.id

This gives you a resultset with a single column and one record per post as a json object that contains nested objects that represent the user and the campus.

If you want a result with a single row and all rows stuffed in a json array, you can use json_arrayagg() on top of this:

select json_arrayagg(
    json_object(
        'id',     p.id
        'title',  p.title
        'user',   json_object('id', u.id, 'name', u.name),
        'campus', json_object('id', c.id, 'name', c.name)
    ) 
) all_posts_objects
from posts p
inner join users u on u.id = p.user_id
inner join campus c on c.user_id = u.id