CODEWITHSUNDEEP
sqlpostgresqlpsql
Coding is Life
Coding is Life

Reputation: 53

SQL query. Join right table as nested object inside left table

After reading through documentation I can't find anything about how to make this simple query: there is two tables:

    first one is "PACKAGES"
       {
          id: 1,
          name: 'package_1',
          sender_id: 1,
          type: 'shipping'
       }

    second one is "USERS"
       {
          id: 1,
          name: 'user_1'
       }

When I perform Left, Right Join, or simply JOIN, its just merges this two tables into one. And id, name just overwrites with each other

What I need is:

       {
          id: 1,
          name: 'package_1',
          sender_id: 1
          type: 'shipping',
          user: {             // second table as nested object
              id: 1,
              name: 'user_1'
          }
       }

I tried everything that I can find, for example:

SELECT * FROM packages JOIN users AS user ON packages.sender_id=user.id;"

How I can put right table as an object inside left table?

Upvotes: 0

Views: 1519

Answers (1)

Stefanov.sm
Stefanov.sm

Reputation: 13049

Use to_json to present the query result as a nested JSON object:

select to_json(res) from 
(
  select p.*, to_json(u) "user"
  from packages p 
  inner join users u 
  on p.sender_id = u.id
) res;

Result:

{
  "id": 1,
  "name": "package_1",
  "sender_id": 1,
  "type": "shipping",
  "user": {
    "id": 1,
    "name": "user_1"
  }
}

Upvotes: 6

Related Questions