Why Metadata Filter Syntax Doesn't Transfer Between Vector Databases

Filter languages look like dialects of each other. They are not. The operator sets differ, two operators with the same name mean different things, and at least one common filter intent cannot be expressed at all in some of them.

The same filter in five syntaxes

Take one intent: documents published in 2024 or later, in the handbook collection, excluding drafts. Here is that intent in five filter languages, each taken from the vendor’s own filtering reference.

# Pinecone — MongoDB-style operator objects, JSON
{"$and": [{"year": {"$gte": 2024}},
          {"collection": {"$eq": "handbook"}},
          {"status": {"$ne": "draft"}}]}

# Qdrant — clause lists, condition objects keyed by field
{"must": [{"key": "year", "range": {"gte": 2024}},
          {"key": "collection", "match": {"value": "handbook"}}],
 "must_not": [{"key": "status", "match": {"value": "draft"}}]}

# Chroma — operator objects, same family as Pinecone
{"$and": [{"year": {"$gte": 2024}},
          {"collection": "handbook"},
          {"status": {"$nin": ["draft"]}}]}

# Milvus — a boolean expression string
'year >= 2024 && collection == "handbook" && status != "draft"'

# pgvector — it is just SQL
WHERE year >= 2024 AND collection = 'handbook' AND status <> 'draft'

Those five are genuinely equivalent, which is what makes the exercise misleading: a translation layer written against this example will look finished. The differences begin one step past it.

The operator sets themselves are small and worth knowing exactly. Pinecone’s indexing documentation lists eleven: $eq, $ne, $gt, $gte, $lt, $lte, $in, $nin, $exists, $and and $or, with the last two permitted only at the top level of the expression. Qdrant’s filtering reference instead combines clauses with must, should and must_not and offers condition types including match, range, values_count, is_empty, is_null, has_id and nested, plus geo conditions. Weaviate’s GraphQL filter is a third shape again: a path array, an operator name such as Equal, GreaterThan, Like, ContainsAny, ContainsAll, ContainsNone or IsNull, and a typed value key such as valueInt or valueText, per its GraphQL filters documentation.

Membership operators that look identical

This is the mistranslation most likely to survive review, because both sides read as “is it in the list”.

$in asks whether a scalar field’s value appears in a list you supply. $contains — and Weaviate’s ContainsAny — asks whether a list-valued field contains a value you supply. The argument and the field swap roles. Chroma documents both: $in and $nin among its inclusion operators, $contains and $not_contains as array operators.

# field is a scalar; list is the argument
{"status": {"$in": ["published", "archived"]}}

# field is a list; scalar is the argument
{"tags": {"$contains": "handbook"}}

Now put Pinecone on the other side. Pinecone metadata accepts a list of strings as a value type, but its documented operators are the eleven above, and $contains is not among them. What Pinecone does instead is treat $in against a list-valued field as matching if any element matches — so the intent survives, expressed by a differently-shaped expression. That is the good case. The bad case is a translator that maps $contains to $in mechanically and swaps the operands, producing a filter that is syntactically valid and returns the wrong set.

The same trap sits under “contains all”. Weaviate has ContainsAll and Milvus has array_contains_all. In an operator set without one, a conjunction of single-element membership tests is the workaround — $and of several $in clauses — and it works, but if the target restricts $and to the top level, you cannot nest it inside an $or. Two portable-looking restrictions compose into an impossible filter.

Substring matching has no portable form

“Where the title starts with” or “where the body mentions” is where translation stops being possible.

• Milvus supports like with a pattern such as VARCHAR like "prefix%", per its boolean expression reference.
• Weaviate has a Like operator in the same list as Equal.
• Chroma keeps document text matching in a separate argument altogether: where_document, with $contains, applied alongside the metadata where.
• pgvector is SQL, so LIKE, ILIKE and full-text search are all available and all indexable.
• Pinecone’s operator list has no pattern or substring operator at all.

If your source filter has a prefix match in it and your destination has no equivalent, there is no clever encoding that fixes this at query time. The fix is at write time: materialise the predicate as a stored field. A title_prefix field holding the first token, a path_segments list holding each ancestor directory, a doc_type enum derived from a filename pattern. You are trading a flexible query for a fixed one, which means new prefixes need a backfill — but a backfill is a scheduled job, and a filter you cannot express is a feature you cannot ship.

Nested fields, absence and null

Pinecone’s documentation is explicit that metadata must be a flat JSON object, that nested objects are not supported, that keys may not begin with $, and that null values are unsupported. Qdrant, by contrast, stores arbitrary JSON payloads and has a dedicated nested condition for filtering inside arrays of objects, plus is_empty and is_null as distinct conditions.

Flattening in one direction is mechanical: author.name becomes a key called author_name, and you pick a separator that cannot appear in a field name. What is not mechanical is the semantics of arrays of objects. If a chunk has three authors each with a name and a role, flattening gives you two parallel lists and loses the pairing — a filter for “an author named Kim who is an editor” becomes “an author named Kim and some author who is an editor”. Those are different sets, and the difference is invisible until a document has two authors.

Absence is the other quiet one. Where nulls are unsupported, a field you did not set and a field you set to null are the same state, and $exists is the only way to ask about it. Where nulls are a value, “missing” and “present but null” are two states with two conditions. A filter written against three-state logic and translated into two-state logic will change its answer for exactly the records you were being careful about.

Pre-filter, post-filter and the missing rows

Syntax is only half of it. When the filter is applied relative to the approximate index scan decides whether a valid filter returns a useful number of rows.

pgvector states the position plainly: with approximate indexes, filtering is applied after the index is scanned. Ask for the ten nearest neighbours with a filter matching one row in a thousand, and the index hands back its candidates, the filter removes nearly all of them, and you get two rows. The project’s README documents iterative index scans, added in version 0.8.0 and controlled by hnsw.iterative_scan and ivfflat.iterative_scan, with strict_order and relaxed_order modes, precisely to keep scanning until enough filtered results exist.

Databases that integrate the filter into the graph traversal do not have this failure, but they have a different one: a filter selective enough can make the traversal much slower, because the walk keeps hitting excluded nodes. Either way, the observable is the same and you should measure it deliberately — issue the same query with filters of increasing selectivity and record both the returned row count and the latency. A translation that is semantically perfect and returns three rows where the old one returned ten is still a regression, and it will be reported to you as a search quality problem rather than a filtering one.

Writing a filter representation you can port

If you expect to move again — or to run two stores during a migration — stop writing vendor filter objects in application code. Define a small internal representation and compile it per backend.

Filter =
  | {"field": str, "op": "eq"|"ne"|"lt"|"lte"|"gt"|"gte", "value": scalar}
  | {"field": str, "op": "in"|"nin", "values": [scalar]}     # scalar field
  | {"field": str, "op": "has_any"|"has_all", "values": [scalar]}  # list field
  | {"field": str, "op": "exists"}
  | {"all": [Filter]} | {"any": [Filter]} | {"none": [Filter]}

Two rules make this worth the effort. Keep in and has_any as separate operators even though several backends spell them the same way, because that is the distinction that gets lost. And make the compiler raise on an intent the backend cannot express rather than approximating it — an unsupported prefix match should fail at build time in a test, not degrade to a full scan or an over-broad match at runtime. A compiler that throws is how you find out which filters need a materialised field, on the day you choose the backend rather than the day a customer notices.