Can PostgreSQL JOIN on jsonb array objects?

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:

Let’s say there are two tables:

Table 1) organisations:

  ID (INT)  |  members (JSONB)                                        |
------------+---------------------------------------------------------|
     1      | [{ id: 23, role: "admin" }, { id: 24, role: "default" }]|
     2      | [{ id: 23, role: "user" }]

Table 2) users:

  ID (INT)  | name TEXT | email TEXT    |
------------+-----------+---------------|
     23     | Max       | [e-mail] |
     24     | Joe       | [e-mail] |

Now I want to get a result like this (all i have is the ID of the organisation [1]):

  ID (INT)  |  members (JSONB)                                       |
------------+--------------------------------------------------------|
     1      | [{ id: 23, name: "Max", email: "[e-mail]", role: 
                "admin" },
               { id: 24, name: "Joe", email: "[e-mail] ", role: 
                "default" }]
(1 row)

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.

Thanks!

Answer

Yes it is possible to meet this requirement with Postgres. Here is a solution for 9.6 or higher.

SELECT o.id, JSON_AGG(
    JSON_BUILD_OBJECT(
        'id'    , u.id, 
        'name'  , u.name, 
        'email' , u.email, 
        'role'  , e.usr->'role'
    )
)
FROM organisations o
CROSS JOIN LATERAL JSONB_ARRAY_ELEMENTS(o.data) AS e(usr)
INNER JOIN users u ON (e.usr->'id')::text::int = u.id
GROUP BY o.id

See this db fiddle.

Explanation :

• the JSONB_ARRAY_ELEMENTS function splits the organisation json array into rows (one per user) ; it is usually used in combination with JOIN LATERAL

• to join the users table, we access the content of the id field using the -> operator

• for each user, the JSONB_BUILD_OBJECT is used to create a new object, by passing a list of values/keys pairs ; most values comes from the users table, excepted the role, that is taken from the organisation json element

• the query aggregates by organisation id, using JSONB_AGG to generate a json array by combining above objects

For more information, you may also have a look at Postgres JSON Functions documentation.