Запрос и порядок по количеству совпадений в массиве JSON

Ответ 1

В ваших массивах содержатся только примитивные значения, вложенные документы будут более сложными.

Query

Unnest массивы JSON найденных строк с jsonb_array_elements_text() в совпадении со списком 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', как это делает первый вариант. Подробнее см. Ниже.

Также демонстрирует альтернативный способ передать параметр массива: как литерал массива (text): '{foo, bar}'. Это может быть проще для некоторых клиентов:

• PostgreSQL: проблема с передачей массива в процедуру

Или вы можете создать функцию поиска на стороне сервера, взяв параметр VARIADIC и передать переменное число простых значений text:

• Передача нескольких значений в одном параметре

по теме:

• Проверить, существует ли ключ в JSON с PL/pgSQL?

Индекс

Обязательно наличие функционального индекса GIN для поддержки jsonb оператора существования ?|:

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

• Указатель для поиска элемента в массиве JSON
• Каков правильный индекс для запросов структур в массивах в Postgres jsonb?

Нюансы с дубликатами

Уточнение по запросу в комментарии. Скажем, у нас есть массив 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;

Результат:

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

Сравнение элементов массива JSON с элементами в параметре массива:

• Теги
• 4 соответствуют любому из элементов поиска: ct.
• 3 теги в наборе пересекаются (могут быть сопоставлены между собой): ct_intsct_all.
• 2 могут быть идентифицированы различные теги: ct_dist или ct_intsct.

Если у вас нет обмана или если вы не хотите их исключать, используйте один из первых двух методов. Остальные два немного медленнее (помимо другого результата), потому что они должны проверять наличие обмана.

Ответ 2

Я размещаю детали своего решения в Ruby, если это полезно для тех, кто занимается одной и той же проблемой.

В конце концов я решил, что область не подходит, поскольку метод вернет массив объектов (а не цепочку ActiveRecord::Relation), поэтому я написал метод класса и предоставил способ передать цепочку область действия через блок:

def self.with_any_tags(tags, &block)
  composed_scope = (
    block_given? ? yield : all
  ).where(["data->'tags' ?| ARRAY[:tags]", { tags: tags }])

  t   = Arel::Table.new('t',  ActiveRecord::Base)
  ct  = Arel::Table.new('ct', ActiveRecord::Base)

  arr_sql = Arel.sql "ARRAY[#{ tags.map { |t| Arel::Nodes::Quoted.new(t).to_sql }.join(', ') }]"
  any_tags_func = Arel::Nodes::NamedFunction.new('ANY', [arr_sql])

  lateral = ct
    .project(Arel.sql('e').count(true).as('ct'))
    .from(Arel.sql "jsonb_array_elements_text(t.data->'tags') e")
    .where(Arel::Nodes::Equality.new Arel.sql('e'), any_tags_func)

  query = t
    .project(t[Arel.star])
    .from(composed_scope.as('t'))
    .join(Arel.sql ", LATERAL (#{ lateral.to_sql }) ct")
    .order(ct[:ct].desc)

  find_by_sql query.to_sql
end

Это можно использовать так:

Page.with_any_tags(['foo', 'bar'])

# SELECT "t".*
# FROM (
#   SELECT "pages".* FROM "pages"
#   WHERE data->'tags' ?| ARRAY['foo','bar']
#   ) t,
# LATERAL (
#   SELECT COUNT(DISTINCT e) AS ct
#   FROM jsonb_array_elements_text(t.data->'tags') e
#   WHERE e = ANY(ARRAY['foo', 'bar'])
#   ) ct
# ORDER BY "ct"."ct" DESC

Page.with_any_tags(['foo', 'bar']) do
  Page.published
end

# SELECT "t".*
# FROM (
#   SELECT "pages".* FROM "pages"
#   WHERE pages.published_at <= '2015-07-19 15:11:59.997134'
#   AND pages.deleted_at IS NULL
#   AND data->'tags' ?| ARRAY['foo','bar']
#   ) t,
# LATERAL (
#   SELECT COUNT(DISTINCT e) AS ct
#   FROM jsonb_array_elements_text(t.data->'tags') e
#   WHERE e = ANY(ARRAY['foo', 'bar'])
#   ) ct
# ORDER BY "ct"."ct" DESC