Postgres jsonb complex query 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. :


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.
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!
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 -&gt; 'permissions' -&gt; 'perm-name-1' -&gt; 'who' ?| array['group1', 'group5']


For the second one, the ? operator is enough:

select *
from forms
where form -&gt; 'permissions' -&gt; 'perm-name-1' -&gt; '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 ?&amp; operator. The following would return the row with ID=1

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


The following would return nothing:

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