Articles in this section

Using SQL with MongoDB

  • Updated
Follow

The MongoDB connector translates standard SQL into MongoDB queries when extracting data from MongoDB. You write SQL; Etlworks runs the equivalent MongoDB query under the hood.

This article shows the SQL forms Etlworks accepts and the MongoDB queries each form produces. It also covers running native MongoDB queries and Java-driver code directly, and how to test queries from Etlworks Explorer.

How to use SQL in a MongoDB flow

  1. Create a flow and configure the transformation where the source is MongoDB.
  2. Click Mapping.

    Mapping button

  3. Enter SQL in Source query.

    SQL in Source query

  4. Add mapping or further transformations as needed.

Important: SQL identifiers (collection and field names) are case-sensitive in the translation.

SQL to MongoDB query translations

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 match

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"] }
})

Boolean equals

select column1 from my_table where column = true

******Result:*********
db.my_table.find({ "column": true })
select column1 from my_table where column = false

******Result:*********
db.my_table.find({ "column": false })

NOT boolean

select column1 from my_table where NOT column

******Result:*********
db.my_table.find({ "value": { "$ne": true } })

ObjectId support

select column1 from my_table where OBJECTID('_id') IN ('53102b43bf1044ed8b0ba36b', '54651022bffebc03098b4568')

******Result:*********
db.my_table.find({
  "_id": { "$in": [
    { "$oid": "53102b43bf1044ed8b0ba36b" },
    { "$oid": "54651022bffebc03098b4568" }
  ] }
})
select column1 from my_table 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 of records deleted)

UPDATE

UPDATE my_table SET name = 'John Doe', city = 'Memphis' WHERE customerID = 1;

******Result:*********
1 (number of 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

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 } }
])

Multi-table join with nested fields:

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": "$t2" },
  { "$lookup": {
      "from": "my_table3",
      "let": { "nested1_column": "$nested1.Column" },
      "pipeline": [
        { "$match": { "$and": [
          { "$expr": { "$eq": ["$$nested1_column", "$nested3.Column"] } },
          { "nested3.whereColumn3": "whereValue3" }
        ] } }
      ],
      "as": "t3"
  } },
  { "$unwind": "$t3" },
  { "$project": { "_id": 0, "Column1": 1, "t2.Column2": 1 } }
])

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.my_collection.aggregate([
  { "$match": { "$and": [
    { "object.key2": 34 },
    { "object2.key4": { "$gt": 5 } }
  ] } },
  { "$project": {
      "_id": 0,
      "key1": "$object.key1",
      "key3": "$object2.key3",
      "key4": "$object1.key4"
  } }
])

Alias with GROUP BY

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
  } }
])

LIMIT and OFFSET

select * from table limit 3 offset 4
-- or
select a, count(*) from table group by a limit 3 offset 4

******Result:*********
Equivalent to MongoDB's $skip stage.

Column names starting with a number

Surround the column name in double quotes:

SELECT * FROM tb_test WHERE "3rd_column" = 10

Execute native MongoDB queries

If the source query is a valid JSON document, the MongoDB connector treats it as a native MongoDB query and runs db.collection.find(query) without translation.

Example:

{"STORE_ID": 1}

Execute MongoDB queries using the Java client

If the source query is valid JavaScript, the MongoDB connector runs the script as-is — useful for aggregation pipelines and other operations beyond what SQL translation covers.

Available variables in the script

Name Class Package
database MongoDatabase com.mongodb.client
client MongoClient com.mongodb.client

Expected return value

Assign a com.mongodb.client.MongoCursor to the value variable on the last line of code.

Example: aggregation pipeline via Java client

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"))
));

Test queries in Etlworks Explorer

Expand the MongoDB connection in Etlworks Explorer, pick any collection (the editor opens against the selected collection but can run queries against any collection or multiple collections), click Develop SQL, enter the query, and execute.

Develop SQL in Explorer

Related articles