NoSQLBooster - The Smartest GUI Tool and IDE for MongoDB

All SQL samples are running against MongoDB 3.4+. MongoDB 2.6-3.2 does not support $addFields operator; the equivalent MongoDB query will be slightly different.

It should be mentioned that there is a tutorial on NoSQLBooster SQL Query for MongoDB in the lower left “Samples” pane. With this tutorial, you can learn and understand how to use NoSQLBooster SQL Query for MongoDB. Better yet, all SQL Functions provide the appropriate code snippets and mouse hover information and support code completion.

##SQL SELECT

The SELECT statement is used to select data from a database.

The data returned is stored in a result table, called the result-set.

SELECT Syntax
SELECT column1, column2, ...
FROM table_name;

Here, column1, column2, ... are the field names of the table you want to select data from. If you want to select all the fields available in the table, use the following syntax:

SELECT * FROM table_name;

Example - Select All Fields from a Table/Collection

SELECT * FROM customers

The equivalent MongoDB query

db.customers.aggregate()

Example - Select Individual Fields from a Table/Collection

SELECT supplier_name, city FROM suppliers

The equivalent MongoDB query

db.suppliers.aggregate(
  [{
    "$project": {
      "supplier_name": 1,
      "city": 1
    }
  }])

Example - Exclude Select Individual Fields from a Table/Collection

SELECT supplier_name,city, -_id FROM suppliers

The equivalent MongoDB query

db.suppliers.aggregate(
  [{
    "$project": {
      "supplier_name": 1,
      "city": 1,
      "_id": 0
    }
  }])

SQL SELECT DISTINCT

The SELECT DISTINCT statement is used to return only distinct (different) values.

Inside a table, a column often contains many duplicate values; and sometimes you only want to list the different (distinct) values.

SELECT DISTINCT country FROM customers;

The equivalent MongoDB query

db.customers.aggregate(
  [{
      "$match": {
        "country": {
          "$exists": true
        }
      }
    },
    {
      "$group": {
        "_id": {
          "country": "$country"
        }
      }
    },
    {
      "$project": {
        "country": "$_id.country"
      }
    }
  ])

The following SQL statement lists the number of different (distinct) customer countries:

SELECT COUNT(DISTINCT country) FROM customers;

The equivalent MongoDB query

db.customers.aggregate(
  [{
      "$group": {
        "_id": null,
        "COUNT(DISTINCT country)": {
          "$addToSet": "$country"
        }
      }
    },
    {
      "$project": {
        "COUNT(DISTINCT country)": {
          "$size": "$COUNT(DISTINCT country)"
        }
      }
    }
  ])

##SQL WHERE

The WHERE clause is used to filter records.

The WHERE clause is used to extract only those records that fulfill a specified condition.

WHERE Clause Example

The following SQL statement selects all the customers from the country "ShangHai", in the "customers" table:

SELECT * FROM customers WHERE country='ShangHai';

The equivalent MongoDB query

db.customers.find({
  "country": "ShangHai"
})

Single quotes are for strings. If you use double quotes "ShangHai," NoSQLBooster for MongoDB will treat it as a column "ShangHai," not a string 'ShangHai.'

SELECT * FROM customers WHERE country="ShangHai";

The SQL will generate the following MongoDB query that you may not want.

db.customers.aggregate(
  [{
      "$addFields": {
        "__tmp_cond_1": {
          "$eq": [
            "$country",
            "$ShangHai"
          ]
        }
      }
    },
    {
      "$match": {
        "__tmp_cond_1": true
      }
    },
    {
      "$project": {
        "__tmp_cond_1": 0
      }
    }
  ])

Text Fields vs. Numeric Fields

NoSQLBooster SQL requires single quotes around text values. However, numeric fields should not be enclosed in quotes:

Example

SELECT * FROM customers WHERE customer_id !=1;

The equivalent MongoDB query

db.customers.find({
  "customer_id": {
    "$ne": 1
  }
})

Operators in The WHERE Clause

The following operators can be used in the WHERE clause:

