0

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
Rahul Sharma
  • 5,562
  • 4
  • 24
  • 48
Morteza Malvandi
  • 1,656
  • 7
  • 30
  • 73
  • You say `user: "user1"` and `permission: 2` and according to you row with `id`=2 satisfies this. But I don't see any element inside the array where value of `permission` is 2. – Rahul Sharma Oct 01 '22 at 09:11

1 Answers1

1

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.

klin
  • 112,967
  • 15
  • 204
  • 232
  • Note that the first one doesn't necessarily pick the `permission` from the array element of the one with "user1". You would need to use `jsonb_path_query_first("access", '$[*] ? (@user == "user1").permission')::int & 2 = 2` –  Oct 01 '22 at 17:51
  • @a_horse_with_no_name - You are definitely right. The query corrected, thanks. – klin Oct 01 '22 at 19:16