How to Convert SQL Query to MongoDB Query?

0
108
How to Convert SQL Query to MongoDB Query

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.
See More:
Comparison between all NoSQL Database
Nowadays, Its time to migrate from traditional RDBMS to NoSQL...

Grab this awesome tool 

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.

SQL Query to MongoDB Query
SQL Query to MongoDB Query

Some Examples for SQL Query to MongoDB Query

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

2SQL 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.

It supports INNER JOIN and LEFT JOIN, OUTER JOIN is not supported.

  1. (INNER) JOIN: Returns records that have matching values in both tables.
  2. 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.

3SQL 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.

SQL Snippet

Share your thoughts

Loading Facebook Comments ...
Loading Disqus Comments ...