title: Can PostgreSQL JOIN on jsonb array objects?
body: <p>I am considering switching to PostgreSQL, because of the JSON support. However, I am wondering, if the following would be possible with a single query:</p>
  
  <p>Let's say there are two tables:</p>
  
  <p>Table 1) organisations:</p>
  
  <pre><code>  ID (INT)  |  members (JSONB)                                        |
  ------------+---------------------------------------------------------|
       1      | [{ id: 23, role: "admin" }, { id: 24, role: "default" }]|
       2      | [{ id: 23, role: "user" }]
  </code></pre>
  
  <p>Table 2) users:</p>
  
  <pre><code>  ID (INT)  | name TEXT | email TEXT    |
  ------------+-----------+---------------|
       23     | Max       | max@gmail.com |
       24     | Joe       | joe@gmail.com |
  </code></pre>
  
  <p>Now I want to get a result like this (all i have is the ID of the organisation [1]):</p>
  
  <pre><code>  ID (INT)  |  members (JSONB)                                       |
  ------------+--------------------------------------------------------|
       1      | [{ id: 23, name: "Max", email: "max@gmail.com", role: 
                  "admin" },
                 { id: 24, name: "Joe", email: "joe@gmail.com ", role: 
                  "default" }]
  (1 row)
  </code></pre>
  
  <p>I know this is not what JSONB is intended for and that there is a better solution for storing this data in SQL, but I am just curious if it would be possible.</p>
  
  <p>Thanks!</p>