Select rows in PostgreSQL table where id is in JSONB array?

Question

PostgreSQL

PostgreSQL 11. The table looks like this:

id|login    |password|role   |disabled|settings|
--|---------|--------|-------|--------|--------|
 1|admin    |1       |admin  |false   |{}      |
 2|guest    |1       |guest  |false   |{}      |
 4|test     |1       |manager|false   |{}      |
 5|test2    |1       |manager|false   |{}      |

There is this JSONB:

{
  "perms":	[ 1, 2, 3 ],
  "qwe":		true
}

How to select all users whose id is in JSON's perms?

Answer 1

Thanks for the interesting question, I don’t think that id should be stored in a json field, but anything may be needed, and if this is a single transformation, then why not.
I ran into one difficulty, converting json to integer, I can’t do this in a simple way, so I do it through a special conversion function first to text, and then to integer. The result is this design:

with ids as (
    select 
            (id->>0)::int id 
        from jsonb_array_elements((SELECT t->'perms' FROM "table_with_json")) as x(id)
)
select
       user_table.*
    from user_table
    join ids on user_table.id=ids.id

, not too beautiful, but I don’t see another option yet.