The connector 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 connector 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 connector 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" } ) |