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!