The add-in maps SQL queries into the corresponding MongoDB queries. A detailed description of all the transformations is out of scope, but we will describe some of the common elements that are used. The add-in takes advantage of MongoDB features such as the aggregation framework to compute the desired results.
SELECT Queries
The SELECT statement is mapped to the find() function as shown below:
| |
| SQL Query | MongoDB Query |
SELECT * FROM Users |
db.users.find() |
SELECT user_id, status
FROM Users |
db.users.find(
{},
{ user_id: 1, status: 1, _id: 0 }
) |
SELECT *
FROM Users
WHERE status = 'A' |
db.users.find(
{ status: "A" }
) |
SELECT *
FROM Users
WHERE status = 'A' OR age=50 |
db.users.find(
{ $or: [ { status: "A" },
{ age: 50 } ] }
) |
SELECT *
FROM Users
WHERE name LIKE 'A%' |
db.users.find(
{name: /^a/}
) |
SELECT * FROM Users
WHERE status = 'A'
ORDER BY user_id ASC |
db.users.find( { status: "A" }.sort( { user_id: 1 } ) |
SELECT *
FROM Users
WHERE status = 'A'
ORDER BY user_id DESC |
db.users.find( {status: "A" }.sort( {user_id: -1} ) |
Aggregate Queries
The MongoDB aggregation framework was added in MongoDB version 2.2. The add-in makes extensive use of this for various aggregate queries. See some examples below:
| |
| SQL Query | MongoDB Query |
SELECT Count(*) As Count
FROM Orders |
db.orders.aggregate( [
{
$group: {
_id: null,
count: { $sum: 1 }
}
}
] ) |
SELECT Sum(price) As Total
FROM Orders |
db.orders.aggregate( [
{
$group: {
_id: null,
total: { $sum: "$price" }
}
}
] ) |
SELECT cust_id, Sum(price) As total
FROM Orders
GROUP BY cust_id
ORDER BY total |
db.orders.aggregate( [
{
$group: {
_id: "$cust_id",
total: { $sum: "$price" }
}
} ,
{ $sort: {total: 1 } }
] ) |
SELECT cust_id, ord_date, Sum(price) As total
FROM Orders
GROUP BY cust_id, ord_date
HAVING total > 250 |
db.orders.aggregate( [
{
$group: {
_id: {
cust_id: "$cust_id",
ord_date: {
month: { $month: "$ord_date" },
day: { $dayOfMonth: "$ord_date" },
year: { $year: "$ord_date"}
}
},
total: { $sum: "$price" }
}
},
{ $match: { total: { $gt: 250 } } }
] ) |
INSERT Statements
The INSERT statement is mapped to the INSERT function as shown below:
| |
| SQL Query | MongoDB Query |
INSERT INTO users (user_id, age, status, [address.city], [address.postalcode])
VALUES ('bcd001', 45, 'A', 'Chapel Hill', 27517) |
db.users.insert(
{ user_id: "bcd001", age: 45, status: "A", address:{ city:"Chapel Hill", postalCode:27514} }
) |
INSERT INTO t1 ("c1") VALUES (('a1', 'a2', 'a3')) |
db.users.insert({"c1": ['a1', 'a2', 'a3']}) |
INSERT INTO t1 ("c1") VALUES (()) |
db.users.insert({"c1": []}) |
INSERT INTO t1 ("a.b.c.c1") VALUES (('a1', 'a2', 'a3')) |
db.users.insert("a":{"b":{"c":{"c1":['a1','a2', 'a3']}}}) |
Update Statements
The UPDATE statement is mapped to the update function as shown below:
| |
| SQL Query | MongoDB Query |
UPDATE users
SET status = 'C', [address.postalcode] = 90210
WHERE age > 25 |
db.users.update(
{ age: { $gt: 25 } },
{ $set: { status: "C", address.postalCode: 90210 },
{ multi: true }
) |
Delete Statements
The DELETE statement is mapped to the delete function as shown below:
| |
| SQL Query | MongoDB Query |
DELETE FROM users WHERE status = 'D' |
db.users.remove( { status: "D" } ) |