Choosing the Right Query Language: SQL++ vs. Mongo

When you’re building apps, the query language you work with makes a big difference. It affects everything from performance to how quickly your team can ship features. In this post, I’ve asked an LLM to create a list of 21 ranked criteria that are most valuable when deciding which query language to use. We’ll walk through each one and show how SQL++, used by Couchbase, and Mongo’s Query API (previously known as MQL) languages stack up.

Whether you’re evaluating databases for a new project, or just curious about the differences between these two approaches, this should give you a clear picture of where each one shines and where it might fall short.

Expressiveness

SQL++ lets you write rich, complex queries—joins, subqueries, aggregates, window functions—while working with flexible document data. You can express nearly anything you’d expect in SQL, plus native support for JSON.

Mongo’s query language is more limited. You can do a lot with the aggregation pipeline, but expressing multi-collection joins or deep subqueries can become verbose and may require workarounds or advanced pipeline constructs.

SQL++

Mongo

SELECT c.name, o.total
FROM customers c
JOIN orders o ON c.id = o.customer_id
WHERE o.total &gt; 100
ORDER BY o.total DESC;

SELECT c.name, o.total

FROM customers c

JOIN orders o ON c.id = o.customer_id

WHERE o.total > 100

ORDER BY o.total DESC;

Mongo requires a more procedural pipeline:

db.orders.aggregate([
  { $match: { total: { $gt: 100 } } },
  { $lookup: {
      from: "customers",
      localField: "customer_id",
      foreignField: "id",
      as: "customer"
    }
  },
  { $unwind: "$customer" },
  { $project: { name: "$customer.name", total: 1 } },
  { $sort: { total: -1 } }
]);

