SQL to MongoDB Query Converter

The converter allows you to execute the converted queries directly against a running MongoDB instance. You can specify the host, database, and batch size using the following flags:

• -h: Host and port (e.g., localhost:3086)
• -db: Database name
• -b: Batch size (default is 50)

When running in interactive mode with these flags, the tool will prompt you for SQL, execute it, and then ask if you want to see more results if the batch size is exceeded.

java -jar target/sql-to-mongo-db-query-converter-1.18-SNAPSHOT-standalone.jar -i -h localhost:3086 -db local -b 5

To use this library in your Java project, add the following dependency to your pom.xml. Ensure you are using JDK 1.8 or higher.

<dependency>
   <groupId>com.github.vincentrussell</groupId>
   <artifactId>sql-to-mongo-db-query-converter</artifactId>
   <version>1.23</version>
</dependency>

You can run the converter in an interactive mode where it prompts you for SQL input and immediately displays the resulting MongoDB query. Use the -i flag when running the standalone JAR.

java -jar target/sql-to-mongo-db-query-converter-1.18-standalone.jar -i

Using the AS keyword in SQL allows you to rename fields in the resulting MongoDB $project stage.

select object.key1 as key1, object2.key3 as key3 from my_collection

Use the OBJECTID() function in SQL to target MongoDB ObjectIds.

-- Single match
select column1 from my_table where OBJECTID('_id') = '53102b43bf1044ed8b0ba36b'

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

You can use regexMatch and notRegexMatch for regular expressions, or the LIKE operator for simple pattern matching.

Regex Match: select * from my_table where regexMatch(column,'^[ae"gaf]+$')

NOT Regex Match: select * from my_table where notRegexMatch(column,'^[ae"gaf]+$')

LIKE (Start with): select * from my_table where value LIKE 'start%'

NOT LIKE: select * from my_table where value NOT LIKE 'start%'

The IN and NOT IN SQL operators are converted to MongoDB $in and $nin operators.

-- IN
select column1 from my_table where value IN ("theValue1","theValue2","theValue3")

-- NOT IN
select column1 from my_table where value NOT IN ("theValue1","theValue2","theValue3")

The converter supports basic UPDATE and DELETE statements, returning the number of affected records.

-- Delete
delete from my_table where value IN ("theValue1","theValue2","theValue3")

-- Update
UPDATE my_table SET name = 'John Doe', city= 'Melphis' WHERE customerID = 1;

The SQL LIMIT and OFFSET clauses are converted to MongoDB's $skip and $limit functionality.

select * from table limit 3 offset 4

The converter supports SQL date functions to generate MongoDB $date queries.

Standard Date Format: select * from my_table where date(column,'YYYY-MM-DD') >= '2016-12-12'

Natural Language Dates: select * from my_table where date(column,'natural') >= '5000 days ago'

SQL INNER JOIN statements are converted into MongoDB $lookup aggregation stages, typically involving a $match stage, a $lookup stage with a pipeline, and an $unwind stage to flatten the joined array.

select t1.column1, t2.column2 from my_table as t1 inner join my_table2 as t2 on t1.column = t2.column

Complex SQL aggregations including GROUP BY, ORDER BY, and HAVING are converted into MongoDB .aggregate() pipelines using $match, $group, $sort, and $project stages.

-- Group By with Order By
select borough, cuisine, count(*) from my_collection WHERE borough LIKE 'Queens%' GROUP BY borough, cuisine ORDER BY count(*) DESC;

-- Having clause
select Restaurant.cuisine, count(*) from Restaurants group by Restaurant.cuisine having count(*) > 3;

-- Count without GROUP BY
select count(*) as c from table