Page History
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).Contents
...
- Query Syntax
- DML Syntax
- DDL Syntax
...
Anchor | ||||
---|---|---|---|---|
|
Table of Contents | ||
---|---|---|
|
Info |
---|
...
...
- / Division operator
- - Minus operator
- % or MOD Modulo operator
- + Addition operator
- * Multiplication operator
...
...
...
Keywords are not case-sensitive. In this document, keywords such as SELECT are capitalized for illustration purposes. |
Info |
---|
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:
Code Block |
---|
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
Code Block |
---|
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:
Code Block |
---|
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:
Code Block |
---|
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:
Code Block |
---|
...
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
Code Block |
---|
SELECT FLATTEN *
FROM zips
WHERE city LIKE 'H%' |
...
JSON
JSON returns subdocuments and arrays in JSON format.
Example
Code Block |
---|
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
Code Block |
---|
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
Code Block |
---|
SELECT DISTINCT state
FROM zips |
Info |
---|
MongoDB flattens an array field when processing the DISTINCT command. |
Info |
---|
The following DISTINCT select statements are not supported. They will generate an error. |
ALL
ALL is default. Returns all values.
Example
Code Block |
---|
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
Code Block |
---|
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.
...
where the database name is qa and the collection name is testArray.
Example
Code Block |
---|
SELECT *
FROM zips |
Info |
---|
There is no JOIN support in MongoSQL. Only one collection can be specified in a FROM statement. The following query: |
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.
...
WHERE managerName.first = 'Joe'
Info |
---|
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
...
expr | expr
expr & expr
expr << expr
expr >> expr
expr + expr
expr – expr
expr * expr
expr / expr
expr % expr
Info |
---|
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
Code Block |
---|
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
Code Block |
---|
SELECT city
FROM zips
WHERE pop > 100000
GROUP BY state |
Info |
---|
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: |
HAVING clause
Used in conjunction with GROUP BY, allows retreiving a subset of an aggregate function.
Info |
---|
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. |
Example
Code Block |
---|
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.
Info |
---|
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. |
Example
Code Block |
---|
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
Code Block |
---|
SELECT city
FROM zips LIMIT 10
GO
SELECT city
FROM zips LIMIT 10 OFFSET 5
GO |
...
Info |
---|
LIMIT and OFFSET apply to the documents being queried, not to the flattened records when the FLATTEN_ARRAY option is used. |
UNION | UNION ALL | INTERSECT | EXCEPT clause
UNION
UNION can be used to combine the result sets of two or more SELECT statements.
Example
Code Block |
---|
SELECT dodad
FROM newproduct
UNION
SELECT dodad
FROM newproductclone
GO |
Info |
---|
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. |
Info |
---|
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
Code Block |
---|
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
Code Block |
---|
SELECT customerid
FROM customers
INTERSECT
SELECT customerid
FROM orders
GO |
Info |
---|
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. |
Info |
---|
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
Code Block |
---|
SELECT customerid
FROM customers
EXCEPT
SELECT customerid
FROM orders
GO |
Info |
---|
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. |
Info |
---|
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 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.
Info |
---|
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. |
Info |
---|
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: |
Examples
Code Block |
---|
INSERT INTO zips
VALUES ({"city": "MEER", "loc": [-86.51599, 33.584199], "pop": 6055, "state": "CA", "_id": "99999"}) |
Code Block |
---|
INSERT INTO zips (city)
VALUES ('CRANKTON') |
Code Block |
---|
INSERT INTO cities (name)
SELECT city FROM zips |
UPDATE statement
UPDATE [ALL|ONE] collection_name SET field_name1=value1[, field_name2=value2 …]
WHERE condition
...
Default is UPDATE ALL. UPDATE ONE updates only the first document that matches the criteria. UPDATE updates the contents of a Collection.
Info |
---|
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
Code Block |
---|
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.
Info |
---|
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
Info |
---|
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
Code Block |
---|
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]
...
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
Code Block |
---|
CREATE DATABASE webstore COLLECTION NewProducts |
...
DROP DATABASE
DROP DATABASE db_name
DROP DATABASE removes the database and all of its Collections.
Example
Code Block |
---|
DROP DATABASE webstore |
CREATE COLLECTION
CREATE [ CAPPED ] COLLECTION collection_name [ SIZE sizeInBytes ] [ MAX_CAP numOfDocuments ]
...
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.
Info |
---|
As a limitation of MongoDB, you cannot delete documents from a CAPPED collection. To remove the collection entirely, use DROP COLLECTION. |
Example
Code Block |
---|
CREATE COLLECTION products |
...
Code Block |
---|
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
Code Block |
---|
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
Code Block |
---|
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
Code Block |
---|
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
Code Block |
---|
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
Code Block |
---|
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
Code Block |
---|
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
Code Block |
---|
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/)
Info |
---|
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
Info |
---|
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>
Info |
---|
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
Code Block |
---|
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
Code Block |
---|
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
Code Block |
---|
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
Code Block |
---|
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
Code Block |
---|
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
Code Block |
---|
CREATE ROLE comrole PRIVILEGE DB mydatabase COLLECTION mycollection ACTIONS "find" |
Example
Code Block |
---|
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
Code Block |
---|
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
Code Block |
---|
GRANT PRIVILEGE DB mydatabase COLLECTION mycollection ACTIONS "find" TO ROLE comrole |
Example
Code Block |
---|
GRANT PRIVILEGE DB sports COLLECTION test ACTIONS "find", "insert", "remove", "update" TO ROLE all_baseball_role |
Example
Code Block |
---|
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
Code Block |
---|
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
Code Block |
---|
REVOKE PRIVILEGE DB mydatabase COLLECTION mycollection ACTIONS "find" FROM ROLE comrole |
Example
Code Block |
---|
REVOKE PRIVILEGE DB sports COLLECTION test ACTIONS "insert", "remove", "update" FROM ROLE all_baseball_role |
Example
Code Block |
---|
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
Code Block |
---|
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
Code Block |
---|
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.
...
Multiplies one or more numbers and returns their product.
Example
Code Block |
---|
SELECT itemID + prodID
FROM products
WHERE dodad='whirlygig4' |
Info |
---|
Operators from the Aggregation Framework have some limitations. For example, given the baseball collection containing documents like: |
Bit Functions
Bit Functions perform bit manipulations between two expressions of any of the integer data type.
...
The bitwise operator that shifts all bits to the right by the specified number of positions.
Example
Code Block |
---|
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').
...
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
Code Block |
---|
INSERT INTO testDateTime
VALUES( { _id : 1, timestampField : Date('2011-02-10 09:30:00') } )
GO |
Code Block |
---|
UPDATE testDateTime
SET timestampField=Date('2013-02-10 09:30:00')
WHERE _id=1
GO |
Code Block |
---|
SELECT *
FROM testDateTime
WHERE timestampField=Date('2013-02-10 09:30:00')
GO |
Code Block |
---|
SELECT *
FROM testDateTime
WHERE timestampField = ISODate('2012-07-14T01:00:00+01:00')
GO |
Code Block |
---|
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
Code Block |
---|
INSERT INTO child
VALUES({"mydad" : DBREF('Parent_collection','518d39f7ecde1f4f0ab442f2','Parent_DB')})
GO |
...
Object IDs
Use the OBJECTID(objectIdString) function to specify an ObjectId.
Example
Code Block |
---|
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
Code Block |
---|
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).
...
Find text which has numbers inside (<flags> value of 0 means no flag is used)
Code Block |
---|
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:
Code Block |
---|
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
Code Block |
---|
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
Code Block |
---|
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.
...
Use MAXKEY() function for the BSON MaxKey object.
Example
Code Block |
---|
INSERT INTO testMongoTypes (maxkey)
VALUES (MAXKEY()) |
...
MinKey
Use MINKEY() function for the BSON MinKey object.
Example
Code Block |
---|
INSERT INTO testMongoTypes (minkey)
VALUES (MINKEY()) |
...
Configuration Functions
SELECT @@version
This function returns MongoDB version information.
Example
Code Block |
---|
SELECT @@version |
SELECT @@spid
This function returns the server process ID.
Info |
---|
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
Code Block |
---|
SELECT @@spid |
SELECT @@current_database
This function returns information on the current database context.
Example
Code Block |
---|
USE test
go
SELECT @@current_database |
SELECT @@username
This function returns the current user.
Example
Code Block |
---|
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
Code Block |
---|
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
Code Block |
---|
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.00
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
Code Block |
---|
EVAL(db.baseball.find()) |
Code Block |
---|
EVAL(db.baseball.explain().find()) |
Code Block |
---|
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
Code Block |
---|
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
Code Block |
---|
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.
Info |
---|
ARRAY_SIZE() can only be used with the "=" operator. |
Example
Code Block |
---|
SELECT *
FROM NewProducts
WHERE ARRAY_SIZE(city) = 12 |