How to loop over an array using PostgreSQL JSON functions

Question

I have a postgreSQL database with an entities table with a permissions column of type jsonb. permissions is an array of permissions. Each permission has an array of principals and an operation. The actual JSON of the permissions column for a particular entity could look like for example like this:

[
  {
    "principals": [
      "Administrators",
      "Users"
    ],
    "operation": "read"
  },
  {
    "principals": [
      "Administrators"
    ],
    "operation": "write"
  }
]

Now I want to retrieve all entities where either "Editors" or "Guests" have "read" permission. But the only syntax I can come up with is the one below where I query only the first permission (index=0) of the entities:

SELECT * FROM entities WHERE permissions->0->'principals' ?| array['Editors', 'Guests'] and permissions->0->>'operation' = 'read'

How do I modify this query to comprise all the permissions of an entity?

Enlico · Accepted Answer · 2021-01-13 20:43:16Z

1

If I understand this correctly, this might work for you:

select * from entities e
where exists(select 1 from jsonb_array_elements(e.permissions) r
        where (r->'principals' ?| array['Users', 'Editors']) and r->>'operation' = 'read')

edited Jan 13, 2021 at 20:43

Enlico

30.3k10 gold badges72 silver badges165 bronze badges

answered Apr 1, 2020 at 10:11

Bjarni Ragnarsson

1,7811 gold badge8 silver badges8 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to loop over an array using PostgreSQL JSON functions

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related