0

I have a table on PostgreSQL 10 that looks like:

CREATE TABLE forms (id serial, form jsonb);
INSERT INTO forms (id, form) VALUES (
  1, '"permissions": [ {"name": "perm-name-1", "who":["group1", "group2",]},
                       {"name": "perm-name-2", "who":["group1", "group3"]}]');
INSERT INTO forms (id, form) VALUES (
  2, '"permissions": [ {"name": "perm-name-1", "who":["group5", "group6"]},
                       {"name": "perm-name-2", "who":["group7", "group8"]}]');

The JSON for column "form" has more key-value pairs which are irrelevant here.

I need to query based on the permissions "name" and one or more "who" values e.g. :

  1. Get all rows whose permission "name" is "perm-name-1" and "who" is any of "group1", "group5" should match all the rows of the example above.
  2. Get all rows whose permission "name" is "perm-name-1" and "who" is just "group1" should match row with id 1.

Also, any insight/comment on a JSON schema which would help the database to perform better is welcomed!

1

I think the first level of arrays is not useful, especially if you are going to retrieve permissions by name.

I would store it like this:

CREATE TABLE forms (id serial, form jsonb);
INSERT INTO forms (id, form) VALUES 
(1, '{"permissions": 
         {"perm-name-1": {"who": ["group1","group2"]}, 
          "perm-name-2": {"who": ["group1","group3"]}}
     }'), 
(2, '{"permissions": 
         {"perm-name-1": {"who": ["group5","group6"]}, 
          "perm-name-2": {"who": ["group7","group8"]}}
     }');

Then both your queries are quite easy to write:

For the first one you can use the "any" operator ?|:

select *
from forms
where form -> 'permissions' -> 'perm-name-1' -> 'who' ?| array['group1', 'group5']

For the second one, the ? operator is enough:

select *
from forms
where form -> 'permissions' -> 'perm-name-1' -> 'who' ? 'group1'

You didn't ask for it, but you can also query for rows where a specific permissions contains all specified groups. That can be done using the ?& operator. The following would return the row with ID=1

select *
from forms
where form -> 'permissions' -> 'perm-name-1' -> 'who' ?& array['group1', 'group2'];

The following would return nothing:

select *
from forms
where form -> 'permissions' -> 'perm-name-1' -> 'who' ?& array['group2', 'group3'];
  • Thanx for it! How much more complicated do the queries become with the original schema? Can you provide an example? Should I create a GIN as well? – Vagelis Savvas Nov 19 '18 at 15:04
  • @VagelisS: with the original structure you need to unnest the array (using jsonb_each()) every time you want to find something in the array. – a_horse_with_no_name Nov 19 '18 at 15:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.