##SQL AND, OR and NOT

The AND and OR operators are used to filter records based on more than one condition:

The AND operator displays a record if all the conditions separated by AND is TRUE.
The OR operator displays a record if any of the conditions separated by OR is TRUE.
The NOT operator displays a record if the condition(s) is NOT TRUE. not supported

###AND Example

SELECT * FROM customers
WHERE country='China' AND city='ShangHai';

The equivalent MongoDB query

db.customers.find({
  "country": "China",
  "city": "ShangHai"
})

###OR Example

SELECT * FROM Customers
WHERE city='ShangHai' OR city='Wuxi';

The equivalent MongoDB query

db.Customers.find({
  "$or": [{
      "city": "ShangHai"
    },
    {
      "city": "Wuxi"
    }
  ]
})

###NOT Example (not supported)

###Combining AND and OR Example

SELECT * FROM customers
WHERE country='China' AND (city='ShangHai' OR city='Wuxi');

The equivalent MongoDB query

db.customers.find({
  "$and": [{
      "country": "China"
    },
    {
      "$or": [{
          "city": "ShangHai"
        },
        {
          "city": "Wuxi"
        }
      ]
    }
  ]
})

##SQL ORDER BY

The SQL ORDER BY clause is used to sort the records in the result set for a SELECT statement.

This example would return all records from the customers sorted by the last_name field in ascending order.

SELECT *
FROM customers
ORDER BY last_name ASC; --Most programmers omit the ASC attribute if sorting in ascending order.

The equivalent MongoDB query

db.customers.find({})
  .sort({
    "last_name": 1
  })

Example - Using both ASC and DESC attributes

SELECT *
FROM products
WHERE product_id <> 8
ORDER BY category_id DESC, product_name ASC;

The equivalent MongoDB query

db.products.find({
    "product_id": {
      "$ne": 8
    }
  })
  .sort({
    "category_id": -1,
    "product_name": 1
  })

##SQL NULL Values

The IS NULL condition is used in SQL to test for a NULL value. It returns TRUE if a NULL value is found or missing fields, otherwise it returns FALSE. The IS NOT NULL condition is used in SQL to test for a non-NULL value. It returns TRUE if a non-NULL value is found. Otherwise, it returns FALSE. It does not include documents that do not contain the field

Different query operators in MongoDB treat null values differently. Please refer to Query for Null or Missing Fields and How do you query this in Mongo? (is not null)

This example will return all records from the customers' table where the favorite_website is null or missing fields.

SELECT * FROM customers WHERE favorite_website IS NULL;

The equivalent MongoDB query

db.customers.find({
  "favorite_website": null
})

This example will return all records from the customers' table where the favorite_website field exists, and its value is not null

SELECT * FROM customers WHERE favorite_website IS NOT NULL;

The equivalent MongoDB query

db.customers.find({
  "favorite_website": {
    "$ne": null
  }
})

