Skip to main content

Edit this page

Match Several Terms

Find rows that contain at least N of a set of M values, with the floor as a knob you turn. Reach for this "N of M" filter when a candidate needs most of a required skill set, a product needs most of a feature list or a document needs most of a keyword set. If you come from Elastic it is the terms_set query, and ts_any is the function underneath.

Six candidates each list a handful of skills in a VARCHAR[] array. An array column indexes every element as an exact keyword on its own, so there is no dictionary to declare.

Schema and sample data
Query
CREATE TABLE candidates (    id INTEGER PRIMARY KEY,    name VARCHAR NOT NULL,    skills VARCHAR[] NOT NULL);
INSERT INTO candidates VALUES(1, 'Alice', ['java', 'sql', 'rust']),(2, 'Bob',   ['java', 'sql', 'python']),(3, 'Carol', ['java', 'rust', 'go']),(4, 'Dave',  ['sql', 'python', 'go']),(5, 'Erin',  ['java', 'sql', 'rust', 'go']),(6, 'Frank', ['python', 'go']);
CREATE INDEX candidates_idx ON candidates    USING inverted (id, name, skills);
VACUUM (REFRESH_TABLE) candidates;

Require every skill​

Pass the list length as the second argument to ts_any and every alternative must match. Here all three skills are required, so only candidates whose skill set covers java, sql and rust come back:

Query
SELECT id, name, skillsFROM candidates_idxWHERE skills @@ ts_any(['java', 'sql', 'rust'], 3)ORDER BY id;
Result
 id | name  | skills----+-------+--------------------  1 | Alice | {java,sql,rust}  5 | Erin  | {java,sql,rust,go}

Relax the floor to N of M​

Lower the second argument to demand fewer of the alternatives. Dropping the floor to 2 keeps everyone who has any two of the three skills, which pulls in Bob and Carol alongside the full matches:

Query
SELECT id, name, skillsFROM candidates_idxWHERE skills @@ ts_any(['java', 'sql', 'rust'], 2)ORDER BY id;
Result
 id | name  | skills----+-------+--------------------  1 | Alice | {java,sql,rust}  2 | Bob   | {java,sql,python}  3 | Carol | {java,rust,go}  5 | Erin  | {java,sql,rust,go}

Same filter as a boolean helper​

has_any_tokens is sugar for the same query without the @@ operator or an explicit list of sub-queries. It takes the column, the candidate values and the same minimum-match count, returning the identical rows:

Query
SELECT id, name, skillsFROM candidates_idxWHERE has_any_tokens(skills, ['java', 'sql', 'rust'], 2)ORDER BY id;
Result
 id | name  | skills----+-------+--------------------  1 | Alice | {java,sql,rust}  2 | Bob   | {java,sql,python}  3 | Carol | {java,rust,go}  5 | Erin  | {java,sql,rust,go}

See also​