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.

MongoDB 4.4

Couchbase 6.5 

Released

GA: July 2020

GA: January 2020

What’s new?

https://www.mongodb.com/collateral/mongodb-4.4-guide

https://docs.couchbase.com/server/current/introduction/whats-new.html

UNION set operation

Introduces $UnionWith aggregation pipeline stage to bend data into a single resultset. Roughly equivalent to UNION ALL set operation in SQL.

https://docs.mongodb.com/master/reference/operator/aggregation/unionWith/#pipe._S_unionWith

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).

https://docs.couchbase.com/server/current/n1ql/n1ql-language-reference/union.html

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

The significant feature here iss $function which allows users to write Javascript snippet as part of the aggregation pipeline to customize the processing. It can also create user-defined aggregation using $accumulate, $function, $merge and $group.   This approach seems verbose and error-prone since it has to be written for every query.  In SQL, once you create a user-defined aggregate, it can be used like any other aggregate without exposing the logic.

Other improvements are simple scalar functions.

https://docs.mongodb.com/master/release-notes/4.4/#custom-aggregation-expressions

Released the preview of SQL functions and Javascript functions anywhere an expression can be used in N1QL.  These can be part of any complex query for data processing. This functionality is expected to be GA in the next major release.

MongoDB still lacks business-critical window functions.  This was released in Couchbase 6.5 and has a major impact on developer productivity.

Couchbase has supported Javascript based aggregation via map-reduce views but is inaccessible via N1QL.

Couchbase Eventing functions are also written in Javascript.  These provide a scalable and flexible infrastructure for modern apps.

Search

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.

Transactions

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.

Resources:

  1. Couchbase docs
  2. N1QL Tutorial
  3. MongoDB docs

Posted by Keshav Murthy

Keshav Murthy is a Vice President at Couchbase R&D. Previously, he was at MapR, IBM, Informix, Sybase, with more than 20 years of experience in database design & development. He lead the SQL and NoSQL R&D team at IBM Informix. He has received two President's Club awards at Couchbase, two Outstanding Technical Achievement Awards at IBM. Keshav has a bachelor's degree in Computer Science and Engineering from the University of Mysore, India, holds ten US patents and two US patents pending.

Leave a reply