If you want to check whether the field exists with MongoDB, you have to use ["$exists" operator]. (https://docs.mongodb.com/manual/reference/operator/query/exists/). At present, there is no corresponding SQL function.

db.inventory.find( { qty: { $exists: true } });

//or  chaining method
db.inventory.where("qty").exists(true);

##SQL LIMIT and OFFSET

The LIMIT clause is used in the SELECT statement to constrain the number of rows in a result set. The LIMIT clause accepts one or two arguments. The values of both arguments must be zero or positive integers.

The following illustrates the LIMIT clause syntax with two arguments:

SELECT column1,column2,... FROM table LIMIT offset , count;

Let's examine the LIMIT clause parameters:

The offset specifies the offset of the first row to return. The offset of the first row is 0, not 1.
The count determines the maximum number of rows to return.

e.g. Using LIMIT to get the first N rows

select * from housing limit 3;

The equivalent MongoDB query

db.housing.find({})
  .limit(3)

e.g. Using LIMIT to skip N rows and get the first N rows

select * from housing order by units desc limit 3,10;

The equivalent MongoDB query

db.housing.find({})
  .sort({
    "units": -1
  })
  .limit(10)
  .skip(3)

Tip: The mb.runSQLQuery() functions return a cursor, you can use chaining methods to skip and get N rows.

instead of writing:

  select * from housing order by units desc limit 3,10;

We can write.

mb.runSQLQuery(`
    select * from housing order by units desc
`)
  .skip(3)   //chaining skip and limit methods
  .limit(10)

Their execution results are the same

##SQL COUNT(), SUM() , AVG() , MIN() and MAX()

COUNT(*) and COUNT(column_name)

The COUNT() function returns the number of rows that match specified criteria.

e.g. COUNT Function only includes NOT NULL Values

SELECT COUNT(customer_id) FROM customers;

The equivalent MongoDB query

db.customers.aggregate(
  [{
      "$match": {
        "customer_id": {
          "$ne": null
        }
      }
    },
    {
      "$group": {
        "_id": null,
        "COUNT(customer_id)": {
          "$sum": NumberInt("1")
        }
      }
    }
  ])

AVG(), MIN() and MAX()

The AVG() function returns the average value of a numeric column.

The MIN() function returns the smallest value of the selected column.

The MAX() function returns the largest value of the selected column.

e.g.

SELECT avg(salary), min(salary), max(salary) FROM employees WHERE salary > 25000;

The equivalent MongoDB query

db.employees.aggregate(
  [{
      "$match": {
        "salary": {
          "$gt": 25000
        }
      }
    },
    {
      "$group": {
        "_id": null,
        "AVG(salary)": {
          "$avg": "$salary"
        },
        "MIN(salary)": {
          "$min": "$salary"
        },
        "MAX(salary)": {
          "$max": "$salary"
        }
      }
    }
  ])

SUM()

The SUM() function returns the total sum of a numeric column.

SELECT SUM(salary) AS "Total Salary"
FROM employees
WHERE salary > 25000;

The equivalent MongoDB query

db.employees.aggregate(
  [{
      "$match": {
        "salary": {
          "$gt": 25000
        }
      }
    },
    {
      "$group": {
        "_id": null,
        "Total Salary": {
          "$sum": "$salary"
        }
      }
    }
  ])

Compare above SQL statement and generated MongoDB scripts, using MongoDB script to write some simple aggregate operation is a little tedious.

##SQL GROUP BY

The SQL GROUP BY clause can be used in a SELECT statement to collect data across multiple records and group the results by one or more columns.

e.g.

SELECT dept_id, SUM(salary) AS total_salaries
FROM employees
GROUP BY dept_id;

The equivalent MongoDB query

db.employees.aggregate(
  [{
      "$group": {
        "_id": {
          "dept_id": "$dept_id"
        },
        "total_salaries": {
          "$sum": "$salary"
        }
      }
    },
    {
      "$project": {
        "dept_id": "$_id.dept_id",
        "total_salaries": "$total_salaries"
      }
    }
  ])

##SQL LIKE and Wildcards

The SQL LIKE condition allows you to use wildcards to perform pattern matching in a query.

% Allows you to match any string of any length (including zero length)
_ Allows you to match on a single character
LIKE condition is not case-sensitive
ESCAPE 'escape_character' is not supported.

e.g., match any string of any length

SELECT *
FROM customers
WHERE last_name LIKE 'J%'
ORDER BY last_name;

The equivalent MongoDB query

db.customers.find({
    "last_name": /^J.*$/i
  })
  .sort({
    "last_name": 1
  })

e.g., match on a single character

SELECT *
FROM categories
WHERE category_id LIKE '_5';

The equivalent MongoDB query

db.categories.find({
  "category_id": /^.5$/i
})

##SQL IN Operator

The SQL IN condition allows you to quickly test if expression matches any value in a list of values. It is used to help reduce the need for multiple OR conditions in a SELECT statement.

SELECT *
FROM suppliers
WHERE supplier_name IN ('Microsoft', 'Oracle', 'Flowers Foods');

The equivalent MongoDB query

db.suppliers.find({
  "supplier_name": {
    "$in": [
      "Microsoft",
      "Oracle",
      "Flowers Foods"
    ]
  }
})

##SQL IN Uncorrelated Sub-queries

NoSQLBooster supports uncorrelated sub-queries that will be translated as MongoDB $lookup stage.

 SELECT *, holidays FROM absences
 WHERE holidays IN (SELECT name, date
                     FROM holidays
                     WHERE year = 2018);

The equivalent MongoDB query

db.getCollection("absences").aggregate(
 [
	{
		"$lookup" : {
			"from" : "holidays",
			"as" : "holidays",
			"pipeline" : [
				{
					"$match" : {
						"year" : 2018
					}
				},
				{
					"$project" : {
						"name" : 1,
						"date" : 1
					}
				}
			]
		}
	}
])

##SQL BETWEEN Operator

The SQL BETWEEN condition allows you to quickly test if an expression is within a range of values (inclusive).

Use the ISODate function to cast a string to MongoDB Date. The mongo shell wraps the Date object with the ISODate helper. All MongoDB helper functions are case-sensitive.

NOT BETWEEN is not supported.

SELECT *
FROM orders
WHERE ord_date BETWEEN ISODate('2016-02-27') AND ISODate('2017-02-27');

The equivalent MongoDB query

db.orders.find({
  "ord_date": {
    "$gte": ISODate("2016-02-27T08:00:00.000+08:00"),
    "$lte": ISODate("2017-02-27T08:00:00.000+08:00")
  }
})

##SQL Aliases

SQL ALIASES can be used to create a temporary name for columns or tables. At present NoSQLBooster SQL for MongoDB is only for a single collection, there is no need to support TABLE ALIASES.

COLUMN ALIASES are used to make column headings in your result set easier to read.
TABLE ALIASES (not supported) are used to shorten your SQL to make it easier to understand or when you are performing a self-join.

e.g.

SELECT dept_id, COUNT(*) AS total
FROM employees
GROUP BY dept_id;

##SQL HAVING Clause The SQL HAVING clause is used in combination with the GROUP BY clause to restrict the groups of returned rows to only those whose the condition is TRUE.

e.g.

SELECT dept_id, COUNT(*) AS "Number of employees"
FROM employees
WHERE salary > 5000
GROUP BY dept_id
HAVING COUNT(*) > 5;

##SQL Comments

Comments are used to explain sections of SQL statements, or to prevent the execution of SQL statements.

###Single Line Comments Single line comments start with --.

e.g.

--Select all customer:
SELECT * FROM customers;

SELECT * FROM customers -- WHERE City='ShangHai';

###Multi-line Comments Multi-line comments start with /* and end with */.

Any text between /* and */ will be ignored.

e.g.

/*Select all the columns
of all the records
in the Customers table:*/
SELECT * FROM customers;

##SQL Functions and MongoDB Aggregation Operators##

Through mapping SQL functions to MongoDB operators, NoSQLBooster for MongoDB allows you to use all MongoDB aggregation operators as SQL functions in your SQL statement.

For example, we want to find all employees who are hired this year, and display first_name and last_name as 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')

The equivalent MongoDB query:

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

Let us look at concat function concat("first_name", ' ', "last_name"). The concat function is a MongoDB string aggregation operators.

// instead of writing
{ $concat: [ "$first_name", " ", "last_name" ] }

// we write,  
concat("first_name", ' ', "last_name") //Double quotes quote field name, Single quotes are for strings

// or 
concat(first_name, ' ', last_name)  //allow to omit double quotes

No $ prefix with MongoDB operator and collection field name
Double quotes quote field name. Single quotes are for strings.
All function names are case-sensitive except for COUNT, SUM, MAX, MIN, AVG
We can use the standard SQL comparison operators: =, !=, <>, <, <=, >=, or >.

The date function convert string to a MongoDB Date type. NoSQLBooster for MongoDB uses Moment.js to parse date string. When creating a date from a string, Moment.js first check if the string matches known ISO 8601 formats, Moment.js then check if the string matches the RFC 2822 Date time format before dropping to the fall back of new Date(string) if a known format is not found.

Please refer to Moment.js parse string document to view all supported string formats.

The dateToString is another MongoDB date operator to convert a date object to a string according to a user-specified format. The $dateToString expression has the following syntax:

{ $dateToString: { format: <formatString>, date: <dateExpression> } }

As SQL functions doesn't support JSON object parameter, NoSQLBooster for MongoDB converts the object param as plain parameter list.

dateToString('%Y-%m-%d',"hire_date") as hiredate

The first parameter is formatString,single quotes, the second parameter is "Date Field", double quotes.

###List of MongoDB Aggregation Operators###

NoSQLBooster for MongoDB allows you to use all the following MongoDB aggregation operators as SQL functions in your SQL statement.

###SQL Joins

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 comparison operator in the where clause to refer equality.

NoSQLBooster 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

Equi JOIN will be translated as MongoDB $lookup stage. (MongoDB 3.2+) refer to: https://docs.mongodb.com/manual/reference/operator/aggregation/lookup

e.g.

   SELECT * FROM orders
   JOIN inventory ON orders.item=inventory.sku

db.getCollection("orders").aggregate(
 [
	{
		"$lookup" : {
			"from" : "inventory",
			"localField" : "item",
			"foreignField" : "sku",
			"as" : "inventory_docs"
		}
	},
	{
		"$match" : {
			"inventory_docs" : {
				"$ne" : [ ]
			}
		}
	},
	{
		"$addFields" : {
			"inventory_docs" : {
				"$arrayElemAt" : [
					"$inventory_docs",
					0
				]
			}
		}
	},
	{
		"$replaceRoot" : {
			"newRoot" : {
				"$mergeObjects" : [
					"$inventory_docs",
					"$$ROOT"
				]
			}
		}
	},
	{
		"$project" : {
			"inventory_docs" : 0
		}
	}
])

###SQL Subqueries

Not Supported.

The EXISTS operator is used to test for the existence of any record in a subquery.

e.g.

SELECT SupplierName
FROM Suppliers
WHERE EXISTS (SELECT ProductName FROM Products WHERE SupplierId = Suppliers.supplierId AND Price < 20);

SELECT ProductName
  FROM Product 
 WHERE Id IN (SELECT ProductId 
                FROM OrderItem
               WHERE Quantity > 100)

###SQL UNION and UNION ALL (MongoDB 4.4+)

The SQL UNION operator is used to combine the result sets of 2 or more SELECT statements. It removes duplicate rows between the various SELECT statements.

e.g.

    SELECT supplier_id, supplier_name
    FROM suppliers
    WHERE supplier_id > 2000
    UNION
    SELECT company_id, company_name
    FROM companies
    WHERE company_id > 1000
    ORDER BY 1;

###SQL any Operators

Not Supported.

The any operators are used with a WHERE or HAVING clause.

The ANY operator returns true if any of the subquery values meet the condition.

The ALL operator returns true if all of the subquery values meet the condition.

e.g.

SELECT ProductName
FROM Products
WHERE ProductID = ANY (SELECT ProductID FROM OrderDetails WHERE Quantity = 10);

SELECT ProductName
FROM Products
WHERE ProductID = ALL (SELECT ProductID FROM OrderDetails WHERE Quantity = 10);

###SQL SELECT INTO Statement

Not Supported.

The SELECT INTO statement copies data from one table into a new table.

SELECT *
INTO newtable [IN externaldb]
FROM oldtable
WHERE condition;

Complete List of SQL Support

Example - Select All Fields from a Table/Collection

Example - Select Individual Fields from a Table/Collection

Example - Exclude Select Individual Fields from a Table/Collection

SQL SELECT DISTINCT

WHERE Clause Example

Text Fields vs. Numeric Fields

Operators in The WHERE Clause

COUNT(*) and COUNT(column_name)

AVG(), MIN() and MAX()

SUM()