ruby on rails - Consulta y orden por número de coincidencias en matriz JSON

ruby-on-rails postgresql

Question

Utilizando matrices JSON en una columna jsonb en Postgres 9.4 y Rails, puedo configurar un ámbito que devuelve todas las filas que contienen elementos de una matriz pasada al método de alcance, como por ejemplo:

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

También me gustaría ordenar los resultados según la cantidad de elementos coincidentes en la matriz.

Aprecio que pueda necesitar salir de los límites de ActiveRecord para hacer esto, por lo que una respuesta de vainilla Postgres SQL también es útil, pero puntos de bonificación si se puede incluir en ActiveRecord para que pueda ser un alcance de cadena.

Según lo solicitado, aquí hay una tabla de ejemplo. (El esquema real es mucho más complicado, pero esto es todo lo que me preocupa).

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

El caso de uso es encontrar contenido relacionado basado en etiquetas. Más etiquetas coincidentes son más relevantes, por lo tanto, los resultados deben ordenarse por el número de coincidencias. En Ruby tendría un método simple como este:

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

Que debería devolver las páginas en el siguiente orden: 2, 1, 4 .

Answer 1

Estoy publicando detalles de mi solución en Ruby, en caso de que sea útil para cualquiera que aborde el mismo problema.

Al final, decidí que un alcance no es apropiado ya que el método devolverá una matriz de objetos (no una relación ActiveRecord::Relation concatenable), así que escribí un método de clase y proporcioné una forma de pasar un alcance encadenado a a través de un bloque:

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

Esto se puede usar así:

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

Answer 2

Sus matrices contienen solo valores primitivos , los documentos anidados serían más complicados.

Consulta

Agrupe las matrices JSON de las filas encontradas con jsonb_array_elements_text() en una unión LATERAL y cuente las coincidencias:

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?

Alternativa con INSTERSECT . Es una de las pocas ocasiones en que podemos hacer uso de esta característica SQL básica:

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;

Tenga en cuenta una sutil diferencia : esto consume cada elemento cuando coincide, por lo que no cuenta los duplicados no coincidentes en los data->''tags'' como lo hace la primera variante. Para más detalles, vea la demostración a continuación.

También se demuestra una forma alternativa de pasar el parámetro array: como array literal ( text ): ''{foo, bar}'' . Esto puede ser más simple de manejar para algunos clientes:

• PostgreSQL: Emita con pasar la matriz al procedimiento

O puede crear una función de búsqueda del lado del servidor que tome un parámetro VARIADIC y pase una cantidad variable de valores de text sin text :

• Pasar múltiples valores en un solo parámetro

Relacionado:

• Compruebe si la clave existe en un JSON con PL / pgSQL?

Índice

Asegúrese de tener un índice GIN funcional para admitir el operador de existencia jsonb ?| :

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

• Índice para encontrar un elemento en una matriz JSON
• ¿Cuál es el índice adecuado para consultar estructuras en matrices en Postgres jsonb?

Matices con duplicados

Aclaración según solicitud en el comentario . Digamos, tenemos una matriz JSON con dos etiquetas duplicadas (4 en total):

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

Y busque con un parámetro de matriz SQL que incluya ambas etiquetas, una de ellas duplicada (3 en total):

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

Considere los resultados de esta demostración:

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;

Resultado:

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

Comparando elementos en la matriz JSON con elementos en el parámetro de la matriz:

• 4 etiquetas coinciden con cualquiera de los elementos de búsqueda: ct .
• 3 etiquetas en el conjunto se cruzan (pueden combinarse elemento a elemento): ct_intsct_all .
• Se pueden itentificar 2 etiquetas distintas : ct_dist o ct_intsct .

Si no tiene engaños o si no le importa excluirlos, use una de las dos primeras técnicas. Los otros dos son un poco más lentos (además del resultado diferente), porque tienen que buscar engaños.