Выбрать строки в таблице PostgreSQL где id находится в JSONB array?

Question

[morfair]

PostgreSQL 11. Табличка, например, такая:

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   |{}      |

Есть такой JSONB:

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

Как выбрать всех пользователей, id которых находится в perms JSON'а?

Comments

[unfilled]

вам нужен jsonb_array_elements

Answer 1

[Vitsliputsli]

Спасибо за интересный вопрос, не думаю, что следует id хранить в json-поле, но всякое может понадобиться, а если это единичное преобразование - то почему бы и нет.
Столкнулся с одной сложностью, преобразование json в integer, простым путем этого не сделать, поэтому делаю через специальную функцию преобразования сперва в text, а затем уже в integer. Получилась вот такая конструкция:

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

, не слишком красиво, но другого варианта пока не вижу.