Skip to main content
In addition to basic match, phrase, and term queries, additional advanced query types are exposed as query builder functions. Query builder functions use the @@@ operator. @@@ takes a column on the left-hand side and a query builder function on the right-hand side. It means “find all rows where the column matches the given query.” For example: 
SELECT description, rating, category
FROM mock_items
WHERE description @@@ pdb.regex('key.*rd');

       description        | rating |  category
--------------------------+--------+-------------
 Ergonomic metal keyboard |      4 | Electronics
 Plastic Keyboard         |      4 | Electronics
(2 rows)
This uses the regex query builder function to match all rows where description matches the regex expression key.*rd.

​
When to Use Query Builder Functions

For most use cases, we recommend using the full text search operators instead of query builder functions. However, there are three situations where query builder functions are useful.
  1. If you need an advanced query type, like regex.
  2. If you want to leverage ParadeDB’s support for JSON query syntax.
  3. If you want to force query pushdown into the ParadeDB custom scan. There are some cases where queries that don’t contain a ParadeDB operator can be more efficiently executed by ParadeDB vs. standard Postgres, like TopN or aggregate queries. In this situation, adding an AND ... pdb.all() WHERE clause is semantically equivalent and forces ParadeDB to execute the query. 
-- Top N executed by standard Postgres
SELECT * FROM mock_items
WHERE rating IS NOT NULL
ORDER BY rating
LIMIT 5;

-- Top N executed by ParadeDB
SELECT * FROM mock_items
WHERE rating IS NOT NULL AND id @@@ pdb.all()
ORDER BY rating
LIMIT 5;
⌘I