TIBCO Data Virtualization® Adapter Online Help > TDV MongoDB Adapter > NoSQL Database > Query Mapping
 
Query Mapping
The adapter 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 adapter 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 adapter 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} }
)
 
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" } )