How can I retrieve other table's data as an object?

Question

select
  e.*,
  c.*
from
  restaurants e
  inner join restaurants_social c on e.id = c.id

And this one returns the result like this

{
  id: 10,
  address: 'asjofosifje 23'
  instagram: 'sadf'
  .....
}

However, I want to have it like that. What should I change in order to make 'social_media' as an object?

{
  id: 10,
  address: 'asjofosifje 23'
  social_media: {
    instagram: 'sadf'
    ......
  }
}

Answer 1

Depends on how these relate to each other. To get a social_media as opposed to values of social_media, just don't unpack it. Notice the missing .*: demo

select
  e.*,
  c
from
  restaurants e
  inner join restaurants_social c on e.id = c.id;

Chances are, your mapper will give you exactly what you showed. Problem is I'd expect each restaurant to have many restaurants_social, in which case you'll need to aggregate. For each restaurant array_agg(c) will get you an array of their restaurants_social, entirely mappable to array of the given entity.

The jsonb functions give you full control over the JSON structure you retrieve but that's sort of duplicating what your ORM already does:

select
  to_jsonb(e) || jsonb_build_object('social_media',c)
from
  restaurants e
  inner join restaurants_social c on e.id = c.id;

restaurant_with_social
{     "id": 1,     "name": "restaurant1",     "address": "180 Prince St, New York, NY 10012, United States",     "social_media": {         "x": "x.com/ristoranti1",         "id": 1,         "facebook": "fb.com/rest_1",         "instagram": "instagram.com/rastaurant1"     } }