PostgreSQL, how to append result to another query result

Question

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?

Hi this is not exactly JOIN right? JOIN will produce many rows. I just need one row with appended fields — Zanko
– Zanko, Commented Feb 12, 2017 at 17:32

Roman Tkachuk · Accepted Answer · 2017-02-12 17:52:18Z

3

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

answered Feb 12, 2017 at 17:52

Roman Tkachuk

3,2761 gold badge18 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Zanko Over a year ago

Thank you for this! I guess this is absolutely the shortest way? What if my userobject have 100 fields must I write a really long SQL just to put u.field1, u.field2 ... u.field100?

Roman Tkachuk Over a year ago

You can use u.* if you want to get all fields in table

Zanko Over a year ago

Perfect! Thank you so much!

Collectives™ on Stack Overflow

PostgreSQL, how to append result to another query result

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related