Postgres jsonb复合查询

Question

我在PostgreSQL 10上有一张桌子，看起来像：

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"]}]');

列"form“的JSON有更多的键值对，这在这里是不相关的。

我需要根据权限"name“和一个或多个"who”值进行查询，例如：

1. 获取其权限"name“为"perm-name-1”和"who“为"group1”的所有行，"group5“应与上面示例中的所有行匹配。
2. 获取其权限"name“为"perm-name- 1”的所有行，以及仅为"group1“的”谁“应与id 1匹配行。

此外，欢迎对JSON模式的任何见解/评论，这将有助于数据库更好地执行！

Answer 1

我认为第一级数组是没有用的，特别是如果您要按名称检索权限的话。

我会这样储存它：

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"]}}
     }');

那么，您的两个查询都很容易编写：

对于第一个操作符，可以使用“任意”操作符?|：

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

对于第二个操作，?操作符就足够了：

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

您没有要求它，但也可以查询包含所有指定组的特定权限的行。这可以使用?&操作符来完成。下面将返回带有ID=1的行

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

下列人员将一事无成：

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