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

Используя массивы JSON в столбце jsonb в Postgres 9.4 и Rails, я могу настроить область, которая возвращает все строки, содержащие любые элементы из массива, переданного методу области видимости:

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.

Ответы

Ответ 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}'. Это может быть проще для некоторых клиентов:

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

по теме:

Индекс

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

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

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

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