Match - ParadeDB

Match queries are the go-to query type for text search in ParadeDB. There are two types of match queries: match disjunction and match conjunction.

Match Disjunction

Match disjunction uses the ||| operator and means “find all documents that contain one or more of the terms tokenized from this text input.” To understand what this looks like in practice, let’s consider the following query:

SELECT description, rating, category
FROM mock_items
WHERE description ||| 'running shoes';

import { search } from "@paradedb/drizzle-paradedb";

await db
  .select({
    description: mockItems.description,
    rating: mockItems.rating,
    category: mockItems.category,
  })
  .from(mockItems)
  .where(search.matchAny(mockItems.description, "running shoes"));

from paradedb import MatchAny, ParadeDB

MockItem.objects.filter(
    description=ParadeDB(MatchAny('running shoes'))
).values('description', 'rating', 'category')

from sqlalchemy import select
from sqlalchemy.orm import Session
from paradedb.sqlalchemy import search

stmt = (
    select(MockItem.description, MockItem.rating, MockItem.category)
    .where(search.match_any(MockItem.description, "running shoes"))
)

with Session(engine) as session:
    session.execute(stmt).all()

MockItem.search(:description)
        .match_any("running shoes")
        .select(:description, :rating, :category)

await dbContext
    .MockItems.Where(item =>
        EF.Functions.MatchAny(item.Description, "running shoes")
    )
    .Select(item => new { item.Description, item.Rating, item.Category })
    .ToListAsync();

This query returns:

     description     | rating | category
---------------------+--------+----------
 Sleek running shoes |      5 | Footwear
 White jogging shoes |      3 | Footwear
 Generic shoes       |      4 | Footwear
(3 rows)

How It Works

Let’s look at what the ||| operator does:

Retrieves the tokenizer configuration of the description column. In this example, let’s assume description uses the unicode tokenizer.
Tokenizes the query string with the same tokenizer. This means running shoes becomes two tokens: running and shoes.
Finds all rows where description contains any one of the tokens, running or shoes.

This is why all results have either running or shoes tokens in description.

Examples

Let’s consider a few more hypothetical documents to see whether they would be returned by match disjunction. These examples assume that the index uses the default tokenizer and token filters, and that the query is running shoes.

Original Text	Tokens	Match	Reason	Related
Sleek running shoes	`sleek` `running` `shoes`	✅	Contains both `running` and `shoes`.
Running shoes sleek	`sleek` `running` `shoes`	✅	Contains both `running` and `shoes`.	Phrase
SLeeK RUNNING ShOeS	`sleek` `running` `shoes`	✅	Contains both `running` and `shoes`.	Lowercasing
Sleek run shoe	`sleek` `run` `shoe`	❌	Contains neither `running` nor `shoes`.	Stemming
Sleke ruining shoez	`sleke` `ruining` `shoez`	❌	Contains neither `running` nor `shoes`.	Fuzzy
White jogging shoes	`white` `jogging` `shoes`	✅	Contains `shoes`.	Match conjunction

Match Conjunction

Suppose we want to find rows that contain both running and shoes. This is where the &&& match conjunction operator comes in. &&& means “find all documents that contain all terms tokenized from this text input.”

SELECT description, rating, category
FROM mock_items
WHERE description &&& 'running shoes';

import { search } from "@paradedb/drizzle-paradedb";

await db
  .select({
    description: mockItems.description,
    rating: mockItems.rating,
    category: mockItems.category,
  })
  .from(mockItems)
  .where(search.matchAll(mockItems.description, "running shoes"));

from paradedb import MatchAll, ParadeDB

MockItem.objects.filter(
    description=ParadeDB(MatchAll('running shoes'))
).values('description', 'rating', 'category')

from sqlalchemy import select
from sqlalchemy.orm import Session
from paradedb.sqlalchemy import search

stmt = (
    select(MockItem.description, MockItem.rating, MockItem.category)
    .where(search.match_all(MockItem.description, "running shoes"))
)

with Session(engine) as session:
    session.execute(stmt).all()

MockItem.search(:description)
        .match_all("running shoes")
        .select(:description, :rating, :category)

await dbContext
    .MockItems.Where(item =>
        EF.Functions.MatchAll(item.Description, "running shoes")
    )
    .Select(item => new { item.Description, item.Rating, item.Category })
    .ToListAsync();

This query returns:

     description     | rating | category
---------------------+--------+----------
 Sleek running shoes |      5 | Footwear
(1 row)

Note that White jogging shoes and Generic shoes are no longer returned because they do not have the token running.

How It Works

Match conjunction works exactly like match disjunction, except for one key distinction. Instead of finding documents containing at least one matching token from the query, it finds documents where all tokens from the query are a match.

