How query bit operator in postgresql jsonb column-CodePudding

I have a table as follows in PostgreSQL:

id | access
--------------------------
1  | [{"id": 1, "user":"user1", "permission": 1}, {"id": 2, "user":"user2", "permission": 3}]
2  | [{"id": 1, "user":"user1", "permission": 3}, {"id": 2, "user":"user2", "permission": 7}]

I want to get records with user: "user1" and permission: 2(in other words permission & 2 = 2). The response for the above example is record with id 2. My query for filter user is as follows but I can't handled it for permission:

Select * from my_table where jsonb_path_exists("access", '$[*] ? (@.user == "user1")')

What do I add to query for filtering permission?

Update: Permissions are in bit codec. some samples are as followes:

1 -> 001
2 -> 010
3 -> 011
4 -> 100
5 -> 101
6 -> 110

CodePudding user response：

There are no bitwise operators in SQL/JSON Path Language. Use an additional condition with jsonb_path_query_first()

select *
from my_table
where jsonb_path_exists("access", '$[*] ? (@.user == "user1")')
and jsonb_path_query_first("access", '$[*] ? (@.user == "user1").permission')::int & 2 = 2

or the function jsonb_array_elements() in lateral join

select t.* 
from my_table t
cross join jsonb_array_elements(access)
where value->>'user' = 'user1'
and (value->>'permission')::int & 2 = 2

Test it in db<>fiddle.