This is a short note reviewing the MongoDB MQL language features highlighted in the release blog: MongoDB 4.4: User-Driven Engineering. Ready for You. MongoDB 4.4 release has added a number of features for the MQL language. Couchbase released 6.5 earlier the year with a significant number of features in N1QL — both for query service and analytics service. Let’s dive in.
GA: July 2020
GA: January 2020
UNION set operation
Introduces $UnionWith aggregation pipeline stage to bend data into a single resultset. Roughly equivalent to UNION ALL set operation in SQL.
Supports all of the SQL set operations since the initial release: UNION, UNION ALL, EXCEPT, EXCEPT ALL, INTERSECT, INTERSECT ALL. The ALL modifier returns duplicate documents. Using the operation without ALL avoids the sort stage you need to write in the MQL aggregation pipeline. These are set operations that can be used anywhere SELECT query is used (e.g. subquery, derived tables, etc).
Also, see Lukas Eder’s blog. https://blog.jooq.org/2015/10/06/you-probably-dont-use-sql-intersect-or-except-often-enough/
Custom Aggregation Expressions
Other improvements are simple scalar functions.
Introduces full text $search based on Lucene, but only for MongoDB Atlas service.
The mongodb product itself has limited search functionality and isn’t based on the Lucene.
The $search comes with a usual set of restrictions in MongoDB: can only be used with aggregation pipeline (e.g. cannot be used in updates or deletes as a predicate). $search can only be in the first stage of any pipeline.
Couchbase 6.5 introduced SEARCH() function and its related metadata functions. It can do simple string search to full-text search with conjuncts and disjuncts with deep integration with N1QL optimizer in the query service.
Introduces the ability to create collections and indexes within a non-sharded transaction. Some databases allow the use of DDLs within a transaction but is a rare case.
Released distributed transactions for Java SDKs. This enables the applications to operate on multiple documents directly using the get/set APIs. Distributed transactions are unsupported for N1QL statements.