Skip to main content
The more like this (MLT) query finds documents that are “like” another document. To use this query, pass the key field value of the input document to pdb.more_like_this. For instance, the following query finds documents that are “like” a document with an id of 3: 
SELECT id, description, rating, category
FROM mock_items
WHERE id @@@ pdb.more_like_this(3)
ORDER BY id;
Expected Response
 id |     description      | rating | category
----+----------------------+--------+----------
  3 | Sleek running shoes  |      5 | Footwear
  4 | White jogging shoes  |      3 | Footwear
  5 | Generic shoes        |      4 | Footwear
 13 | Sturdy hiking boots  |      4 | Footwear
 23 | Comfortable slippers |      3 | Footwear
 33 | Winter woolen socks  |      5 | Footwear
(6 rows)
In the output above, notice that documents matching any of the indexed fields, description, rating, and category, were returned. This is because, by default, all fields present in the index are considered for matching.
The only exception is JSON fields, which are not yet supported and are ignored by the more like this query.
To find only documents that match on specific fields, provide an array of field names as the second argument: 
SELECT id, description, rating, category
FROM mock_items
WHERE id @@@ pdb.more_like_this(3, ARRAY['description'])
ORDER BY id;
Expected Response
 id |     description     | rating | category
----+---------------------+--------+----------
  3 | Sleek running shoes |      5 | Footwear
  4 | White jogging shoes |      3 | Footwear
  5 | Generic shoes       |      4 | Footwear
(3 rows)
Because JSON fields are not yet supported for MLT, an error will be returned if a JSON field is passed into the array.

​
How It Works

Let’s look at how the MLT query works under the hood:
  1. Stored values for the input document’s fields are retrieved. If they are text fields, they are tokenized and filtered in the same way as the field was during index creation.
  2. A set of representative terms is created from the input document. For example, in the statement above, these terms would be sleek, running, and shoes for the description field; 5 for the rating field; footwear for the category field.
  3. Documents with at least one term match across any of the fields are considered a match.

​
Using a Custom Input Document

In addition to providing a key field value, a custom document can also be provided as JSON. The JSON keys are field names and must correspond to field names in the index. 
SELECT id, description, rating, category
FROM mock_items
WHERE id @@@ pdb.more_like_this('{"description": "Sleek running shoes", "category": "footwear"}')
ORDER BY id;

​
Configuration Options

​
Term Frequency

min_term_frequency excludes terms that appear fewer than a certain number of times in the input document, while max_term_frequency excludes terms that appear more than that many times. By default, no terms are excluded based on term frequency. For instance, the following query returns no results because no term appears twice in the input document. 
SELECT id, description, rating, category
FROM mock_items
WHERE id @@@ pdb.more_like_this(3, min_term_frequency => 2)
ORDER BY id;

​
Document Frequency

min_doc_frequency excludes terms that appear in fewer than a certain number of documents across the entire index, while max_doc_frequency excludes terms that appear in more than that many documents. By default, no terms are excluded based on document frequency. 
SELECT id, description, rating, category
FROM mock_items
WHERE id @@@ pdb.more_like_this(3, min_doc_frequency => 3)
ORDER BY id;

​
Max Query Terms

By default, only the top 25 terms across all fields are considered for matching. Terms are scored using a combination of inverse document frequency and term frequency (TF-IDF) — this means that terms that appear frequently in the input document and are rare across the index score the highest. This can be configured with max_query_terms: 
SELECT id, description, rating, category
FROM mock_items
WHERE id @@@ pdb.more_like_this(3, max_query_terms => 10)
ORDER BY id;

​
Term Length

min_word_length and max_word_length can be used to exclude terms that are too short or too long, respectively. By default, no terms are excluded based on length. 
SELECT id, description, rating, category
FROM mock_items
WHERE id @@@ pdb.more_like_this(3, min_word_length => 5)
ORDER BY id;

​
Custom Stopwords

To exclude terms from being considered, provide a text array to stopwords: 
SELECT id, description, rating, category
FROM mock_items
WHERE id @@@ pdb.more_like_this(3, stopwords => ARRAY['the', 'a'])
ORDER BY id;
⌘I