Skip to main content

Edit this page

Exact Value Matching

Match precise token values in indexed columns. This is the simplest form of search — find documents that contain an exact term.

See Setup for the shared dataset used in all examples.

Match a single value​

Use TERM_EQ to match a single exact token:

SELECT id, title, genre
FROM movies_idx
WHERE TERM_EQ(genre, 'sci-fi')
ORDER BY id;
 id | title                           | genre
----+---------------------------------+--------
  1 | The Matrix                      | sci-fi
  2 | The Matrix Reloaded             | sci-fi
  3 | The Matrix Revolutions          | sci-fi
  7 | Star Trek: The Motion Picture   | sci-fi
  8 | Alien                           | sci-fi

Match multiple alternatives​

Use TERM_IN to match any of several values — more efficient than chaining OR:

SELECT id, title, genre
FROM movies_idx
WHERE TERM_IN(genre, 'comedy', 'drama')
ORDER BY id;
 id | title                         | genre
----+-------------------------------+--------
  6 | Scary Movie                   | comedy
  9 | Café Society                  | drama
 10 | The Grand Budapest Hotel      | comedy

Negation​

Combine NOT with term operations to exclude matches:

SELECT id, title, genre
FROM movies_idx
WHERE NOT TERM_EQ(genre, 'sci-fi')
ORDER BY id;

Search for an exact genre and a phrase in the description:

SELECT id, title
FROM movies_idx
WHERE TERM_EQ(genre, 'sci-fi') AND PHRASE(description, 'alien spacecraft')
ORDER BY id;
 id | title
----+-----------------------------------
  7 | Star Trek: The Motion Picture

Combine with analytics​

Search and aggregate in the same query:

SELECT genre, COUNT(*) AS count, AVG(runtime) AS avg_runtime
FROM movies_idx
WHERE PHRASE(description, 'biggest blockbuster')
GROUP BY genre;
 genre     | count | avg_runtime
-----------+-------+-------------
 adventure |     1 |         127
 comedy    |     1 |          88

See also​