Documentation Index
Fetch the complete documentation index at: https://docs.paradedb.com/llms.txt
Use this file to discover all available pages before exploring further.
Now that your environment is configured, select the codetab for your tool and run some queries.
Match Query
We’re now ready to execute a basic text search query. We’ll look for matches where description matches running shoes where rating is greater than 2.
SELECT description, rating, category
FROM mock_items
WHERE description ||| 'running shoes' AND rating > 2
ORDER BY rating
LIMIT 5;
description | rating | category
---------------------+--------+----------
White jogging shoes | 3 | Footwear
Generic shoes | 4 | Footwear
Sleek running shoes | 5 | Footwear
(3 rows)
||| is ParadeDB’s custom match disjunction operator, which means “find me all documents containing
running OR shoes.
If we want all documents containing running AND shoes, we can use ParadeDB’s &&& match conjunction operator.
SELECT description, rating, category
FROM mock_items
WHERE description &&& 'running shoes' AND rating > 2
ORDER BY rating
LIMIT 5;
description | rating | category
---------------------+--------+----------
Sleek running shoes | 5 | Footwear
(1 row)
BM25 Scoring
Next, let’s add BM25 scoring to the results, which sorts matches by relevance. To do this, we’ll use pdb.score.
SELECT description, pdb.score(id)
FROM mock_items
WHERE description ||| 'running shoes' AND rating > 2
ORDER BY score DESC
LIMIT 5;
description | score
---------------------+-----------
Sleek running shoes | 6.817111
Generic shoes | 3.8772602
White jogging shoes | 3.4849067
(3 rows)
Highlighting
Finally, let’s also highlight the relevant portions of the documents that were matched.
To do this, we’ll use pdb.snippet.
SELECT description, pdb.snippet(description), pdb.score(id)
FROM mock_items
WHERE description ||| 'running shoes' AND rating > 2
ORDER BY score DESC
LIMIT 5;
description | snippet | score
---------------------+-----------------------------------+-----------
Sleek running shoes | Sleek <b>running</b> <b>shoes</b> | 6.817111
Generic shoes | Generic <b>shoes</b> | 3.8772602
White jogging shoes | White jogging <b>shoes</b> | 3.4849067
(3 rows)
Top K
ParadeDB is highly optimized for quickly returning the Top K results out of the index. In SQL, this means queries that contain an ORDER BY...LIMIT:
SELECT description, rating, category
FROM mock_items
WHERE description ||| 'running shoes'
ORDER BY rating
LIMIT 5;
description | rating | category
---------------------+--------+----------
White jogging shoes | 3 | Footwear
Generic shoes | 4 | Footwear
Sleek running shoes | 5 | Footwear
(3 rows)
Facets
Faceted queries allow a single query to return both the Top K results and an aggregate value,
which is more CPU-efficient than issuing two separate queries.
For example, the following query returns the top 3 results as well as the total number of results matched.
SELECT
description, rating, category,
pdb.agg('{"value_count": {"field": "id"}}') OVER ()
FROM mock_items
WHERE description ||| 'running shoes'
ORDER BY rating
LIMIT 5;
description | rating | category | agg
---------------------+--------+----------+----------------
White jogging shoes | 3 | Footwear | {"value": 3.0}
Generic shoes | 4 | Footwear | {"value": 3.0}
Sleek running shoes | 5 | Footwear | {"value": 3.0}
(3 rows)
