ruby-on-rails - 按 JSON 数组中的匹配数查询和排序

Question

在 Postgres 9.4 和 Rails 的 jsonb 列中使用 JSON 数组，我可以设置一个范围，返回包含传递给范围的数组中的任何 元素的所有行方法 - 像这样:

scope :tagged, ->(tags) {
  where(["data->'tags' ?| ARRAY[:tags]", { tags: tags }])
}

我还想根据数组中匹配元素的数量对结果进行排序。

我明白我可能需要跳出 ActiveRecord 的范围来执行此操作，因此普通的 Postgres SQL 答案也很有帮助，但如果它可以包含在 ActiveRecord 中以便它可以成为可链接的范围，则可以加分。

根据要求，这是一个示例表。 (实际模式要复杂得多，但这就是我所关心的。)

 id |               data                
----+-----------------------------------
  1 | {"tags": ["foo", "bar", "baz"]}
  2 | {"tags": ["bish", "bash", "baz"]}
  3 |
  4 | {"tags": ["foo", "foo", "foo"]}

用例是根据标签查找相关内容。更多匹配标签更相关，因此结果应按匹配数量排序。在 Ruby 中，我有一个像这样的简单方法:

Page.tagged(['foo', 'bish', 'bash', 'baz']).all

应该按以下顺序返回页面:2, 1, 4。

Answer 1

您的数组仅包含 primitive values , 嵌套文档会更复杂。

查询

使用 jsonb_array_elements_text() 取消嵌套找到的行的 JSON 数组在 LATERAL 中加入并计算匹配项:

SELECT *
FROM  (
   SELECT *
   FROM   tbl
   WHERE  data->'tags' ?| ARRAY['foo', 'bar']
   ) t
, LATERAL (
   SELECT count(*) AS ct
   FROM   jsonb_array_elements_text(t.data->'tags') a(elem)
   WHERE  elem = ANY (ARRAY['foo', 'bar'])  -- same array parameter
   ) ct
ORDER  BY ct.ct DESC;  -- more expressions to break ties?

替代 INSTERSECT .这是我们可以使用此基本 SQL 功能的罕见情况之一:

SELECT *
FROM  (
   SELECT *
   FROM   tbl
   WHERE  data->'tags' ?| '{foo, bar}'::text[]  -- alt. syntax w. array
   ) t
, LATERAL (
   SELECT count(*) AS ct
   FROM  (
      SELECT * FROM jsonb_array_elements_text(t.data->'tags')
      INTERSECT ALL
      SELECT * FROM unnest('{foo, bar}'::text[])  -- same array literal
      ) i
   ) ct
ORDER  BY ct.ct DESC;

注意一个细微差别:这消耗匹配的每个元素，因此它不计算data->'tags'中不匹配的重复项就像第一个变体一样。 有关详细信息，请参阅下面的演示。

还演示了另一种传递数组参数的方法:作为数组文字:'{foo, bar}'。对于一些客户来说，这可能更容易处理:

• PostgreSQL: Issue with passing array to procedure

或者您可以创建一个服务器端搜索函数，采用 VARIADIC 参数并传递可变数量的纯 text 值:

• Passing multiple values in single parameter

相关:

• Check if key exists in a JSON with PL/pgSQL?

索引

一定要有一个功能性的 GIN 索引来支持 jsonb existence operator ?| :

CREATE INDEX tbl_dat_gin ON tbl USING gin (data->'tags');

• Index for finding an element in a JSON array
• What's the proper index for querying structures in arrays in Postgres jsonb?

重复的细微差别

根据 request in the comment 进行澄清.比方说，我们有一个包含两个 重复标签的 JSON 数组(总共 4 个):

jsonb '{"tags": ["foo", "bar", "foo", "bar"]}'

然后使用包含两个 标记的 SQL 数组参数进行搜索，其中 一个 重复(总共 3 个):

'{foo, bar, foo}'::text[]

考虑这个演示的结果:

SELECT *FROM  (SELECT jsonb '{"tags":["foo", "bar", "foo", "bar"]}') t(data), LATERAL (   SELECT count(*) AS ct   FROM   jsonb_array_elements_text(t.data->'tags') e   WHERE  e = ANY ('{foo, bar, foo}'::text[])   ) ct, LATERAL (   SELECT count(*) AS ct_intsct_all   FROM  (      SELECT * FROM jsonb_array_elements_text(t.data->'tags')      INTERSECT ALL      SELECT * FROM unnest('{foo, bar, foo}'::text[])      ) i   ) ct_intsct_all, LATERAL (   SELECT count(DISTINCT e) AS ct_dist   FROM   jsonb_array_elements_text(t.data->'tags') e   WHERE  e = ANY ('{foo, bar, foo}'::text[])   ) ct_dist, LATERAL (   SELECT count(*) AS ct_intsct   FROM  (      SELECT * FROM jsonb_array_elements_text(t.data->'tags')      INTERSECT      SELECT * FROM unnest('{foo, bar, foo}'::text[])      ) i   ) ct_intsct;

Result:

data                                     | ct | ct_intsct_all | ct_dist | ct_intsct
-----------------------------------------+----+---------------+---------+----------
'{"tags": ["foo", "bar", "foo", "bar"]}' | 4  | 3             | 2       | 2

将 JSON 数组中的元素与数组参数中的元素进行比较:

• 4 标签匹配任何搜索元素:ct。
• 3 标签在 intersect 集合中(可以匹配元素到元素):ct_intsct_all。
• 2 distinct 可以识别匹配标签:ct_dist 或 ct_intsct。

如果您没有受骗或不想排除它们，请使用前两种技术中的一种。其他两个有点慢(除了不同的结果)，因为他们必须检查是否有欺骗。