CODEWITHSUNDEEP
postgresql
Zanko
Zanko

Reputation: 4694

PostgreSQL, how to append result to another query result

First Query

SELECT * FROM users WHERE _id = 1

result from query:

{
  username: "Test",
  nickname: "somename"
}

Second Query

SELECT json_object_agg(permission, true) FROM user_permissions WHERE user_id = 1

result from query:

{
  add: true,
  delete: true,
  somepermission: true
}

Third Query

SELECT array_agg(number) FROM user_phone_numbers WHERE user_id = 1

result from query:

[
  00000-0000-000,
  11111-1111-111
]

Basically I want to put the result of second and third query into first query so the final result will be

{
  username: "Test",
  nickname: "somename"
  customJSONPermission: {
        add: true,
        delete: true,
        somepermission: true
  },
  customerArrayPhone: [
      00000-0000-000,
      11111-1111-111
  ]
}

How do craft a single SQL command to handle this kind of operation. What kind of keywords or function I should look up?

Upvotes: 0

Views: 4257

Answers (1)

Roman Tkachuk
Roman Tkachuk

Reputation: 3276

You can just put all queries in one:

SELECT u.username,
       u.nikname,
       (SELECT json_object_agg(permission, true) FROM user_permissions WHERE user_id = u._id) AS customJSONPermission,
       (SELECT array_agg(number) FROM user_phone_numbers WHERE user_id = u._id) AS customerArrayPhone
  FROM users AS u
 WHERE u._id = 1

Upvotes: 3

Related Questions