Join Postgresql jsonb object with an item from an array in values-CodePudding

I have two tables in my postgresql database which I want to join:

Table 1) platforms:

  ID (INT)  |  data (JSONB)                                        
------------ ---------------------------------------------------------
     1      | {"identity": "1", "platformName": "Teradata" }
     2      | {"identity": "2", "platformName": "MSSQL" }

Table 2) users:

  ID (INT)  | data (JSONB)
------------ ----------- ---------------
     12     | { "role": "developer", "identity": "12", "accessRights": {"platforms": ["1"]} }
     13     | { "role": "admin", "identity": "13", "accessRights": {"platforms": ["1", "2"]}" }

I need to get the list of platforms along with the list of users who has access to them. Something like this:

  Platform ID | data (JSONB)
------------ ----------- ---------------
     1       | [{"role": "developer", "identity": "12"}]
     2       | [{"role": "developer", "identity": "12"}, {"role": "admin", "identity": "13"}]

I thought maybe something like this can help:

SELECT p.id, u.id, u.role
FROM users u
INNER JOIN platforms p ON (u.data->>'accessRights'->'platforms')::text::int = p.id
GROUP BY p.id

But I can't make it work. So is there anyway to get the result I need?

CodePudding user response：

A simple join is not enough because you need to aggregate information from the users.data JSON for each platform.

select p.id as platform_id, u.*
from platforms p
  cross join lateral (
    select jsonb_agg(u.data - 'accessRights') as data
    from users u
    where u.data -> 'accessRights' -> 'platforms' ? p.id::text
  ) as u

Note this only works because you stored the platform IDs in the array as strings, not as (JSON) integers, because the ? operator only works on strings.

Online example