Advanced Search Queries

​

Overview

The search function that’s generated for your index can accept query objects instead of a query string. Nearly all the query types implemented in Tantivy, our underlying search engine, are exposed to Postgres.

Query objects are composable, allowing for arbitrarily fine-grained queries. A query string itself is parsed into a query object, and can be used anywhere in a query tree with paradedb.parse.

paradedb.parse is implicitly performed if a query string is passed directly to the search function. These two queries are identical:

SELECT * FROM search_idx.search('description:shoes');

SELECT * FROM search_idx.search(query => paradedb.parse('description:shoes'));

​

Query Types

The paradedb schema exposes the following functions to construct query objects.

​

All

This query type indiscriminately matches every document in the index, assigning a uniform score of 1.0 to each.

SELECT * FROM search_idx.search(
    query => paradedb.all()
);

​

Boolean

Boolean queries filter documents based on the logical relationships defined by their subqueries, considering:

• Documents that satisfy all must conditions.
• Documents that satisfy none of the must_not conditions.
• Documents that satisfy at least one condition from either must or should.

SELECT * FROM search_idx.search(
    query => paradedb.boolean(
	    should => ARRAY[
		    paradedb.parse('description:shoes'),
		    paradedb.phrase_prefix(field => 'description', phrases => ARRAY['book']),
		    paradedb.term(field => 'description', value => 'speaker'),
		    paradedb.fuzzy_term(field => 'description', value => 'wolo')
	    ]
    )
);

​

Boost

A boost query wraps around another query to amplify its scoring impact, without altering the set of matched documents.

SELECT * FROM search_idx.search(
	query => paradedb.boost(query => paradedb.all(), boost => 1.5)
);

​

Const Score

Applies a constant score across all documents matched by the underlying query. It can avoid unnecessary score computation on the wrapped query.

SELECT * FROM search_idx.search(
    query => paradedb.const_score(query => paradedb.all(), score => 3.9)
);

​

Disjunction Max

Returns documents that match one or more of the specified subqueries. If a document matches multiple criteria, it receives the highest score from those matches, with a slight increase for any additional matches.

SELECT * FROM search_idx.search(
	query => paradedb.disjunction_max(
		disjuncts => ARRAY[paradedb.parse('description:shoes')]
	)
);

​

Empty

Serves as a placeholder, matching no documents. It’s useful for testing scenarios or specific edge cases.

SELECT * FROM search_idx.search(
	query => paradedb.empty()
);

​

Fuzzy Term

Fuzzy search allows users to obtain search results that approximately match the query term, accommodating minor typos in the input. This enhances the search experience by providing relevant results even when the query is not spelled correctly.

SELECT * FROM search_idx.search(
	query => paradedb.fuzzy_term(field => 'description', value => 'wolo')
);

​

Phrase

Searches for documents containing an exact sequence of words, with slop allowing for some flexibility in term proximity. This query type also requires position indexing.

SELECT * FROM search_idx.search(
	query => paradedb.phrase(
		field => 'description',
		phrases => ARRAY['robot', 'building', 'kit']
	)
);

​

Phrase Prefix

Identifies documents containing a given sequence of words followed by a term prefix, requiring the indexing of positions for the search field.

SELECT * FROM search_idx.search(
	query => paradedb.phrase_prefix(
		field => 'description',
		phrases => ARRAY['har']
	)
);

​

Range

Finds documents containing a term that falls within a specified range of values.

SELECT * FROM search_idx.search(
  query => paradedb.range(
    field => 'rating',
    range => '[1,3)'::int4range
  ),
  stable_sort => true
);

​

Regex

Finds documents containing terms that match a specific regex pattern, enabling pattern-based searching.

SELECT * FROM search_idx.search(
	query => paradedb.regex(
		field => 'description',
		pattern => '(hardcover|plush|leather|running|wireless)'
	)
);

​

Term

Matches documents containing a specified term, with scoring based on term frequency, inverse document frequency, and field normalization. The term value is treated as a token and is not tokenized further. It is matched directly against terms in the index.

SELECT * FROM search_idx.search(
	query => paradedb.term(field => 'description', value => 'shoes')
);

SELECT * FROM search_idx.search(
  query => paradedb.term(
    field => 'created_at',
    value => TIMESTAMP '2023-05-01 09:12:34'
  )
);

​

TermSet

Matches documents containing any term from a specified set, offering flexibility in matching criteria.

SELECT * FROM search_idx.search(
	query => paradedb.term_set(
	    terms => ARRAY[
	        paradedb.term(field => 'description', value => 'shoes'),
	        paradedb.term(field => 'description', value => 'novel')
	    ]
	)
);