db.orders.aggregate([

{ $match: { total: { $gt: 100 } } },

{ $lookup: {

from: "customers",

localField: "customer_id",

foreignField: "id",

as: "customer"

}

{ $unwind: "$customer" },

{ $project: { name: "$customer.name", total: 1 } },

{ $sort: { total: -1 } }

]);

Readability

SQL++ is declarative and familiar to anyone with SQL experience. The intent of the query is easy to see.

SELECT name, age
FROM users
WHERE age &gt;= 21
ORDER BY name;

SELECT name, age

FROM users

WHERE age >= 21

ORDER BY name;

Mongo’s pipeline is more verbose and procedural—you have to mentally step through each stage.

db.users.find(
  { age: { $gte: 21 } },
  { name: 1, age: 1, _id: 0 }
).sort({ name: 1 });

db.users.find(

{ age: { $gte: 21 } },

{ name: 1, age: 1, _id: 0 }

).sort({ name: 1 });

For simple queries this isn’t terrible. But as queries grow (joins, subqueries, aggregations), readability can drop quickly in Mongo’s pipeline format.

Consistency

SQL++ is highly consistent. The syntax and semantics are predictable, and it feels like “SQL for documents” across the board.

Example with nested data:

SELECT u.name, a.city
FROM users u
UNNEST u.addresses AS a
WHERE a.country = "USA";

SELECT u.name, a.city

FROM users u

UNNEST u.addresses AS a

WHERE a.country = "USA";

In Mongo, querying nested structures often requires switching to dot notation or array operators, and pipelines use a different style than find(). You end up learning multiple syntaxes:

Simple query with dot notation:

db.users.find(
  { "addresses.country": "USA" },
  { name: 1, "addresses.city": 1, _id: 0 }
);

db.users.find(

{ "addresses.country": "USA" },

{ name: 1, "addresses.city": 1, _id: 0 }

);

More complex? You’re back in aggregation-land:

db.users.aggregate([
  { $unwind: "$addresses" },
  { $match: { "addresses.country": "USA" } },
  { $project: { name: 1, city: "$addresses.city", _id: 0 } }
]);

db.users.aggregate([

{ $unwind: "$addresses" },

{ $match: { "addresses.country": "USA" } },

{ $project: { name: 1, city: "$addresses.city", _id: 0 } }

]);

Performance

When it comes to raw performance, Couchbase SQL++ consistently outpaces MongoDB’s query language in benchmarks, especially under high-throughput, low-latency workloads.

Couchbase has demonstrated superior scalability and efficiency in various benchmarks. For instance, in the Yahoo! Cloud Serving Benchmark (YCSB), Couchbase exhibited significantly better performance at scale—up compared to MongoDB.

Beyond benchmarks, Couchbase customers have reported significant performance improvements:

- FICO chose Couchbase over MongoDB to achieve <1ms response time
- LinkedIn chose Couchbase over MongoDB to execute 10m+ queries per second

MongoDB’s latest release, while improving over its previous versions, still lags behind in certain scenarios. MongoDB 8.0 introduced performance enhancements, achieving faster mixed read/write workloads compared to MongoDB 7.0. However, these improvements are relative to its own prior performance and may not match the scalability and speed demonstrated by Couchbase in independent benchmarks (like the BenchANT Database Ranking).

Index Support

SQL++ supports a broad set of indexes: primary, secondary, composite, array, partial, covering, FTS—all usable from a single language.

Mongo supports primary (on _id), secondary, compound, multikey, text, and hashed indexes. It’s strong, but lacks some of the flexibility of SQL++ when it comes to indexing deeply nested fields or expressions.

Example with partial index in SQL++:

CREATE INDEX idx_active_users ON users(email)
WHERE active = TRUE;

1 2	CREATE INDEX idx_active_users ON users(email) WHERE active = TRUE;

Mongo partial index is similar:

db.users.createIndex(
  { email: 1 },
  { partialFilterExpression: { active: true } }
);

db.users.createIndex(

{ email: 1 },

{ partialFilterExpression: { active: true } }

);

Join Capabilities

SQL++ supports full JOIN syntax—inner, left outer, UNNEST, and combinations. It treats documents as first-class citizens in relational-style queries.

Example:

SELECT u.name, o.total
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE o.total &gt; 100;

SELECT u.name, o.total

FROM users u

JOIN orders o ON u.id = o.user_id

WHERE o.total > 100;

Mongo’s $lookup supports more expressive pipelines since 5.0, but is still LEFT OUTER and lacks full join syntax.

db.orders.aggregate([
  { $match: { total: { $gt: 100 } } },
  { $lookup: {
      from: "users",
      localField: "user_id",
      foreignField: "id",
      as: "user"
    }
  },
  { $unwind: "$user" },
  { $project: { name: "$user.name", total: 1 } }
]);

db.orders.aggregate([

{ $match: { total: { $gt: 100 } } },

{ $lookup: {

from: "users",

localField: "user_id",

foreignField: "id",

as: "user"

}

{ $unwind: "$user" },

{ $project: { name: "$user.name", total: 1 } }

]);

SQL++ also supports multiple joins and joins on expressions—Mongo’s $lookup is more limited in comparison.

Aggregation Support

SQL++ has full GROUP BY, HAVING, window functions, and flexible expressions—just like standard SQL.

Example:

SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department
HAVING avg_salary &gt; 75000;

SELECT department, AVG(salary) AS avg_salary

FROM employees

GROUP BY department

HAVING avg_salary > 75000;

Mongo’s aggregation pipeline is capable, but more procedural. You chain stages like $group, $match, $project, etc.:

db.employees.aggregate([
  { $group: { _id: "$department", avg_salary: { $avg: "$salary" } } },
  { $match: { avg_salary: { $gt: 75000 } } }
]);

db.employees.aggregate([

{ $group: { _id: "$department", avg_salary: { $avg: "$salary" } } },

{ $match: { avg_salary: { $gt: 75000 } } }

]);

Both are powerful, but SQL++ is typically more concise and familiar for SQL users, and supports features like window functions that Mongo’s pipeline lacks.

Filtering and Predicate Logic

SQL++ has full support for complex filtering with AND, OR, NOT, BETWEEN, IN, subqueries, and arbitrary expressions.

Example:

SELECT name
FROM users
WHERE (age BETWEEN 21 AND 65)
  AND (email LIKE "%@example.com")
  AND (status IN ["active", "pending"]);

SELECT name

FROM users

WHERE (age BETWEEN 21 AND 65)

AND (email LIKE "%@example.com")

AND (status IN ["active", "pending"]);

Mongo supports filtering in find() and in $match stages, but the syntax is more verbose and JSON-based:

db.users.find({
  age: { $gte: 21, $lte: 65 },
  email: { $regex: "@example\\.com$" },
  status: { $in: ["active", "pending"] }
});

db.users.find({

age: { $gte: 21, $lte: 65 },

email: { $regex: "@example\\.com$" },

status: { $in: ["active", "pending"] }

});

Both can express complex predicates, but SQL++ tends to be more readable for highly complex boolean logic.

Subquery Support

SQL++ supports full subqueries: correlated, uncorrelated, scalar, EXISTS/NOT EXISTS—all the patterns you’d expect.

Example:

SELECT name
FROM users u
WHERE EXISTS (
  SELECT 1 FROM orders o
  WHERE o.user_id = u.id AND o.total &gt; 100
);

SELECT name

FROM users u

WHERE EXISTS (

SELECT 1 FROM orders o

WHERE o.user_id = u.id AND o.total > 100

);

Mongo doesn’t support subqueries in the same way. You generally have to rewrite using $lookup, $facet, or multiple queries in application code. This is one area where SQL++ is much stronger and more natural.

Data Manipulation Support

SQL++ supports full INSERT, UPDATE, DELETE, MERGE — all standard data manipulation operations.

Example UPDATE:

UPDATE users
SET status = "inactive"
WHERE last_login &lt; "2024-01-01";

UPDATE users

SET status = "inactive"

WHERE last_login < "2024-01-01";

Mongo also supports data manipulation via insertOne, insertMany, updateOne, updateMany, deleteOne, deleteMany, and replaceOne. The functionality is solid, but operations are separate API calls—not unified under one query language.

Example update:

db.users.updateMany(
  { last_login: { $lt: new Date("2024-01-01") } },
  { $set: { status: "inactive" } }
);

db.users.updateMany(

{ last_login: { $lt: new Date("2024-01-01") } },

{ $set: { status: "inactive" } }

);

Both are capable, but SQL++ provides a single, consistent language for both querying and modifying data.

Transactions Support

SQL++ supports multi-statement ACID transactions (with Couchbase’s BEGIN TRANSACTION, COMMIT, ROLLBACK), and transactions can span multiple documents, collections, and even multiple statements.

Example:

BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = "user123";
UPDATE accounts SET balance = balance + 100 WHERE id = "user456";
COMMIT;

BEGIN TRANSACTION;

UPDATE accounts SET balance = balance - 100 WHERE id = "user123";

UPDATE accounts SET balance = balance + 100 WHERE id = "user456";

COMMIT;

Mongo introduced multi-document ACID transactions in version 4.0. They are supported, but must be initiated from drivers using a session object, or mongosh, not part of the Mongo query language itself.

Example (driver-based in Node.js):

const session = client.startSession();
session.startTransaction();
try {
  db.accounts.updateOne({ id: "user123" }, { $inc: { balance: -100 } }, { session });
  db.accounts.updateOne({ id: "user456" }, { $inc: { balance: 100 } }, { session });
  await session.commitTransaction();
} catch (error) {
  await session.abortTransaction();
}
session.endSession();

const session = client.startSession();

session.startTransaction();

try {

db.accounts.updateOne({ id: "user123" }, { $inc: { balance: -100 } }, { session });

db.accounts.updateOne({ id: "user456" }, { $inc: { balance: 100 } }, { session });

await session.commitTransaction();

} catch (error) {

await session.abortTransaction();

}

session.endSession();

Summary: Both support ACID transactions, but SQL++ lets you express them declaratively inside the query language itself, which is easier and clearer.

Error Handling

SQL++ surfaces structured error codes and messages to the client when a query fails — these are accessible through the SDKs and query metadata. You can also write queries defensively (e.g. IF EXISTS, IF MISSING, CASE expressions) to control behavior.

Example (defensive logic):

UPDATE users
SET last_login = CURRENT_TIMESTAMP
WHERE user_id = "user123"
AND last_login IS NOT MISSING;

UPDATE users

SET last_login = CURRENT_TIMESTAMP

WHERE user_id = "user123"

AND last_login IS NOT MISSING;

Error codes are documented here: Couchbase SQL++ Error Codes.

Mongo also relies on driver-level error handling — errors are raised to the client when queries or commands fail. Mongo’s aggregation pipeline does not support in-query error handling either.

Example (Mongo defensive update):

db.users.updateOne(
  { user_id: "user123", last_login: { $exists: true } },
  { $set: { last_login: new Date() } }
);

db.users.updateOne(

{ user_id: "user123", last_login: { $exists: true } },

{ $set: { last_login: new Date() } }

);

Summary: Both SQL++ and Mongo rely on external (driver-level) error handling. You can use defensive query patterns to mitigate common errors.

Extensibility

SQL++ allows user-defined functions (UDFs) written in SQL++ itself. These can encapsulate complex logic, reuse expressions, and simplify queries.

Example UDF:

CREATE FUNCTION get_discount(total FLOAT)
RETURNS FLOAT
AS
  CASE WHEN total &gt; 500 THEN 0.10
       WHEN total &gt; 100 THEN 0.05
       ELSE 0.0
  END;

CREATE FUNCTION get_discount(total FLOAT)

RETURNS FLOAT

CASE WHEN total > 500 THEN 0.10

WHEN total > 100 THEN 0.05

ELSE 0.0

END;

Usage:

SELECT customer_id, total, get_discount(total) AS discount
FROM orders;

1 2	SELECT customer_id, total, get_discount(total) AS discount FROM orders;

Mongo has no in-query UDFs in its query language or aggregation pipeline. You can use $function (introduced in MongoDB 4.4), which lets you run JavaScript functions inside aggregation — but this is less efficient and limited in portability:

db.orders.aggregate([
  {
    $addFields: {
      discount: {
        $function: {
          body: function(total) {
            if (total &gt; 500) return 0.10;
            if (total &gt; 100) return 0.05;
            return 0.0;
          },
          args: ["$total"],
          lang: "js"
        }
      }
    }
  }
]);

db.orders.aggregate([

{

$addFields: {

discount: {

$function: {

body: function(total) {

if (total > 500) return 0.10;

if (total > 100) return 0.05;

return 0.0;

args: ["$total"],

lang: "js"

}

]);

Summary: SQL++ UDFs are native, efficient, and portable; Mongo’s $function uses JavaScript and may have performance trade-offs.

Declarative Nature

SQL++ is fully declarative — you express what you want, not how to compute it. You rely on the query optimizer to determine the best execution plan. This makes queries simpler to write and maintain.

Mongo’s aggregation pipeline is more procedural — you specify the exact sequence of operations ($match, $group, $project, $sort, etc.). For complex logic, this often requires thinking in terms of data flow, not desired result.

Portability

SQL++ is based on SQL with JSON extensions. If you know SQL, it’s easy to learn SQL++, and much of your knowledge carries over. The language is also designed to be portable across Couchbase deployments — cloud, self-managed, edge.

Mongo’s query language is specific to MongoDB. Its syntax is JSON-based, not SQL-like, and most of it doesn’t transfer to other databases. While it’s great inside the Mongo ecosystem, it’s harder to port to systems like relational databases, Couchbase, or cloud data warehouses.

Pagination Support

SQL++ supports pagination using LIMIT and OFFSET — standard SQL-style:

SELECT name
FROM users
ORDER BY name
LIMIT 10 OFFSET 20;

SELECT name

FROM users

ORDER BY name

LIMIT 10 OFFSET 20;

Mongo also supports pagination with .limit() and .skip():

db.users.find({})
  .sort({ name: 1 })
  .skip(20)
  .limit(10);

db.users.find({})

.sort({ name: 1 })

.skip(20)

.limit(10);

Both handle basic pagination well.

However — SQL++ also supports window functions (e.g. ROW_NUMBER() OVER (…)), which allows for more advanced pagination and cursor-based patterns, whereas Mongo requires additional pipeline stages or application logic to simulate those.

Schema Introspection

SQL++ / Couchbase supports automatic schema discovery via the INFER command. Since Couchbase is schema-flexible, INFER lets you analyze documents in a collection and generate a probabilistic schema — showing what fields exist, their types, nesting, and occurrence percentages.

Example:

INFER `travel-sample`.inventory.airline WITH { "sample_size": 1000 };

1	INFER `travel-sample`.inventory.airline WITH { "sample_size": 1000 };

Returns information about the airline collection, including field names, types, and occurrence percentages:

[
  [
    {
      "#docs": 187,
      "$schema": "http://json-schema.org/draft-06/schema",
      "Flavor": "`type` = \"airline\"",
      "properties": {
        "callsign": { ... }, "country": { ... }, "name": { ... }, ...
      },
      "type": "object"
    }
  ]
]

[

{

"#docs": 187,

"$schema": "http://json-schema.org/draft-06/schema",

"Flavor": "`type` = \"airline\"",

"properties": {

"callsign": { ... }, "country": { ... }, "name": { ... }, ...

"type": "object"

}

]

Mongo has no direct query language equivalent to INFER, though MongoDB Atlas offers Schema Explorer in the UI.

Closest options:

- db.collection.aggregate([ { $sample: { size: N } } ]) + manual inspection
- Third-party tools

Tooling and IDE Support

SQL++ is supported in:

- Couchbase Web UI Query Workbench
- VS Code SQL++ extension
- JetBrains IDEs (plugin)
- REST API / CLI
- Couchbase Shell

Mongo’s tooling includes:

- MongoDB Compass (GUI)
- Mongo Shell / mongosh
- MongoDB Atlas UI
- VS Code MongoDB extension

There are other tools as well. Both have good tooling. SQL++ integrates better with SQL-friendly tools (DataGrip, BI tools, etc.). Mongo tools are more Mongo-specific.

Community and Documentation

SQL++ / Couchbase:

- Official Docs: Couchbase SQL++ Reference
- Active Couchbase developer community
- Blogs, forums, Stack Overflow, Discord
- SQL++ is based on familiar SQL, so existing SQL resources also apply

Mongo:

- Official Docs: MongoDB Query Language Reference
- Large community
- Many tutorials, courses, books
- Heavily used in web dev / JavaScript communities

Mongo has a bigger community (simply due to broader adoption), but SQL++ benefits from SQL familiarity and an increasingly active Couchbase developer ecosystem.

Integration with App Frameworks

SQL++ / Couchbase is supported via:

- SDKs in major languages: Java, .NET (C#), Node.js, Python, Go, C++, Scala
- ODBC / JDBC drivers → works with BI tools (Tableau, Power BI, Excel, Looker)
- REST API
- A growing ecosystem of integrations, including Spring Data, EF Core, Quarkus, and more

Mongo has:

- Official drivers for nearly every major language
- Strong ecosystem for developers

Mongo has a larger ecosystem, but SQL++ is more familiar to SQL developers in general.

Standards Compliance

SQL++ is a SQL-derived language:

- Based on ANSI SQL with extensions for JSON documents, arrays, nesting
- You can take standard SQL skills and use them right away
- Many existing SQL-based tools and patterns apply

Mongo’s query language is non-standard:

- Based on JSON syntax and operators
- No direct mapping to ANSI SQL
- Requires learning Mongo-specific query patterns

If you want SQL compatibility and standards alignment, look to SQL++. If you’re fine with a Mongo-specific language, Mongo’s approach works, but is harder to port.

Summary

SQL++ is a natural fit for developers familiar with SQL. It’s a superset of SQL, so existing SQL knowledge carries over.

MongoDB’s query language is more specialized and procedural, which can be powerful but also requires a different mindset.

Here’s the complete criteria listing:

Rank	Name of Criteria	How to Apply It	Why It’s Important
1	Expressiveness	Assess how many types of queries can be written	Determines flexibility in handling data tasks
2	Readability	Review sample queries for clarity	Impacts learning curve and maintenance
3	Consistency	Look for consistent syntax and semantics	Reduces bugs and surprises
4	Performance	Benchmark query execution on real workloads	Affects speed and scalability
5	Index Support	Check range of index types and usage options	Critical for optimizing query speed
6	Join Capabilities	Test support for different join types	Key for working with related data
7	Aggregation Support	Evaluate support for grouping and aggregation	Necessary for analytics and reporting
8	Filtering and Predicate Logic	Test complex WHERE and HAVING clauses	Enables precise data retrieval
9	Subquery Support	Verify support for correlated and nested subqueries	Adds depth to querying
10	Data Manipulation Support	Test INSERT, UPDATE, DELETE, MERGE functionality	Important for data maintenance
11	Transactions Support	Check support for multi-statement transactions	Ensures data consistency and atomicity
12	Error Handling	Review mechanisms for detecting and reporting errors	Helps with debugging and reliability
13	Extensibility	Check ability to add user-defined functions (UDFs)	Allows customization for advanced needs
14	Declarative Nature	Determine if the language specifies what to do, not how	Simplifies development and optimization
15	Portability	See how easily queries migrate across systems	Reduces vendor lock-in and aids flexibility
16	Pagination Support	Test LIMIT, OFFSET, or windowing support	Critical for APIs and UI-driven data access
17	Schema Introspection	Check querying of schema/catalog metadata	Supports automation and tooling
18	Tooling and IDE Support	Survey ecosystem of query editors, plugins, etc.	Impacts developer productivity
19	Community and Documentation	Review quality of docs, community forums, tutorials	Affects ease of learning and troubleshooting
20	Integration with App Frameworks	Check library and driver support in popular languages	Eases app development
21	Standards Compliance	Compare with industry standards (e.g. SQL)	Helps with portability and interoperability

Matthew Groves

Platform

Self-Managed

Services

Capabilities

Why Couchbase?

Migrate to Capella

By Use Case

By Industry

By Application Need

Popular Docs

By Developer Role

Quickstart

Resource Center

About

Partnerships

Our Services

Partners: Register a Deal

Ready to register a deal with Couchbase?

Marriott

All Posts

Choosing the Right Query Language: SQL++ vs. Mongo

Expressiveness

Readability

Consistency

Performance

Index Support

Join Capabilities

Aggregation Support

Filtering and Predicate Logic

Subquery Support

Data Manipulation Support

Transactions Support

Error Handling

Extensibility

Declarative Nature

Portability

Pagination Support

Schema Introspection

Tooling and IDE Support

Community and Documentation

Integration with App Frameworks

Standards Compliance

Summary

Author

Posted by Matthew Groves

Leave a reply Cancel reply