Postgresql – Postgres jsonb complex query

indexjsonpostgresqlpostgresql-10

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!

Best Answer

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'];
Related Question