mongo-to-sql
v1.3.2
Published
Downloads
37
Readme
MongoToSQL - Convert MongoDB aggregation pipelines to their SQL equivalent
Notes
- Some pipelines throw errors so it would be safer to wrap all library function call in a try..catch block.
- When a
$match
stage is immediately followed by a$project
stage, an optimization will kick in where the headless output of the$project
will be appended with the headless output of the$match
to avoid an unnecessary subquery.
Supported pipelines
- $group
- $project
- $match
- $lookup
Supported $group operators
- $sum
- NOTE: $sum currently does not support nested operators or multiple expressions through an array.
$lookup
$lookup as per the MongoDB documentation performs a left outer join. This behaviour has been mirrored here. To change the type of join, please specify the joinType
key in the $lookup object.
The difference with the as
key is that it takes an object that will map from the result to the table that is being joined with.
For example:
$lookup({
from: "states",
localField: "state_id",
foreignField: "id",
as: {
stateName: "name",
stateId: "id"
}
})
will return
SELECT t2.name as stateName, t2.id as stateId FROM (SELECT * FROM currentTable) t1 LEFT JOIN (SELECT * FROM states) t2 ON t1.state_id = t2.id
$match usage
$match(matchObject, tableName, options)
- options (optional): A hashmap of options
- headless: Should the
SELECT * FROM tableName
be included. Defaults totrue
.
- headless: Should the
Example usage
$match({
status: "D",
qty: 2
},
"inventory",
{
headless: true
});
will return
WHERE status = 'D' AND qty = 2
Without the headless option specified, it will return
SELECT * FROM inventory WHERE status = 'D' AND qty = 2
$match notes
This library currently only supports comparisons with numbers. Strings and arrays are not compared and will cause an error when used with $eq despite (MongoDB's support for the same)[https://docs.mongodb.com/manual/reference/operator/query/eq/#match-an-array-value].
$ne will only match values and not strings at the moment.
Only arrays can be passed as the value to the $in operator.
Example Usage
For a complete understanding and set of examples for how to use this library, please refer to the tests folder.
Using $sum:
let collectionName = "loginstore";
mongoToSQL.convert(collectionName, [
{"$group": {
count: {
"$sum": 1
},
age: "$age"
}}
]);