Overview
Etlworks MongoDB connectors can automatically translate standard SQL to MongoDB queries when extracting data from MongoDB.
Process
Step 1. Create flow and configure transformation where the source is MongoDB.
Step 2. Click the Mapping button.
Step 3. Enter SQL in Source query.
Step 4. Add Mapping and other transformations if needed.
SQL to MongoDB query
The identifiers in SQL which can be translated to MongoDB query are case sensitive.
Dates
select * from my_table where date(column,'YYYY-MM-DD') >= '2016-12-12'
******Result:*********
db.my_table.find({
"column": {
"$gte": {
"$date": 1452556800000
}
}
})Natural Language Dates
select * from my_table where date(column,'natural') >= '5000 days ago'
******Result:*********
db.my_table.find({
"column": {
"$gte": {
"$date": 1041700019654
}
}
})Regex
select * from my_table where regexMatch(column,'^[ae"gaf]+$')
******Result:*********
db.my_table.find({
"column": {
"$regex": "^[ae\"gaf]+$"
}
})NOT Regex match
select * from my_table where notRegexMatch(column,'^[ae"gaf]+$')
******Result:*********
db.my_table.find({
"column": {
"$not": /^[ae\"gaf]+$/
}
})Distinct
select distinct column1 from my_table where value IS NULL
******Result:*********
db.my_table.distinct("column1" , {
"value": {
"$exists": false
}
})Like
select * from my_table where value LIKE 'start%'
******Result:*********
db.my_table.find({
"value": {
"$regex": "^start.*$"
}
})Not Like
select * from my_table where value NOT LIKE 'start%'
******Result:*********
db.my_table.find({
"value": {
"$not": /^start.*$/
}
})IN
select column1 from my_table where value IN ("theValue1","theValue2","theValue3")
******Result:*********
db.my_table.find({
"value" : {
"$in" : ["theValue1","theValue2", "theValue3"]
}
})
Not IN
select column1 from my_table where value NOT IN ("theValue1","theValue2","theValue3")
******Result:*********
db.my_table.find({
"value" : {
"$nin" : ["theValue1","theValue2", "theValue3"]
}
})
Is True
select column1 from my_table where column = true
******Result:*********
db.my_table.find({
"column" : true
})
Is False
select column1 from my_table where column = false
******Result:*********
db.my_table.find({
"column" : false
})
Not True
select column1 from my_table where NOT column
******Result:*********
db.my_table.find({
"value" : {$ne: true}
})
ObjectId Support
select column1 from where OBJECTID('_id') IN ('53102b43bf1044ed8b0ba36b', '54651022bffebc03098b4568')
******Result:*********
db.my_table.find({
"_id" : {$in: [{$oid: "53102b43bf1044ed8b0ba36b"},{$oid: "54651022bffebc03098b4568"}]}
})
select column1 from where OBJECTID('_id') = '53102b43bf1044ed8b0ba36b'
******Result:*********
db.my_table.find({
"_id" : {$oid: "53102b43bf1044ed8b0ba36b"}
})
Delete
delete from my_table where value IN ("theValue1","theValue2","theValue3")
******Result:*********
3 (number or records deleted)
Update
UPDATE my_table SET name = 'John Doe', city= 'Melphis' WHERE customerID = 1;
******Result:*********
1 (number or records updated)
Group By (Aggregation)
select borough, cuisine, count(*) from my_collection WHERE borough LIKE 'Queens%' GROUP BY borough, cuisine ORDER BY count(*) DESC;
******Mongo Query:*********
db.my_collection.aggregate([{
"$match": {
"borough": {
"$regex": "^Queens.*$"
}
}
},{
"$group": {
"_id": {
"borough": "$borough",
"cuisine": "$cuisine"
},
"count": {
"$sum": 1
}
}
},{
"$sort": {
"count": -1
}
},{
"$project": {
"borough": "$_id.borough",
"cuisine": "$_id.cuisine",
"count": 1,
"_id": 0
}
}])
Having clause with aggregation
select Restaurant.cuisine, count(*) from Restaurants group by Restaurant.cuisine having count(*) > 3;
******Mongo Query:*********
db.Restaurants.aggregate([
{
"$group": {
"_id": "$Restaurant.cuisine",
"count": {
"$sum": 1
}
}
},
{
"$match": {
"$expr": {
"$gt": [
"$count",
3
]
}
}
},
{
"$project": {
"Restaurant.cuisine": "$_id",
"count": 1,
"_id": 0
}
}
])
Count without GROUP BY
select count(*) as c from table
******Mongo Query:*********
db.table.aggregate([{ "$group": { "_id": {}, "c": { "$sum": 1 } } },{ "$project": { "c": 1, "_id": 0 } }])
Avg without GROUP BY
select avg(field) as avg from table
******Mongo Query:*********
db.table.aggregate([{ "$group": { "_id": {}, "avg": { "$avg": "$field" } } },{ "$project": { "avg": 1, "_id": 0 } }])
Joins
select t1.column1, t2.column2 from my_table as t1 inner join my_table2 as t2 on t1.column = t2.column
******Result:*********
db.my_table.aggregate([
{
"$match": {}
},
{
"$lookup": {
"from": "my_table2",
"let": {
"column": "$column"
},
"pipeline": [
{
"$match": {
"$expr": {
"$eq": [
"$$column",
"$column"
]
}
}
}
],
"as": "t2"
}
},
{
"$unwind": {
"path": "$t2",
"preserveNullAndEmptyArrays": false
}
},
{
"$project": {
"_id": 0,
"column1": 1,
"t2.column2": 1
}
}
])
or
select t1.Column1, t2.Column2 from my_table as t1 inner join my_table2 as t2 on t1.nested1.Column = t2.nested2.Column inner join my_table3 as t3 on t1.nested1.Column = t3.nested3.Column where t1.nested1.whereColumn1 = "whereValue1" and t2.nested2.whereColumn2 = "whereValue2" and t3.nested3.whereColumn3 = "whereValue3"
******Result:*********
db.my_table.aggregate([
{
"$match": {
"nested1.whereColumn1": "whereValue1"
}
},
{
"$lookup": {
"from": "my_table2",
"let": {
"nested1_column": "$nested1.Column"
},
"pipeline": [
{
"$match": {
"$and": [
{
"$expr": {
"$eq": [
"$$nested1_column",
"$nested2.Column"
]
}
},
{
"nested2.whereColumn2": "whereValue2"
}
]
}
}
],
"as": "t2"
}
},
{
"$unwind": {
"path": "$t2",
"preserveNullAndEmptyArrays": false
}
},
{
"$lookup": {
"from": "my_table3",
"let": {
"nested1_column": "$nested1.Column"
},
"pipeline": [
{
"$match": {
"$and": [
{
"$expr": {
"$eq": [
"$$nested1_column",
"$nested3.Column"
]
}
},
{
"nested3.whereColumn3": "whereValue3"
}
]
}
}
],
"as": "t3"
}
},
{
"$unwind": {
"path": "$t3",
"preserveNullAndEmptyArrays": false
}
},
{
"$project": {
"_id": 0,
"c1": "$Column1",
"c2": "$t2.Column2",
"c3": "$t3.Column3"
}
}
])
Alias
select object.key1 as key1, object2.key3 as key3, object1.key4 as key4 from my_collection where object.key2 = 34 AND object2.key4 > 5;
******Mongo Query:*********
db.Restaurants.aggregate([{
"$match": {
"$and": [
{
"Restaurant.cuisine": "American"
},
{
"Restaurant.borough": {
"$gt": "N"
}
}
]
}
},{
"$project": {
"_id": 0,
"key1": "$Restaurant.borough",
"key3": "$Restaurant.cuisine",
"key4": "$Restaurant.address.zipcode"
}
}])
Alias Group By (Aggregation)
select borough as b, cuisine as c, count(*) as co from my_collection WHERE borough LIKE 'Queens%' GROUP BY borough, cuisine ORDER BY count(*) DESC;
******Mongo Query:*********
db.my_collection.aggregate([{
"$match": {
"borough": {
"$regex": "^Queens.*$"
}
}
},{
"$group": {
"_id": {
"borough": "$borough",
"cuisine": "$cuisine"
},
"co": {
"$sum": 1
}
}
},{
"$sort": {
"co": -1
}
},{
"$project": {
"b": "$_id.borough",
"c": "$_id.cuisine",
"co": 1,
"_id": 0
}
}])
Offset
select * from table limit 3 offset 4
or
select a, count(*) from table group by a limit 3 offset 4
******Result:*********
is equivalent to the $skip function in mongodb json query language
Using column names that start with a number. Surround it in quotes
SELECT * FROM tb_test WHERE "3rd_column" = 10
Executing native MongoDB queries
If the provided query is a valid JSON the MongoDB connector interprets it as native MongoDB query, does not translate it to SQL and executes the query as db.collection.find(query).
Example:
{"STORE_ID": 1}
Executing MongoDB queries using Java client
If the provided query is a valid JavaScript code the MongoDB connector executes the script as is, without translating it to SQL.
Available variables
The following variables can be referenced by name from JavaScript code:
| Name | Class name / JavaDoc | Package |
|---|---|---|
| database | com.mongodb.client.MongoDatabase | com.mongodb.client |
| collection | com.mongodb.client.MongoCollection | com.mongodb.client |
| client | com.mongodb.client.MongoClient | com.mongodb.client |
Expected return value
The code must return an instance of com.mongodb.client.MongoCursor by assigning to the variable value in the last executed line of code.
Example
var myCollection = database.getCollection("myCollection");
value = myCollection.aggregate(Arrays.asList(
new org.bson.Document("$unwind", "$views"),
new org.bson.Document("$match", new org.bson.Document("views.isActive", true)),
new org.bson.Document("$sort", new org.bson.Document("views.date", 1)),
new org.bson.Document("$limit", 200),
new org.bson.Document("$project", new org.bson.Document("_id", 0)
.append("url", "$views.url")
.append("date", "$views.date"))
));Testing queries in Explorer
To test the query in Explorer, select and expand the MongoDB connection, select collection (you can run the query against any collection or multiple collections, but you need to select any collection to open the query editor), click Develop SQL, enter and execute the query.
Comments
0 comments
Please sign in to leave a comment.