Examples

Let’s consider a few more hypothetical documents to see whether they would be returned by match conjunction. These examples assume that the index uses the default tokenizer and token filters, and that the query is running shoes.

Original Text	Tokens	Match	Reason	Related
Sleek running shoes	`sleek` `running` `shoes`	✅	Contains both `running` and `shoes`.
Running shoes sleek	`sleek` `running` `shoes`	✅	Contains both `running` and `shoes`.	Phrase
SLeeK RUNNING ShOeS	`sleek` `running` `shoes`	✅	Contains both `running` and `shoes`.	Lowercasing
Sleek run shoe	`sleek` `run` `shoe`	❌	Does not contain both `running` and `shoes`.	Stemming
Sleke ruining shoez	`sleke` `ruining` `shoez`	❌	Does not contain both `running` and `shoes`.	Fuzzy
White jogging shoes	`white` `jogging` `shoes`	❌	Does not contain both `running` and `shoes`.	Match conjunction

If the query string only contains one token, then ||| and &&& are effectively the same:

-- These two queries produce the same results
SELECT description, rating, category
FROM mock_items
WHERE description ||| 'shoes';

SELECT description, rating, category
FROM mock_items
WHERE description &&& 'shoes';

Using a Custom Tokenizer

By default, the match query automatically tokenizes the query string with the same tokenizer used by the field it’s being searched against. This behavior can be overridden by explicitly casting the query to a different tokenizer.

SELECT description, rating, category
FROM mock_items
WHERE description ||| 'running shoes'::pdb.whitespace;

import { search, tokenizer } from "@paradedb/drizzle-paradedb";

await db
  .select({
    description: mockItems.description,
    rating: mockItems.rating,
    category: mockItems.category,
  })
  .from(mockItems)
  .where(
    search.matchAny(
      mockItems.description,
      search.tokenize("running shoes", tokenizer.whitespace()),
    ),
  );

from paradedb import MatchAny, ParadeDB, Tokenized, Tokenizer

MockItem.objects.filter(
    description=ParadeDB(
        MatchAny(Tokenized('running shoes', Tokenizer.whitespace()))
    )
).values('description', 'rating', 'category')

from sqlalchemy import select
from sqlalchemy.orm import Session
from paradedb.sqlalchemy import search, tokenizer

stmt = (
    select(MockItem.description, MockItem.rating, MockItem.category)
    .where(search.match_any(MockItem.description, "running shoes", tokenizer=tokenizer.whitespace()))
)

with Session(engine) as session:
    session.execute(stmt).all()

MockItem.search(:description)
        .match_any("running shoes", tokenizer: ParadeDB::Tokenizer.whitespace())
        .select(:description, :rating, :category)

await dbContext
    .MockItems.Where(item =>
        EF.Functions.MatchAny(
            item.Description,
            EF.Functions.Tokenize("running shoes", Tokenizer.Whitespace())
        )
    )
    .Select(item => new { item.Description, item.Rating, item.Category })
    .ToListAsync();

Using Pretokenized Text

The match operators also accept text arrays. If a text array is provided, each element of the array is treated as an exact token, which means that no further processing is done.

SELECT description, rating, category
FROM mock_items
WHERE description &&& ARRAY['running', 'shoes'];

import { search } from "@paradedb/drizzle-paradedb";

await db
  .select({
    description: mockItems.description,
    rating: mockItems.rating,
    category: mockItems.category,
  })
  .from(mockItems)
  .where(search.matchAll(mockItems.description, ["running", "shoes"]));

from paradedb import MatchAll, ParadeDB

MockItem.objects.filter(
    description=ParadeDB(MatchAll('running', 'shoes'))
).values('description', 'rating', 'category')

from sqlalchemy import select
from sqlalchemy.orm import Session
from paradedb.sqlalchemy import search

stmt = (
    select(MockItem.description, MockItem.rating, MockItem.category)
    .where(search.match_all(MockItem.description, "running", "shoes"))
)

with Session(engine) as session:
    session.execute(stmt).all()

MockItem.search(:description)
        .match_all("running shoes")
        .select(:description, :rating, :category)

await dbContext
    .MockItems.Where(item =>
        EF.Functions.MatchAll(item.Description, new[] { "running", "shoes" })
    )
    .Select(item => new { item.Description, item.Rating, item.Category })
    .ToListAsync();

​Match Disjunction

​How It Works

​Examples

​Match Conjunction

​How It Works

​Examples

​Using a Custom Tokenizer

​Using Pretokenized Text

Match Disjunction

How It Works

Examples

Match Conjunction

How It Works

Examples

Using a Custom Tokenizer

Using Pretokenized Text