Combinar consultas de texto completo y no de texto

En esta página se describe cómo realizar una búsqueda que combine datos de texto completo y datos que no sean de texto.

Hacer una búsqueda mixta en todo el texto y no textual

Los índices de búsqueda admiten columnas de texto completo, de concordancia exacta, numéricas y JSON/JSONB. Puedes combinar condiciones de texto y no de texto en la cláusula WHERE de forma similar a las consultas de búsqueda de varias columnas. El optimizador de consultas intenta optimizar los predicados que no son de texto con un índice de búsqueda. Si no es posible, Spanner evalúa la condición de cada fila que coincida con el índice de búsqueda. Las columnas a las que se hace referencia y que no están almacenadas en el índice de búsqueda se obtienen de la tabla base.

Veamos un ejemplo:

GoogleSQL 

CREATE TABLE Albums (
  AlbumId STRING(MAX) NOT NULL,
  Title STRING(MAX),
  Rating FLOAT64,
  Genres ARRAY<STRING(MAX)>,
  Likes INT64,
  Cover BYTES(MAX),
  Title_Tokens TOKENLIST AS (TOKENIZE_FULLTEXT(Title)) HIDDEN,
  Rating_Tokens TOKENLIST AS (TOKENIZE_NUMBER(Rating)) HIDDEN,
  Genres_Tokens TOKENLIST AS (TOKEN(Genres)) HIDDEN
) PRIMARY KEY(AlbumId);

CREATE SEARCH INDEX AlbumsIndex
ON Albums(Title_Tokens, Rating_Tokens, Genres_Tokens)
STORING (Likes);

PostgreSQL

La compatibilidad con PostgreSQL de Spanner tiene las siguientes limitaciones:

  • La función spanner.tokenize_number solo admite el tipo bigint.
  • spanner.token no admite la tokenización de arrays.
CREATE TABLE albums (
  albumid character varying NOT NULL,
  title character varying,
  rating bigint,
  genres character varying NOT NULL,
  likes bigint,
  cover bytea,
  title_tokens spanner.tokenlist AS (spanner.tokenize_fulltext(title)) VIRTUAL HIDDEN,
  rating_tokens spanner.tokenlist AS (spanner.tokenize_number(rating)) VIRTUAL HIDDEN,
  genres_tokens spanner.tokenlist AS (spanner.token(genres)) VIRTUAL HIDDEN,
PRIMARY KEY(albumid));

CREATE SEARCH INDEX albumsindex
ON albums(title_tokens, rating_tokens, genres_tokens)
INCLUDE (likes);

El comportamiento de las consultas en esta tabla incluye lo siguiente:

  • Rating y Genres se incluyen en el índice de búsqueda. Spanner acelera las condiciones mediante listas de publicación de índices de búsqueda. ARRAY_INCLUDES_ANY y ARRAY_INCLUDES_ALL son funciones de GoogleSQL y no se admiten en el dialecto de PostgreSQL.

    SELECT Album
FROM Albums
WHERE Rating > 4
  AND ARRAY_INCLUDES_ANY(Genres, ['jazz'])

  • La consulta puede combinar conjunciones, disyunciones y negaciones de cualquier forma, incluidas las que mezclan predicados de texto completo y que no son de texto. Esta consulta se acelera por completo con el índice de búsqueda.

    SELECT Album
FROM Albums
WHERE (SEARCH(Title_Tokens, 'car')
      OR Rating > 4)
  AND NOT ARRAY_INCLUDES_ANY(Genres, ['jazz'])

  • Likes se almacena en el índice, pero el esquema no solicita a Spanner que cree un índice de tokens para sus posibles valores. Por lo tanto, el predicado de texto completo en Title y el predicado que no es de texto en Rating se aceleran, pero el predicado en Likes no. En Spanner, la consulta obtiene todos los documentos que contienen el término "car" en Title y una valoración superior a 4. Después, filtra los documentos que no tienen al menos 1000 Me gusta. Esta consulta usa muchos recursos si casi todos los álbumes tienen el término "coche" en el título y casi todos tienen una valoración de 5, pero pocos álbumes tienen 1000 Me gusta. En estos casos, indexar Likes de forma similar a Rating ahorra recursos.

    GoogleSQL 

    SELECT Album
FROM Albums
WHERE SEARCH(Title_Tokens, 'car')
  AND Rating > 4
  AND Likes >= 1000

    PostgreSQL 

    SELECT album
FROM albums
WHERE spanner.search(title_tokens, 'car')
  AND rating > 4
  AND likes >= 1000

  • Cover no se almacena en el índice. La siguiente consulta realiza una combinación hacia atrás entre AlbumsIndex y Albums para obtener Cover de todos los álbumes coincidentes.

    GoogleSQL 

    SELECT AlbumId, Cover
FROM Albums
WHERE SEARCH(Title_Tokens, 'car')
  AND Rating > 4

    PostgreSQL 

    SELECT albumid, cover
FROM albums
WHERE spanner.search(title_tokens, 'car')
  AND rating > 4

Siguientes pasos