MongoSQL queries are written using a subset of the SQL-92 standard. This document details Aqua Data Studio's MongoSQL syntax and functions. See the SQL Mapping page for how Aqua Data Studio's syntax maps to MongoDB Query syntax (http://docs.mongodb.org/manual/core/read-operations/#queries-in-mongodb).
Keywords are not case-sensitive. In this document, keywords such as SELECT are capitalized for illustration purposes.
When specifying collection_name, if the collection is in a different database than the current database, you can use the syntax db.collection_name.
Query Syntax
The MongoSQL Query Syntax is designed to allow the use of SQL-like statements to query the structured data documents of MongoDB. View the SQL Mapping reference to see how the MongoSQL syntax and commands map to MongoDB's commands.
All MongoSQL queries are SELECT statements of this form:
SELECT [FLATTEN | JSON | FLATTEN_ARRAY] [DISTINCT|ALL] expr1 [[AS] alias1], expr2 [[AS] alias2], ...
[FROM collection_name] [[AS] tablealias]
[WHERE condition]
[GROUP BY field1|alias1, field2|alias2, ...]
[HAVING condition]
[ORDER BY field1|alias1 [DESC|ASC], field2|alias2 [DESC|ASC], ...]
[LIMIT n]
[OFFSET o]
GO
SELECT ...
{ UNION | UNION ALL | INTERSECT | EXCEPT }
SELECT ...
GO
SELECT clause
SELECT expr1 [AS alias1], expr2 [AS alias2], ...
The SELECT clause indicates the set of values to be returned by a query. Expressions (expr1, etc.) in the SELECT clause can be field names, literals or functional expressions that operate on fields or literals. Expressions must be comma-separated.
The SELECT clause supports an AS section, which defines an alias for the field, literal, or functional expression.
Example
SELECT city AS htowns FROM zips WHERE city LIKE 'H%'
Subdocument and Array Fields
Querying subdocument and array data is supported automatically. Consider the following:
Schema ---------------------------------------------------- |- _id: (ObjectId) |- teamName: (string) |- city: (string) |- division: (string) |- ranking: (integer) +- managerName: (record) | |- first: (string) | |- last: (string) |- colors: (array[string]) |- worldChampionships: (integer) +- stats: (array[record]) | |- year: (integer) | |- wins: (integer) | |- losses: (integer) | |- winPercentage: (double)
Notice that we have subdocument and array fields. If you run the following query:
SELECT FLATTEN_ARRAY teamName, managerName.first, colors, stats.year, stats.wins FROM baseball
The query would return data with a flattened output like the following:
teamName managerName.first colors stats.year stats.wins ----------- -------------------- --------- ------------- ------------- Cubs Dale blue 2010 75 Cubs Dale white 2010 75 Cubs Dale blue 2011 71 Cubs Dale white 2011 71 Cubs Dale blue 2012 61 Cubs Dale white 2012 61 Cardinals Mike red 2010 86 Cardinals Mike white 2010 86 Cardinals Mike red 2011 90 Cardinals Mike white 2011 90 Cardinals Mike red 2012 88 Cardinals Mike white 2012 88 Yankees Joe navy 2010 95 Yankees Joe white 2010 95 Yankees Joe navy 2011 97 Yankees Joe white 2011 97 Yankees Joe navy 2012 95 Yankees Joe white 2012 95 Mets Terry blue 2010 79 Mets Terry white 2010 79 Mets Terry blue 2011 77 Mets Terry white 2011 77 Mets Terry blue 2012 74 Mets Terry white 2012 74 Giants Bruce orange 2010 92 Giants Bruce black 2010 92 Giants Bruce orange 2011 86 Giants Bruce black 2011 86 Giants Bruce orange 2012 94 Giants Bruce black 2012 94 Orioles Buck orange 2010 66 Orioles Buck black 2010 66 Orioles Buck white 2010 66 Orioles Buck orange 2011 69 Orioles Buck black 2011 69 Orioles Buck white 2011 69 Orioles Buck orange 2012 93 Orioles Buck black 2012 93 Orioles Buck white 2012 93 Angels Mike red 2010 80 Angels Mike white 2010 80 Angels Mike red 2011 86 Angels Mike white 2011 86 Angels Mike red 2012 89 Angels Mike white 2012 89 A's Bob green 2010 81 A's Bob gold 2010 81 A's Bob white 2010 81 A's Bob green 2011 74 A's Bob gold 2011 74 A's Bob white 2011 74 A's Bob green 2012 94 A's Bob gold 2012 94 A's Bob white 2012 94 Rays Joe blue 2010 96 Rays Joe white 2010 96 Rays Joe blue 2011 91 Rays Joe white 2011 91 Rays Joe blue 2012 90 Rays Joe white 2012 90 White Sox Robin black 2010 88 White Sox Robin silver 2010 88 White Sox Robin white 2010 88 White Sox Robin black 2011 79 White Sox Robin silver 2011 79 White Sox Robin white 2011 79 White Sox Robin black 2012 85 White Sox Robin silver 2012 85 White Sox Robin white 2012 85
FLATTEN | JSON | FLATTEN_ARRAY option
FLATTEN | JSON | FLATTEN_ARRAY
FLATTEN is default. FLATTEN removes nesting levels of subdocuments. FLATTEN does not flatten array fields. Each subdocument field is returned as a separate field in the result set.
Example
SELECT FLATTEN * FROM zips WHERE city LIKE 'H%'
JSON
JSON returns subdocuments and arrays in JSON format.
Example
SELECT JSON * FROM zips WHERE city LIKE 'H%'
FLATTEN_ARRAY
FLATTEN_ARRAY flattens subdocuments and unfurls array values. Each subfield in a subdocument is returned as a separate field. Each value in an array is returned in a separate record with any non-array fields being duplicated.
Example
SELECT FLATTEN_ARRAY * FROM zips WHERE city LIKE 'H%'
DISTINCT | ALL option
DISTINCT
The DISTINCT keyword returns only distinct (different) values. DISTINCT only supports querying one field based on MongoDB's specifications.
Example
SELECT DISTINCT state FROM zips
MongoDB flattens an array field when processing the DISTINCT command.
The following DISTINCT select statements are not supported. They will generate an error.
SELECT DISTINCT * FROM zips
GO
SELECT DISTINCT city, state, pop FROM zips
GO
ALL
ALL is default. Returns all values.
Example
SELECT ALL state FROM zips
COUNT(DISTINCT <field>)
An additional DISTINCT keyword syntax is supported only for the COUNT() aggregate function.
SELECT COUNT(DISTINCT <field>) FROM <collection_name>
Example
SELECT COUNT(DISTINCT state) FROM zips
FROM clause
Determines the specific dataset to examine to retrieve data. For MongoDB, this would be indicated as a specific Collection.
We support the following syntax for specifying a collection that is in a different database than the currently connected database.
SELECT * FROM qa.testArray
where the database name is qa and the collection name is testArray.
Example
SELECT * FROM zips
There is no JOIN support in MongoSQL. Only one collection can be specified in a FROM statement. The following query:
SELECT * FROM baseball A, basketball B WHERE city IN ['Chicago'] AND A.city=B.city
would generate an error.
WHERE clause
The WHERE clause, sometimes called the predicate, states the qualifying conditions for a query. Multiple conditions can be joined by boolean AND and OR clauses, optionally surrounded by (parentheses) to group them.
To query the _id field with an ObjectId value, use the ObjectId function.
WHERE _id = ObjectId('50ca79698957f0069c479a48')
To query a subdocument field, use the dot notation.
WHERE managerName.first = 'Joe'
Due to limitation of the Mongo API, when comparing two fields in the WHERE filter, only integer, double, string, boolean, and code data types work correctly.
Conditions:
condition AND condition
condition && condition
condition OR condition
condition || condition
CONTAINS_ALL ('value','value',...)
CONTAINS_ALL ( 2011, 2012, 2013,... )
NOT condition
expr IS [NOT] NULL
expr [=|>=|>|<=|<|<>|!=] predicate
expr [NOT] IN (subquery)
expr [NOT] IN (value [, value] ...)
expr [NOT] BETWEEN expr AND expr
expr [NOT] BETWEEN (subquery) AND (subquery)
expr [NOT] LIKE like_expr ESCAPE escape_char
Expressions:
expr | expr
expr & expr
expr << expr
expr >> expr
expr + expr
expr – expr
expr * expr
expr / expr
expr % expr
Math expressions ( + , - , *, / , % ) are supported on SELECT fields ( e.g. select itemID + prodID from NewItems ) and inside WHERE filters (e.g. select * from NewItems where itemID + prodID < 1000 ).
Because both standard SQL and JSON objects are supported, both parentheses () and square brackets [] can be used to specify the IN condition. Either of these will work:
SELECT * FROM baseball WHERE city IN ['Chicago','New York']
SELECT * FROM baseball WHERE city IN ('Chicago','New York')
Example
SELECT * FROM zips WHERE city LIKE 'KE%' AND pop > 2000
GROUP BY clause
The GROUP BY clause is used in combination with aggregate functions to group the results by one or more documents.
Example
SELECT city FROM zips WHERE pop > 100000 GROUP BY state
Each document can have a different set of fields in MongoDB. This is very different from a RDBMS where a table has a defined set of columns and each record has the same set of columns. In MongoDB, a field may exist for one record but not for another record. A WHERE filter can cause some fields to become non-existing. Our implementation of MongoSQL is limited by the behaviors of MongoDB. Consider this example:
SELECT foo FROM baseball WHERE managerName.first='Joe' GROUP BY foo, bar
The MongoShell equivalent for this query, as it is behaves on Aqua Data Studio is:
db.baseball.aggregate({$group: {"_id": {foo:"$foo", bar: "$bar"}}}, {$project: {foo: "$_id.foo", bar: "$_id.bar", _id:0}}).result
which returns a single row with an empty object:
[ { } ]
HAVING clause
Used in conjunction with GROUP BY, allows retreiving a subset of an aggregate function.
On an aggregate SELECT query (i.e. when the GROUP BY clause is present), the WHERE clause filters the documents before performing the GROUP BY operation while the HAVING clause filters the result set after the aggregation step. In the WHERE clause, the collection's field names are used when processing the WHERE filters. However, the field names specified in the HAVING clause are matched with the alias names specified in the SELECT fields first when processing the HAVING filters.
Examples:
SELECT worldChampionships+1 as worldChampionships FROM baseball
WHERE worldChampionships=9
The above query returns the document where the value of the worldChampionships field is 9.
SELECT worldChampionships+1 as worldChampionships FROM baseball
GROUP BY worldChampionships HAVING worldChampionships=9
The above query uses an alias worldChampionships for the value of worldChampionships+1. TheworldChampionships field in the HAVING clause refers to the value of worldChampionships+1. Therefore, the query returns the document where worldChampionships+1=9.
SELECT worldChampionships+1 as worldChampionships FROM baseball
GROUP BY worldChampionships HAVING worldChampionships=10
The above query returns the document where worldChampionships+1=10.
Example
SELECT city, SUM(pop) AS pop FROM zips GROUP BY city HAVING pop > 30000
ORDER BY clause
ORDER BY is used to sort the result set by a specified field and sorts in ascending order by default. Sorting by descending order can be done with the use of DESC.
ORDER BY columnIndex cannot be used with a SELECT * query. This limitation occurs because the $sort operator from MongoDB's Aggregation Framework requires a field name argument for the order-key parameter.
If ORDER BY is used on a query that has a GROUP BY clause or contains aggregate functions or arithmetic expressions inside the WHAT selector, then the order-by-key should match the name of the column that will be displayed in the result set. As an alternative, a column index can be used inside the ORDER BY clause:
SELECT a+1 FROM order_coll ORDER BY a+1
or
SELECT city, avg(ranking), sum(ranking), count(ranking) FROM baseball GROUP BY city ORDER BY 3
Example
SELECT city, state FROM zips WHERE pop > 50000 ORDER BY state
LIMIT and OFFSET clause
LIMIT restricts the number of documents returned by the SELECT statement. OFFSET indicates the offset of the first document to return.
Example
SELECT city FROM zips LIMIT 10 GO SELECT city FROM zips LIMIT 10 OFFSET 5 GO
LIMIT and OFFSET apply to the documents being queried, not to the flattened records when the FLATTEN_ARRAY option is used.
Consider the following result set.
SELECT teamName, colors FROM baseball
teamName colors
----------- ------------------------------
Cubs [ "blue", "white" ]
Cardinals [ "red", "white" ]
Yankees [ "navy", "white" ]
Mets [ "blue", "white" ]
Giants [ "orange", "black" ]
Orioles [ "orange", "black", "white" ]
Angels [ "red", "white" ]
A's [ "green", "gold", "white" ]
Rays [ "blue", "white" ]
White Sox [ "black", "silver", "white" ]
SELECT FLATTEN_ARRAY teamName, colors FROM baseball LIMIT 1 OFFSET 1
teamName colors
----------- ---------
Cardinals red
Cardinals white
The query retrieves one document with an offset of 1 and then flattens its fields.
UNION | UNION ALL | INTERSECT | EXCEPT clause
UNION
UNION can be used to combine the result sets of two or more SELECT statements.
Example
SELECT dodad FROM newproduct UNION SELECT dodad FROM newproductclone GO
UNION clauses can be very CPU and RAM intensive because the resulting documents are compared and selected on the client side within Aqua Data Studio. Out of Memory Exceptions can occur if extremely large amounts of documents are compared and selected. See our Memory Configuration page on how to increase the RAM Aqua Data Studio uses.
Nested UNION, EXCEPT, and INTERSECT are not supported.
UNION ALL
UNION ALL can be used to combine the result sets of two or more SELECT statements and will include duplicate rows.
Example
SELECT dodad FROM newproduct UNION ALL SELECT dodad FROM newproductclone GO
INTERSECT
INTERSECT returns the results of two or more SELECT statements. However, it only returns the rows retrieved by all SELECTs. If a record exists in one statement and not in the other, it will be omitted from the INTERSECT results.
Example
SELECT customerid FROM customers INTERSECT SELECT customerid FROM orders GO
INTERSECT clauses can be very CPU and RAM intensive because the resulting documents are compared and selected on the client side within Aqua Data Studio. Out of Memory Exceptions can occur if extremely large amounts of documents are compared and selected. See our Memory Configuration page on how to increase the RAM Aqua Data Studio uses.
Nested UNION, EXCEPT, and INTERSECT are not supported.
EXCEPT
EXCEPT combines two SELECT statements and returns rows from the first SELECT statement that are not returned by the second SELECT statement. EXCEPT returns only rows not available in second SELECT statement.
Example
SELECT customerid FROM customers EXCEPT SELECT customerid FROM orders GO
EXCEPT clauses can be very CPU and RAM intensive because the resulting documents are compared and selected on the client side within Aqua Data Studio. Out of Memory Exceptions can occur if extremely large amounts of documents are compared and selected. See our Memory Configuration page on how to increase the RAM Aqua Data Studio uses.
Nested UNION, EXCEPT, and INTERSECT are not supported.
DML Syntax
Data manipulation language (DML) is a family of syntax elements used to insert, delete and update data in a database. The DML below is specific to MongoDB in Aqua Data Studio.
INSERT statement
INSERT [INTO] collection_name
VALUES (json_document) [, (json_document) …]
INSERT [INTO] collection_name (field1, field2 …)
VALUES (value1, value2 …)
INSERT [INTO] insert_collection_name
SELECT fields FROM select_collection_name [WHERE ...]
INSERT places data into a specified Collection. Data can be specified in JSON format or as values. If an INSERT includes a field that does not already exist in a collection, that field will be created.
The Fields list displayed within Aqua Data Studio's Schema Browser for a collection is generated by examining the first document in its collection. After executing an INSERT that includes new fields for a collection, the Schema Browser may not display these new fields. By examining the _id value next to the Fields node in the Schema Browser you can determine which document is being used to generate the Fields list.
When using the INSERT ... SELECT syntax on a sub-document field, you need to pass in the top-level field in JSON format. For example:
INSERT INTO managers (name) SELECT JSON managerName FROM baseball
insert_collection_name cannot be the same as select_collection_name.
Examples
INSERT INTO zips
VALUES ({"city": "MEER", "loc": [-86.51599, 33.584199], "pop": 6055, "state": "CA", "_id": "99999"})
INSERT INTO zips (city)
VALUES ('CRANKTON')
INSERT INTO cities (name) SELECT city FROM zips
UPDATE statement
UPDATE [ALL|ONE] collection_name SET field_name1=value1[, field_name2=value2 …]
WHERE condition
value can be a JSON document if field_name is a subdocument.
Default is UPDATE ALL. UPDATE ONE updates only the first document that matches the criteria. UPDATE updates the contents of a Collection.
If the field name doesn't exist in the collection, it will be created.
The default behavior of a MongoSQL UPDATE statement updates all records that satisfy the WHERE criteria. If there is no WHERE clause, then all records will be updated. This does function differently than the default behavior of MongoDB's update operation, which updates the first document that matches the criteria.
Example
UPDATE zips SET pop='626' WHERE city='CANKTON' AND state='LA'
UPSERT statement
REPLACE [ALL|ONE] [INTO] collection_name VALUE (json_document) WHERE condition
Default is UPSERT ALL. If a document that satisfies the WHERE criteria is found, UPSERT updates every field inside the matched document with its corresponding value from the given document.
The Fields list displayed within Aqua Data Studio's Schema Browser for a collection is generated by examining the first document in its collection. After executing a REPLACE INTO that includes fields which are new to the collection, the Schema Browser may not display these new fields. By examining the _id value next to the Fields node in the Schema Browser you can determine which document is being used to generate the Fields list.
Example
REPLACE INTO zips VALUE({"city": "GRONTOWN", "loc": [-92.533765, 30.025565], "pop": 83, "state": "LA"}) WHERE city='GRONTOWN'
DELETE statement
DELETE [ALL|ONE] FROM collection_name [WHERE condition]
Default is DELETE ALL. If there is a WHERE criteria, DELETE ONE will delete only the first document that matches the criteria.
Example
DELETE FROM zips WHERE city='CRAWFISH TRAP'
DDL Syntax
Data Definition Language (DDL) is a syntax used to define data structures, especially database schemas. The DDL below is specific to MongoDB in Aqua Data Studio. For CREATE and INSERT commands MongoDB implicitly creates databases and collections.
CREATE DATABASE
CREATE DATABASE db_name [CAPPED] COLLECTION collection_name [SIZE sizeInBytes] [MAX_CAP numofDocuments]
CREATE DATABASE generates a database (a physical container of Collections) with the given name. Each database gets its own set of files on the file system.
Since MongoDB does not create the database physically until some data is created, you need to specify the first collection to be created when creating a database.
Example
CREATE DATABASE webstore COLLECTION NewProducts
DROP DATABASE
DROP DATABASE db_name
DROP DATABASE removes the database and all of its Collections.
Example
DROP DATABASE webstore
CREATE COLLECTION
CREATE [ CAPPED ] COLLECTION collection_name [ SIZE sizeInBytes ] [ MAX_CAP numOfDocuments ]
CREATE COLLECTION generates a grouping of BSON Documents with a given name. Collections are analogous to Tables.
If CAPPED is true, SIZE is the maximum size in bytes for the capped collection.
If CAPPED is false, SIZE is optional and it specifies the size to preallocate space for the collection.
MAX_CAP is optional. If CAPPED is true, it specifies a maximum "cap" in number of documents.
As a limitation of MongoDB, you cannot delete documents from a CAPPED collection. To remove the collection entirely, use DROP COLLECTION.
Example
CREATE COLLECTION products
CREATE CAPPED COLLECTION logger SIZE 10240 MAX_CAP 10
DROP COLLECTION
DROP COLLECTION collection_name
DROP COLLECTION removes a given Collection and all of its contents. This is analogous to dropping a Table.
Example
DROP COLLECTION products
ALTER COLLECTION
ALTER COLLECTION modifies an existing Collection by deleting or modifying its contained documents. This is analogous to altering a Table.
ALTER COLLECTION DROP FIELD
ALTER COLLECTION collection_name
DROP FIELD field_name
This alters a Collection by dropping a specific field.
Example
ALTER COLLECTION products DROP FIELD widget
ALTER COLLECTION RENAME FIELD
ALTER COLLECTION collection_name
RENAME FIELD old_field_name new_field_name
This alters a Collection by renaming an existing field.
Example
ALTER COLLECTION products RENAME FIELD dodad thingamabob
ALTER COLLECTION CAPPED SIZE
ALTER COLLECTION collection_name
CAPPED SIZE sizeInBytes
This command converts a non-capped collection to a capped collection.
Example
ALTER COLLECTION products CAPPED SIZE 2056
RENAME COLLECTION
RENAME COLLECTION collection_name TO new_name [ DROP_TARGET ]
RENAME COLLECTION changes the name of an existing Collection. A RENAME COLLECTION statement will not fail if target collection namespace contains a database that doesn't exist yet. This way users can quickly move collections to a different database without having to create it in advance. If DROP_TARGET is specified, the target of RENAME_COLLECTION will be dropped prior to renaming the Collection.
Example
RENAME COLLECTION products TO NewProducts
CREATE INDEX
CREATE [UNIQUE] [SPARSE] INDEX index_name
ON collection_name(field_name [ASC|DESC][, field_name2 [ASC|DESC]] …) [BACKGROUND] [DROP_DUPS]
CREATE INDEX creates an Index for a specified Collection. If BACKGROUND or DROP_DUPS are not specified they will be regarded as "false", which is the default.
Example
CREATE UNIQUE INDEX myIndex ON NewItems(itemID)
DROP INDEX
DROP INDEX index_name ON collection_name
DROP INDEX removes an Index for a specified Collection.
Example
DROP INDEX myIndex ON NewItems
CREATE USER
MongoDB 2.6+ CREATE USER user_name [GRANT role[, role, ...]] IDENTIFIED BY <password> [DESCRIPTION <string_value>]
This is equivalent to the createUser command (http://docs.mongodb.org/manual/reference/command/createUser/)
In MongoDB 2.6 role can be user-defined roles.
MongoDB 2.4 CREATE USER user_name GRANT role[, role, ...] IDENTIFIED BY <password>
role options are: READ, READWRITE, DBADMIN, USERADMIN, CLUSTERADMIN, READANYDATABASE, READWRITEANYDATABASE, USERADMINDATABASE, DBADMINANYDATABASE
In MongoDB 2.4 multiple GRANT options can be specified as it supports role-based privileges.
MongoDB 2.2 CREATE USER user_name GRANT [ READWRITE | READ ] IDENTIFIED BY <password>
In MongoDB 2.2 only one GRANT option can be specified as it supports access-based privileges.
CREATE USER generates a user for the MongoDB server.
Example
CREATE USER jpizzle GRANT READWRITE, USERADMIN IDENTIFIED BY '1337@55H@X0|2'
ALTER USER
MongoDB 2.6+ ALTER USER <user_name> [IDENTIFIED BY <password>] [DESCRIPTION <string_value>]
ALTER USER updates a user profile for changing password and setting custom data in MongoDB Server
This is the equivalent of the updateUser command (http://docs.mongodb.org/manual/reference/command/updateUser/)
Example
ALTER USER jpizzle IDENTIFIED BY '1337@55H@X0|2'
DROP USER
DROP USER user_name
DROP USER removes a user for the MongoDB Server
Example
DROP USER jpizzle
GRANT ROLE TO USER
MongoDB 2.6+ GRANT ROLE <role_name> [DB <db>] TO USER <user>
GRANT ROLE grants a role belonging to the current database or another database to a user. It grants one role at a time.
Example
GRANT ROLE comrole DB mydatabase TO USER jpizzle
REVOKE ROLE FROM USER
MongoDB 2.6+ REVOKE ROLE <role_name> [DB <db>] FROM USER <user>
REVOKE ROLE revokes a role belonging to the current database or another database from a user. It revokes one role at a time.
Example
REVOKE ROLE comrole DB mydatabase FROM USER jpizzle
CREATE ROLE
MongoDB 2.6+ CREATE ROLE <new_role> PRIVILEGE [DB <db> COLLECTION <collection> | CLUSTER] ACTIONS <action> [, <action>, <action> ...]
MongoDB 2.6+ CREATE ROLE <new_role> ROLE <inherited_role> [DB <db>]
CREATE ROLE creates a new role in the current database specifying the privilege or a role to inherit privileges.
<action> is a permitted action for the specified resource (http://docs.mongodb.org/manual/reference/privilege-actions/)
Example
CREATE ROLE comrole PRIVILEGE DB mydatabase COLLECTION mycollection ACTIONS "find"
Example
CREATE ROLE all_baseball_role PRIVILEGE DB sports COLLECTION baseball ACTIONS "find", "insert", "remove", "update"
DROP ROLE
MongoDB 2.6+ DROP ROLE role_name
DROP ROLE removes a role for the MongoDB Server
Example
DROP ROLE comrole
GRANT PRIVILEGE TO ROLE
MongoDB 2.6+ GRANT PRIVILEGE [DB <db> COLLECTION <collection> | CLUSTER] ACTIONS <action> [, <action>, <action> …] TO ROLE <role>
GRANT PRIVILEGE TO ROLE grants an additional privilege or inherited role to an existing role in the current database.
This is equivalent to the grantPrivilegesToRole command (http://docs.mongodb.org/manual/reference/command/grantPrivilegesToRole/)
Example
GRANT PRIVILEGE DB mydatabase COLLECTION mycollection ACTIONS "find" TO ROLE comrole
Example
GRANT PRIVILEGE DB sports COLLECTION test ACTIONS "find", "insert", "remove", "update" TO ROLE all_baseball_role
Example
GRANT PRIVILEGE DB "admin" COLLECTION "baseball" Actions "createCollection","createIndex","createRole","createUser","dropCollection" TO ROLE "test_deployment"
GRANT ROLE TO ROLE
MongoDB 2.6+ GRANT ROLE <inherited_role> [DB <db>] TO ROLE <role>
GRANT ROLE TO ROLE grants an additional role or inherited role to an existing role in the current database.
This is equivalent to the grantRolesToRole command (http://docs.mongodb.org/manual/reference/command/grantRolesToRole/)
Example
GRANT ROLE newrole DB mydatabase TO ROLE comrole
REVOKE PRIVILEGE FROM ROLE
MongoDB 2.6+ REVOKE PRIVILEGE [DB <db> COLLECTION <collection> | CLUSTER] ACTIONS <action> [, <action>, <action> …] FROM ROLE <role>
REVOKE PRIVILEGE FROM ROLE revokes a privilege or inherited role from an existing role in the current database.
This is equivalent to the revokePrivilegesFromRole command (http://docs.mongodb.org/manual/reference/command/revokePrivilegesFromRole/)
Example
REVOKE PRIVILEGE DB mydatabase COLLECTION mycollection ACTIONS "find" FROM ROLE comrole
Example
REVOKE PRIVILEGE DB sports COLLECTION test ACTIONS "insert", "remove", "update" FROM ROLE all_baseball_role
Example
REVOKE PRIVILEGE DB "user" COLLECTION "user" ACTIONS "dropCollection","dropRole","dropUser","emptycapped","enableProfiler" FROM ROLE "test_deployment"
REVOKE ROLE FROM ROLE
MongoDB 2.6+ REVOKE ROLE <inherited_role> [DB <db>] FROM ROLE <role>
REVOKE ROLE FROM ROLE revokes a role or inherited role from an existing role in the current database.
This is equivalent to the revokeRolesFromRole command (http://docs.mongodb.org/manual/reference/command/revokeRolesFromRole/)
Example
REVOKE ROLE newrole DB mydatabase FROM ROLE comrole
Supported Functions
A limited set of MongoDB's Aggregate and Math functions (http://docs.mongodb.org/manual/reference/aggregation/#_S_group) are available.
Aggregate Functions
ADDTOSET
Returns an array of all the values found in the selected field among the documents in that group. Every unique value only appears once in the result set. There is no ordering guarantee for the output documents.
AVG
Returns the average of the values.
COUNT
Returns the number of instances of the value.
FIRST
Returns the first instance of the value.
LAST
Returns the last instance of the value.
MAX
Returns the highest value encountered.
MIN
Returns the lowest value encountered.
PUSH
Returns an array of all the values found in the selected field among the documents in that group. A value may appear more than once in the result set if more than one field in the grouped documents has that value.
SUM
Sums the values.
Example
SELECT city, SUM(pop) FROM zips GROUP BY city
Arithmetic and Mathematical Functions
Math expressions ( + , - , *, / , % ) are supported on WHAT fields ( e.g. select itemID + prodID from NewItems ) and inside WHERE filters (e.g. select * from NewItems where itemID + prodID < 1000 ). Arithmetic and Mathematical expressions require the presence of a FROM clause.
/ Division operator
Divides the first number in an array of two numbers by the second number in an array.
- Minus operator
Subtracts the second number in a pair of numbers from the first number in that pair.
% Modulo operator
Returns the remainder when the first number in a pair of numbers is divided by the second number in that pair.
+ Addition operator
Adds together one or more numbers and returns their sum.
* Multiplication operator
Multiplies one or more numbers and returns their product.
Example
SELECT itemID + prodID
FROM products
WHERE dodad='whirlygig4'
Operators from the Aggregation Framework have some limitations. For example, given the baseball collection containing documents like:
{ "teamName" : "Cardinals",
"city" : "St. Louis",
"division" : "NL Central",
"ranking" : 1,
"managerName" : { "first" : "Mike", "last" : "Matheny" },
"colors" : [ "red", "white" ],
"worldChampionships" : 11,
"stats" : [ { "year" : 2010, "wins" : 86, "losses" : 76, "winPercentage" : 0.531 },
{ "year" : 2011, "wins" : 90, "losses" : 72, "winPercentage" : 0.556 },
{ "year" : 2012, "wins" : 88, "losses" : 74, "winPercentage" : 0.543 }
]
}
The query:
SELECT stats.wins + stats.losses FROM baseball
will translate to
db.baseball.aggregate({$project: { "sum": { $add: ["$stats.wins", "$stats.losses"] } } })
which will throw an exception:
"exception: Exception occurred during aggregation: exception: can't convert from BSON type Array to double "
as the aggregation operator cannot work inside arrays.
Bit Functions
Bit Functions perform bit manipulations between two expressions of any of the integer data type.
& Bitwise AND
The operator that compares bits and returns 1 when each bit equals 1. Otherwise, 0 is returned.
| Bitwise OR
The bitwise operator that compares bits and returns 1 when at least one of the bits equals 1. Otherwise, 0 is returned.
<< Left Shift
The bitwise operator that shifts all bits to the left by the specified number of positions.
>> Right Shift
The bitwise operator that shifts all bits to the right by the specified number of positions.
Example
REPLACE INTO zips
VALUE ({ abc:123 })
WHERE (uid << 1) = 16
Date and Timestamp Support
For a Date field value, it is possible to use Date('yyyy-MM-dd'), Date('yyyy-MM-dd HH:mm:ss') or Date('yyyy-MM-dd HH:mm:ss.SSS').
BSONTimestamp() creates the BSONTimestamp object and it takes two arguments BSONTimestamp(<timestamp string>, <inc value>) or BSONTimestamp(<seconds_since_Unix_epoch>, <inc value>)
BSONTimestamp(), Date(), and ISODate() calls without any arguments will initialize with the current time/date.
The ISODate function uses the UTC time zone while the Date and BSONTimestamp functions use the client time zone. The timestamp string specified in the Date and BSONTimestamp functions is parsed in the client time zone.
day() returns an integer that represents the day part of the specified date.
month() returns an integer that represents the month part of a specified date.
year() returns an integer that represents the year part of a specified date.
The Date and Timestamp formats below are also acceptable.
| Date() | MMM dd, yyyy yyyy-MM-dd yyyy-MM-dd HH:mm:ss yyyy-MM-dd HH:mm:ss.SSS |
| ISODate() | yyyy-MM-dd'T'HH:mm:ss±HH:mm yyyy-MM-dd'T'HH:mm:ss.SSS±HH:mm yyyy-MM-dd'T'HH:mm:ss.SSS'Z' yyyy-MM-dd'T'HH:mm:ss yyyy-MM-dd'T'HH:mm:ss'Z' yyyy-MM-dd |
| BSONTimestamp(<timestamp string>, <inc value>) | where the <timestamp string> is a string that respects the or yyyy-MM-dd HH:mm:ss.SSS |
| BSONTimestamp(<seconds_since_Unix_epoch>, <inc value>) | both arguments are integer numbers or valid strings that can be parsed to integer values |
Examples
INSERT INTO testDateTime
VALUES( { _id : 1, timestampField : Date('2011-02-10 09:30:00') } )
GO
UPDATE testDateTime
SET timestampField=Date('2013-02-10 09:30:00')
WHERE _id=1
GO
SELECT *
FROM testDateTime
WHERE timestampField=Date('2013-02-10 09:30:00')
GO
SELECT *
FROM testDateTime
WHERE timestampField = ISODate('2012-07-14T01:00:00+01:00')
GO
REPLACE INTO testDateTime
VALUE ({"timestampField" : ISODate('2012-07-14T01:00:00+01:00')})
WHERE dodad='this'
GO
Database References
Use the DBREF(refColl, idString, dbName) function. refColl is the name of the collection where the referenced document resides. idString is the value of the_id field of the referenced document. dbName is the name of the database where the referenced document resides.
Example
INSERT INTO child
VALUES({"mydad" : DBREF('Parent_collection','518d39f7ecde1f4f0ab442f2','Parent_DB')})
GO
Object IDs
Use the OBJECTID(objectIdString) function to specify an ObjectId.
Example
SELECT *
FROM city
WHERE _id=ObjectId('512d111f7af2119d877944b8')
Regular Expressions
Use the PATTERN(regExpr, flags) function. See java.util.regex.Pattern below the example for the list of flags.
Example
INSERT INTO testRegExpr (regexpr)
VALUES (PATTERN('.*ab.*', 0))
java.util.regex.Pattern
In the <flags> parameter of the PATTERN(regExpr,flags) the following number constants (and their combinations using an OR operation) can be used:
| Java Flag | Number Constant |
|---|---|
| CASE_INSENSITIVE | 2 |
| COMMENTS | 4 |
| MULTILINE | 8 |
| DOTALL | 32 |
For details on creating a pattern with flags, see the Java tutorial "Methods of the Pattern Class: Creating a Pattern with Flags" (http://docs.oracle.com/javase/tutorial/essential/regex/pattern.html).
Example:
Find text which has numbers inside (<flags> value of 0 means no flag is used)
SELECT teamName
FROM baseball
WHERE teamName=PATTERN('[0-9]',0)
The function PATTERN in an INSERT statement just gives the possibility of storing regular expression patterns in the database. Futher usage of it in PATTERN is not possible because the first parameter can only be a string constant but not a database field. The following example gives a "Unknown tree node" error:
INSERT INTO baseball(patt)
VALUES (pattern('[0-9]',0))
go
SELECT teamName
FROM baseball
WHERE teamName=pattern(patt,0)
go
-- unknown tree node error
BSON Data Types
Binary
Use HEXDATA(hexString) for BSON BinData of type 0.
Use UUID(uuidString) for UUID data, i.e. BSON BinData of type 3.
Use MD5(md5String) for MD5 data, i.e. BSON BinData of type 5.
Example
INSERT INTO testBinary (binData, uuidData, md5Data)
VALUES (HEXDATA('616B'), UUID('f81d4fae-7dec-11d0-a765-00a0c91e6bf7'), MD5('79054025255FB1A26E4BC422AEF54EB3'))
Code
Use CODE(jsCode) for a code object that is used to represent JavaScript code.
Example
INSERT INTO testCode (data)
VALUES (CODE('function f() { return 12; }'))
CodeWScope
This data type is not supported in MongoSQL. You can retrieve a CodeWScope field in a SELECT statement, but there is no support to INSERT or UPDATE such values.
MaxKey
Use MAXKEY() function for the BSON MaxKey object.
Example
INSERT INTO testMongoTypes (maxkey)
VALUES (MAXKEY())
MinKey
Use MINKEY() function for the BSON MinKey object.
Example
INSERT INTO testMongoTypes (minkey)
VALUES (MINKEY())
Configuration Functions
SELECT @@version
This function returns MongoDB version information.
Example
SELECT @@version
SELECT @@spid
This function returns the server process ID.
SELECT @@spid command will only work if you are an "admin" user. In MongoDB version 2.2, you need to be able to log in to the "admin" database. In MongoDB version 2.4, you need to have the clusterAdmin role in the "admin" database.
Example
SELECT @@spid
SELECT @@current_database
This function returns information on the current database context.
Example
USE test go SELECT @@current_database
SELECT @@username
This function returns the current user.
Example
SELECT @@username
Utility Statements
EXPLAIN <statement>
The EXPLAIN command is used to retrieve a high-level description of the plan that MongoDB uses to implement a specific SQL query. It reports on how the query uses database indices. Users may find the information it reports useful in optimizing database schemas and queries while debugging.
Example
EXPLAIN SELECT * FROM NewItems
USE database_name
This statement sets the database context and keeps this context for subsequent statements until the end of the session or another USE statement is encountered.
Example
USE baseball
See the SHOW Commands page for additional information about commands used to describe database schema objects.
JavaScript Functions
EVAL(JavaScript command) New in 17.0
The EVAL command takes a MongoDB JavaScript command and displays the result set in grid and tree views. Array data is returned in JSON format.
Examples
EVAL(db.baseball.find())
EVAL(db.baseball.explain().find())
EVAL(db.baseball.insert({"teamName" : "Yankees", "city" : "New York"}))
EVAL_ARRAY(JavaScript command) New in 17.0
The EVAL_ARRAY command takes a MongoDB JavaScript command and displays the result set in grid and tree views. Array data is flattened.
Example
EVAL_ARRAY(db.baseball.find())
Miscellaneous Functions
FIELD_EXISTS(fieldName)
This function checks if a field exists in the document. It is used in the WHERE clause.
Example
SELECT * FROM NewProducts WHERE FIELD_EXISTS(city)
ARRAY_SIZE(arrayFieldName)
This function returns the number of elements in an array field. It is used in the WHERE clause.
ARRAY_SIZE() can only be used with the "=" operator.
Example
SELECT * FROM NewProducts WHERE ARRAY_SIZE(city) = 12