Being a developer šØš½āš»Ā , if you want to write a complex query in MongoDB, then it becomes very difficult.Ā Today I have brought a new tool for you called NoSQLBooster for MongoDB, which will convert your SQL Query to MongoDB Query.
Sounds like a charm, isn’t it?Ā After this tutorial, you will get rid of the mess of the MongoDB query. So let’s start our tutorial.
With NoSQLBooster for MongoDB, you can run SQL SELECT Query against MongoDB. SQL support includes functions, expressions, aggregation for collections with nested objects and arrays.
What features you can get from this tool?
- Access data via SQL including WHERE filters, ORDER BY, GROUP BY, HAVING, DISTINCT, LIMIT
- SQL Functions (COUNT, SUM, MAX, MIN, AVG)
- SQL Functions (Date, String, Conversion)
- SQL Equi JOIN and Uncorrelated SubQuery
- Aggregation Pipeline Operators as SQL Functions (dateToString, toUpper, split, substrā¦)
- Provide a programming interface (mb.runSQLQuery) that can be integrated into your script
- Autocomplete for keywords, MongoDB collection names, field names, and SQL functions.
Grab this awesome toolĀ
[tmh_article_ads]
Convert SQL Query to MongoDB Query
Note:Ā It does not natively support SQL features. The SQL query is validated and translated fromĀ SQL Query to MongoDB Query and executed by NoSQLBooster for MongoDB.
Some Examples forĀ SQL Query to MongoDB Query
Use String and Date SQL Functions
IfĀ we want to find all employees who are hired this year and display first_name and last_name as the full name.
SELECT concat("first_name", ' ', "last_name") as fullname, dateToString('%Y-%m-%d',"hire_date") as hiredate FROM employees WHERE "hire_date" >= date('2017-01-01')
It converts into:
db.employees.aggregate( [{ "$match": { "hire_date": { "$gte": ISODate("2017-01-01T08:00:00.000+08:00") } } }, { "$project": { "fullname": { "$concat": [ "$first_name", " ", "$last_name" ] }, "hiredate": { "$dateToString": { "format": "%Y-%m-%d", "date": "$hire_date" } } } } ])
SQL Equi JOIN
NoSQLBooster supports SQL Equi JOIN which performs a JOIN against equality or matching column(s) values of the associated tables.
An equal sign (=) is used as a comparison operator in the where clause to refer to equality.
[tmh_article_ads]
It supports INNER JOIN and LEFT JOIN, OUTER JOIN is not supported.
- (INNER) JOIN: Returns records that have matching values in both tables.
- LEFT JOIN: Return all records from the left table, and the matched records from the right table.
SELECT * FROM orders JOIN inventory ON orders.item_id=inventory.itemId
Convert into:
// COMMENT US WHAT WILL BE THE OUTPUT IN FORM OF MONGODB BY USING THIS TOOL.
SQL Snippets
NoSQLBooster includes a lot of SQL-specific code snippets to save you time, Date Range, Text Search, Query and Array, Existence Check, Type Check and so on. You can always manually trigger it with āCtrl-Shift-Spaceā}}}. Out of the box, āCtrl-Spaceā}}}, āAlt-Spaceā}}} are acceptable triggers.
Share your thoughts