SELECT Statements
A SELECT statement can consist of the following basic clauses.
SELECT
INTO
FROM
JOIN
WHERE
GROUP BY
HAVING
UNION
ORDER BY
LIMIT
SELECT Syntax
The following syntax diagram outlines the syntax supported by the MongoDB adapter:
SELECT {
[ TOP <numeric_literal> | DISTINCT ]
{
*
| {
<expression> [ [ AS ] <column_reference> ]
| { <table_name> | <correlation_name> } .*
} [ , ... ]
}
[ INTO csv:// [ filename= ] <file_path> [ ;delimiter=tab ] ]
{
FROM <table_reference> [ [ AS ] <identifier> ]
} [ , ... ]
[ [
INNER
] JOIN <table_reference> [ ON <search_condition> ] [ [ AS ] <identifier> ]
] [ ... ]
[ WHERE <search_condition> ]
[ GROUP BY <column_reference> [ , ... ]
[ HAVING <search_condition> ]
[
ORDER BY
{ <column_reference> [ ASC | DESC ] } [ , ... ]
]
[
LIMIT <expression>
[
{ OFFSET | , }
<expression>
]
]
} | SCOPE_IDENTITY()
<expression> ::=
| <column_reference>
| @ <parameter>
| ?
| COUNT( * | { [ DISTINCT ] <expression> } )
| { AVG | MAX | MIN | SUM | COUNT } ( <expression> )
| <literal>
| <sql_function>
<search_condition> ::=
{
<expression> { = | > | < | >= | <= | <> | != | LIKE | IN | NOT IN | AND | OR } [ <expression> ]
} [ { AND | OR } ... ]
Examples
Return all columns:
SELECT * FROM Customers
Rename a column:
SELECT "CompanyName" AS MY_CompanyName FROM Customers
Search data:
SELECT * FROM Customers WHERE Country = 'US';
The MongoDB APIs support the following operators in the WHERE clause: =, >, <, >=, <=, <>, !=, LIKE, IN, NOT IN, AND, OR.
SELECT * FROM Customers WHERE Country = 'US';
Return the number of items matching the query criteria:
SELECT COUNT(*) AS MyCount FROM Customers
Return the number of unique items matching the query criteria:
SELECT COUNT(DISTINCT CompanyName) FROM Customers
Return the unique items matching the query criteria:
SELECT DISTINCT CompanyName FROM Customers
Summarize data:
SELECT CompanyName, MAX(Balance) FROM Customers GROUP BY CompanyName
Retrieve data from multiple tables.
SELECT "restaurants"."restaurant_id", "restaurants".name, "restaurants.grades".* FROM "restaurants.grades" JOIN "restaurants" WHERE "restaurants".name = 'Morris Park Bake Shop'
See
JOIN Queries for details.
Sort a result set in ascending order:
SELECT City, CompanyName FROM Customers ORDER BY CompanyName ASC
Aggregate Functions
Examples of Aggregate Functions
Below are several examples of SQL aggregate functions. You can use these with a GROUP BY clause to aggregate rows based on the specified GROUP BY criterion. This can be a reporting tool.
COUNT
Returns the number of rows matching the query criteria.
SELECT COUNT(*) FROM Customers WHERE Country = 'US'
COUNT(DISTINCT)
Returns the number of distinct, non-null field values matching the query criteria.
SELECT COUNT(DISTINCT City) AS DistinctValues FROM Customers WHERE Country = 'US'
AVG
Returns the average of the column values.
SELECT CompanyName, AVG(Balance) FROM Customers WHERE Country = 'US' GROUP BY CompanyName
MIN
Returns the minimum column value.
SELECT MIN(Balance), CompanyName FROM Customers WHERE Country = 'US' GROUP BY CompanyName
MAX
Returns the maximum column value.
SELECT CompanyName, MAX(Balance) FROM Customers WHERE Country = 'US' GROUP BY CompanyName
SUM
Returns the total sum of the column values.
SELECT SUM(Balance) FROM Customers WHERE Country = 'US'
JOIN Queries
The MongoDB Adapter supports joins of a nested array with its parent document.
Joining Nested Structures
The adapter expects the left part of the join is the array document you want to flatten vertically. This type of query is supported through the MongoDB API.
For example, consider the following query from MongoDB's restaurants collection:
SELECT "restaurants"."restaurant_id", "restaurants".name, "restaurants.grades".*
FROM "restaurants.grades"
JOIN "restaurants"
WHERE "restaurants".name = 'Morris Park Bake Shop'
See
Vertical Flattening for more details.