Repository navigation
Description
Rango on PostgreSQL: how far to follow MongoDB query semantics
Rango promises that code written against the shared collection API returns the same documents on PostgreSQL and on MongoDB. The reviews found nine gaps. This concept proposes a rule for which gaps to close and which to document, a SQL translation for each, and a release plan.
How this was checked. Every SQL statement below ran on PostgreSQL 16.11. The expected results come from MongoDB 8.2.3 with the same seven test documents (a to g). The 1.1.x SQL was generated by the released FilterBuilder, and the in-progress state by the dotted-path-filters branch. Timings come from a 300k-row collection (138 MB heap) with the btree indexes that createIndex builds, on a warm cache with single runs, so read them as orders of magnitude. E stands for the path expression from FilterBuilder::fieldExpression(), e.g. data->'age' or data->'labels'->'color'.
Decisions
- Whole-value list semantics are the permanent, documented exception. List queries use
$elemMatch,$alland$size. - Result changes toward MongoDB (gaps 1, 4, 5 and 6, including
['field' => null]matching missing fields) are bugfixes for 1.2.0, with a clear release note for the null case. - Path expressions: a single top-level key stays
data->'key', so indexes created with 1.1.0 keep working. Dotted paths and keys made of digits usedata #> '{a,b}', and non-negative numeric segments address list positions. This ships with the dotted-path fix in 1.1.x. - The ODM rewrites scalar filters on list properties into
$elemMatch(patchlevel/odm, follow-up after #29).
Plan
- 1.1.x: dotted paths, positional paths, negations with missing fields,
$size/$mod/$elemMatchtype guards,distinctand aggregation paths,$matchalias,listIndexes, docs on list matching - 1.1.x: update builder fixes (placeholder splicing, upsert rewrite, path literal quoting, dotted upsert keys)
- 1.2.0: equality via
=together with null and missing (gaps 1 and 5) - 1.2.0: comparisons within a type bracket (gap 4),
$regexonly on strings (gap 6), operator-only$elemMatch(gap 9) - 1.2.0: "Differences from MongoDB" docs page
- 2.0.0: sort order with missing fields first (gap 7), with an upgrade note to recreate indexes
- still open: collation check on connect, opt-in GIN index for list fields (questions 5 and 6 below)
Principle
For every documented operator, Rango returns the same documents as MongoDB as long as the value at the path is a scalar or an object. It does that with SQL the btree index from
createIndexcan use for both filtering and ordering. Lists are matched through explicit operators. A query never fails because one document holds an unexpected type. Every other difference is listed on one docs page.
In practice:
- Scalars and objects get parity. That covers equality, comparisons, null and missing, regex and positional paths. All of it fits the existing btree index, so there is no reason to differ.
- Lists are matched through explicit operators. An implicit filter (
['tags' => 'php'],$in,$gt) compares a list as a whole value. List semantics come from$elemMatch,$alland$size, which give the same result on both backends. This is the one deliberate exception. MongoDB's implicit traversal turns every condition into anORof a scalar branch and a list branch, and PostgreSQL can't walk an index in order through thatOR. With it, a keyset page goes from 0.06 ms to 59 ms (gap 2). - No runtime errors. Where MongoDB simply doesn't match, Rango must not raise an SQL error.
- Documented differences go on a new page, "Differences from MongoDB".
Full parity isn't worth it in three kinds of case. Some would cost ordered index scans on scalar fields (implicit list traversal). Some need machinery PostgreSQL doesn't have (per-element unique indexes). Others only matter for fields that mix types (cross-type sort order).
Verification at a glance
| Filter | MongoDB | 1.1.x | Proposed |
|---|---|---|---|
obj: {$eq: {x: 1}} |
a, d | a, b, c | a |
age: {$lt: 30} |
a, g | a, b, d | a |
name: {$lt: 5} |
none | a, b, f, g | none |
v: {$gt: false} |
c | c, d, f | c |
age: null |
b, e | b | b, e |
age: {$ne: null} |
a, c, d, f, g | a, c, d, e, f, g | a, c, d, f, g |
obj: {$regex: 'x'} |
none | a, b, c, d, f | none |
'tags.0': 'pg' / 'tags.1': 'pg' |
none / a | a / none | none / a |
tags: 'php' |
a, c, f | c | c |
tags: {$elemMatch: {$eq: 'php'}} |
a, f | exception | a, f |
tags: {$size: 1} |
b, d, f | SQL error | b, d, f |
Wherever "Proposed" still differs from MongoDB, the missing document holds a list at that path. The explicit $elemMatch spelling closes the gap.
The gaps
1. Equality
MongoDB ($eq) compares the whole value exactly. On a list field it also matches a single element (see gap 2). Rango 1.1.x uses containment, data @> '{"color":"red"}' (and on the dotted branch, data->'labels' @> '{"color":"red"}'). Containment matches supersets, so {$eq: ['red']} matches ['red','blue'] and {$eq: {x: 1}} matches {x: 1, y: 2}. It also never uses the btree index.
Proposed: E = '"red"'::jsonb. $ne becomes (E = v) IS NOT TRUE, which keeps documents with missing fields, as the dotted branch already does. $in stays E IN (...).
Index and performance: the existing btree on E serves the query, including ordered scans. Equality on an indexed field went from 43 ms (parallel seq scan) to 0.3 ms.
BC: results only shrink, and every row that drops out was a containment false positive. One exception stays documented: jsonb normalises key order, so {y: 2, x: 1} equals {x: 1, y: 2}, while MongoDB compares objects field by field.
2. A single value against a list
MongoDB (Query an Array, multikey indexes) applies every condition to the list and to each of its elements. ['tags' => 'php'] matches ['php','pg'], $gt matches when any element is greater, and $ne/$nin require that no element matches.
Rango 1.1.x compares the whole list. docs/querying.md claims the opposite ("documents whose tags array contains php"), but that's wrong: data @> '{"tags":"php"}' doesn't match {"tags":["php","pg"]}, because PostgreSQL's primitive-in-array exception only applies at the top level. Ranges are worse. ['scores' => ['$gt' => 1000]] becomes E > '1000', and every non-empty list sorts above every number.
Parity SQL (verified, not recommended as the default): add a list branch on the same btree. jsonb orders [] < null < strings < numbers < booleans < lists < objects, so E > 'true' AND E < '{}' selects exactly the non-empty lists:
E = '"php"' OR (E > 'true' AND E < '{}' AND E @> '"php"')With EXISTS (... jsonb_array_elements(E) ...) for the per-element checks, this matched MongoDB on every list case for $eq, $in, $ne, $nin, $gt, $lt, $regex, $type and $all.
Index and performance: plain filtering stays on the index (a BitmapOr, 0.3 to 0.4 ms). Ordering doesn't survive the OR:
| Query (300k rows, limit 20) | without list branch | with list branch |
|---|---|---|
status = 'paid' order by createdAt desc, composite index only |
0.07 ms | 55 ms |
same, plus a single-field index on createdAt |
0.07 ms | 0.09 ms |
keyset: createdAt > 150000 order by createdAt |
0.06 ms | 59 ms |
Keyset pagination by position is exactly what an event store does. List fields would also need GIN to get index support. A GIN on E (jsonb_path_ops) took 2 MB against 14 MB for the btree and added 23% insert time. A GIN on all of data took 36 MB and added 143%.
Proposed: no implicit list traversal. Keep whole-value semantics, fix the docs, and document $elemMatch as the portable spelling (this needs gap 9). These all matched MongoDB exactly:
['tags' => ['$elemMatch' => ['$eq' => 'php']]]['scores' => ['$elemMatch' => ['$gt' => 1000]]]['tags' => ['$not' => ['$elemMatch' => ['$eq' => 'php']]]]
With gap 4, list values also stop matching scalar comparisons, so what remains is a missing match rather than a wrong one. The ODM knows which properties are lists and could rewrite scalar filters on them into $elemMatch for both backends.
BC: none. This is today's behaviour, now with correct docs.
3. Paths into lists
MongoDB (Query an Array of Embedded Documents) resolves items.score across every object in items. It reads tags.1 as element 1 of a list, or as key "1" of an object.
Rango: the dotted branch builds data->'items'->'score', which is NULL when items is a list. Positional segments go through json_encode([$lastKey => $value]). So ['tags.0' => 'pg'] becomes data->'tags' @> '["pg"]', which matches any list containing pg. ['tags.1' => 'pg'] becomes @> '{"1":"pg"}', which never matches a list.
Proposed, positional paths (3a): a numeric segment compiles to #>, which takes the element index on a list and the key on an object, e.g. (data->'tags' #> '{1}') = '"pg"'. This was verified against MongoDB for both shapes, and an expression index on it is used. As in MongoDB, there's no list traversal after a positional segment (x.0 = 1 doesn't match x: [[1, 2], [3]]).
Proposed, traversal (3b): a documented exception, with the portable spelling ['items' => ['$elemMatch' => ['score' => ['$gt' => 10]]]]. Parity SQL does exist and matches MongoDB: jsonb_path_exists(data, 'lax $."items"."score" ? (@ > $v)', '{"v": 10}'). Lax mode unwraps exactly one list level, as MongoDB does. The catch is that it needs a third OR branch with a prefilter:
- With
E IS NULLas the prefilter, a sparse nested object field drops from 0.06 ms to 31 ms. - A hidden partial index
WHERE jsonb_typeof(data->'items') = 'array'brings it back to 0.08 ms. But thenlistIndexes,dropIndexand the ODM'supdateIndexes(dropUnknown: true)all have to know about these companion indexes. - It also loses ordered scans, as in gap 2.
Timing: the path expression becomes part of every index definition. Decide on #> for numeric segments before the dotted-path fix ships, otherwise indexes on positional paths change twice.
BC: the positional fix only changes wrong results.
4. Comparisons across JSON types
MongoDB ($gt, comparison order) only compares within a type bracket: numbers with numbers, strings with strings. Rango 1.1.x uses jsonb ordering. As a result, age < 30 matches null, strings and [], name < 5 matches every string, and v > false matches lists and objects.
Proposed: limit the range to the operand's bracket on the same btree, and add a type check only where no bound exists:
| Operand | SQL |
|---|---|
number, $gt/$gte |
E > v AND E < 'false' |
number, $lt/$lte |
E > 'null' AND E < v AND jsonb_typeof(E) = 'number' |
string, $lt/$lte |
E > 'null' AND E < v |
string, $gt/$gte |
E > v AND jsonb_typeof(E) = 'string' |
| boolean | E > 'false' AND E <= 'true', and the mirror case |
| null | $gte/$lte act like $eq: null, $gt/$lt are FALSE |
Object and list operands are rare. For those, keep the jsonb order and document it.
Index and performance: the bounds are index conditions, so ordered scans stay (keyset: 0.056 ms). The > 'null' bound also helps nullable fields. age < 2 with 33% nulls went from a 32 ms seq scan to an 8.8 ms bitmap scan.
BC: only cross-type matches drop out.
5. Null and missing
MongoDB (Query for Null or Missing Fields): {f: null} matches both null and missing, $ne: null matches neither, and $in: [null] behaves like $eq: null. Rango 1.1.x: data @> '{"age":null}' only finds an explicit null, and $ne: null also returns documents where the field is missing.
Proposed:
$eq: nullbecomesE IS NULL OR E = 'null'.$ne: nullbecomesE IS NOT NULL AND E <> 'null'.$inwithnulladdsOR E IS NULL.$existsstays as on the dotted branch (E IS [NOT] NULL).
On a dotted path, the SQL NULL also covers a missing parent object.
Index and performance: both branches are btree conditions (a BitmapOr). Sorting on top of a $eq: null filter can't use a composite index for ordering, as in gap 2's table. A single-field index on the sort field avoids that.
BC: this one is visible. ['deletedAt' => null] starts returning documents that have no deletedAt. It needs a changelog entry.
6. $regex
MongoDB ($regex) matches strings only, with PCRE, and . doesn't match a newline unless the s flag is set. Rango 1.1.x runs data->>'v' ~ pattern on the text form of any type, so ^1 matches the number 1 and x matches the object {"x":1}. On top of that, PostgreSQL's regex flavour (ARE) lets . match newlines by default.
Proposed: jsonb_typeof(E) = 'string' AND (data->>'v') ~ '(?p)^1'. Map the flags:
| MongoDB flags | ARE flag |
|---|---|
| none | p |
s |
none |
m |
n |
m and s |
w |
i, x |
unchanged |
Verified: 'a\nb' ~ 'a.b' is true in plain ARE, and false with (?p) and in MongoDB.
Index: none, before or after. A jsonb btree can't serve ~. PCRE-only syntax (possessive quantifiers, named groups) goes on the differences page.
BC: non-string matches drop out, and newline handling follows MongoDB.
7. Sort order
MongoDB (cursor.sort) sorts ascending as [] < missing = null < numbers < strings < objects < booleans. A list sorts by its smallest element (its largest when descending), and strings compare in binary order. Rango 1.1.x: ORDER BY E ASC gives [] < null < strings < numbers < booleans < lists < objects < missing. Both orders were reproduced on a nine-document set.
Proposed: only fix where missing values go. Use ASC NULLS FIRST and DESC NULLS LAST, and have createIndex build (E) ASC NULLS FIRST or (E) DESC NULLS LAST, so one index serves both directions (verified, 0.1 ms each way). Aggregation $sort builds its own data->'f' in 1.1.x and has to follow the same rule. The cross-type order and min/max sorting of lists stay documented, because they would need a type-rank column in every index.
Collation: jsonb compares strings with the database collation. In an ICU en-US database, "a" < "B" is true; in MongoDB (binary) it's false. This affects sorting and string $lt/$gt. The docs should recommend the C collation.
Index and BC: existing indexes (ASC NULLS LAST) can't serve the new ORDER BY. In the test, a sort with a limit went from 0.06 ms to 47 ms. createIndex uses IF NOT EXISTS by name and the ODM reconciles by name, so existing installs keep the old index. This needs a major version with an upgrade step.
8. Unique indexes on list fields
MongoDB (unique indexes) enforces uniqueness per element across documents. It stores a missing field as null, so only one document may lack the field. Both were verified: ['b','c'] after ['a','b'] is rejected, and so is a second document without tags.
Rango builds a btree on the whole value. Both inserts succeed, any number of documents may lack the field, and an explicit null is allowed once.
Proposed: no change, document it. Per-element uniqueness would need a trigger-maintained side table, since jsonb has no operator class for an exclusion constraint. Making missing values collide (NULLS NOT DISTINCT on COALESCE(E, 'null')) would reject writes that work today. Most MongoDB users avoid that behaviour with sparse or partial indexes anyway.
9. $elemMatch with operators, $expr, and runtime errors
MongoDB ($elemMatch) accepts ['scores' => ['$elemMatch' => ['$gte' => 80, '$lt' => 90]]]. Rango throws Operator "$gte" is not supported at the top level.
Worse, $elemMatch, $size and $mod raise SQL errors as soon as one document holds another type at the path:
cannot extract elements from an objectcannot get array length of a scalarcannot cast jsonb null to type numeric
A single odd document breaks the whole query.
Proposed:
-- $elemMatch, operator-only or with fields; predicates as in gap 4, applied to e
EXISTS (SELECT 1 FROM jsonb_array_elements(
CASE WHEN jsonb_typeof(E) = 'array' THEN E ELSE '[]' END) e
WHERE e > '1000' AND e < 'false')
-- $size, $mod
CASE WHEN jsonb_typeof(E) = 'array' THEN jsonb_array_length(E) END = 1
CASE WHEN jsonb_typeof(E) = 'number' THEN MOD(E::numeric, 2) END = 1The CASE matters, because PostgreSQL doesn't promise left-to-right evaluation of AND. The element predicates are the gap 4 predicates with e in place of E, so one compiler serves both. All three were verified against MongoDB. Following the principle, $mod on a list doesn't traverse it.
$expr depends on the ExpressionBuilder in PR #6. MongoDB's aggregation comparisons ($cmp) don't use type brackets. They compare across types in BSON order and don't traverse lists. PR #6 uses jsonb > and IS NOT DISTINCT FROM. That gives the same result for operands of the same type. The order between strings and numbers is reversed, which should be documented.
Index: none needed. BC: additive, plus bugfixes that turn errors into results.
Release plan
| Release | Content | Why there |
|---|---|---|
| 1.1.x patch | the dotted-path branch; CASE guards for $elemMatch, $size, $mod; remove the "tags contains" claim from querying.md |
pure bugfixes, no index change |
| 1.2.0 | gaps 1, 5, 4, 6, 3a; gap 9 (operator-only $elemMatch, and $expr if PR #6 lands); aggregation $sort via fieldExpression(); the "Differences from MongoDB" page |
results move toward MongoDB, all served by the existing btree indexes, no DDL |
| 2.0.0 | gap 7 (missing first, NULLS FIRST/LAST in createIndex) plus an upgrade note to recreate indexes |
changes index definitions |
| documented | gaps 2 and 3b (implicit list traversal), gap 8, cross-type and list sort order, object key order, regex dialect, collation | cost exceeds benefit |
Dependencies:
- Gaps 1 and 5 share the equality builder and ship together.
$ne,$ninand$notare alwaysIS NOT TRUEof the positive form. - Gap 4 comes before gap 9, because operator-only
$elemMatchreuses the gap 4 predicates on the element. - Gap 1 settles gap 2. With
=and the gap 4 bounds, equality and ranges get ordered index scans. List parity would take those away again, and for ranges it would even be slower than 1.1.x (keyset: 0.06 ms to 59 ms). So list parity is a major-version decision whenever it comes, and it gets more expensive the longer applications rely on the fast plans. I'd decide it now: as the documented exception. - Gap 3a fixes the shape of index expressions, so it has to be decided before the dotted-path branch is released.
- Gap 7 changes
createIndexand needs existing indexes rebuilt. The ODM'supdateIndexes()only compares names, so it won't notice the change.
Open questions
Questions 1 to 4 are answered in the decisions above.
- Are result changes toward MongoDB (gaps 1, 4, 5 and 6) bugfixes that may go into 1.2.0, or BC breaks that wait for 2.0? The plan assumes bugfixes.
- Do you accept whole-value list semantics as the permanent exception (my recommendation)? Or do you want MongoDB list parity in 2.0, at the cost of ordered index scans and GIN indexes on list fields?
- Should the ODM rewrite scalar filters on list-typed properties into
$elemMatch? It knows the mapping, and ODM users would get identical results on both backends. - Is it acceptable that
['field' => null]starts matching missing fields in a minor release? It's the most visible change, for soft-delete style filters in particular. - Collation: should we only document the
Ccollation, or also checkdatcollateon connect and warn? - Do we want an opt-in GIN index for list fields, e.g. a Rango-only
createIndexoption? It would serve equality inside$elemMatchand$all, but it isn't portable to the MongoDB driver.
Found while reviewing patchlevel/odm#29. Related: #9.
Activity