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).
Anchor | ||||
---|---|---|---|---|
|
Table of Contents | ||
---|---|---|
|
Info |
---|
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.
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
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.
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'
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
Expressions:
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 [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.
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
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.
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]
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
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 ]
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.
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.
/ 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
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.
& 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
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').
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
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).
Example:
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.
MaxKey
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.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
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 |
...
Amazon Redshift registrations should be straight forward, and nearly identical to the PostgreSQL server registration process.
Contents
- General Tab
- Using Filters Options in Registration (Filter Tab)
- Using Advanced Properties (Advanced Tab)
- Using Driver Properties (Driver Tab)
- Server Permissions (Permissions Tab)
- Server Scripts (Script Tab)
- Shell Scripts (FluidShell Tab)
- Server Connections (Connection Monitor Tab)
General Tab
Tab Coloring
To Identify Server Environments, whether it is a Production, Development or Test, use Tab Coloring feature:
- Locate the Server in the Server Browser.
- Right-click on the Server - A Popup Menu Appears.
- Click on the Item which displays Server Properties.
- Based on the Server Type, you can edit the Tab Coloring option to identify your environments.
Using the Keyboard combination "CTRL + SHIFT + P", you may quickly access Server Properties and edit Tab Coloring options.
Using Filters Options in Registration (Filter Tab)
Edit the Server Properties, to use FILTER options for a Server in Aqua Data Studio:
- Locate the Server in the Server Browser.
- Right-click on the Server - A Popup Menu Appears.
- Click on the Item which displays Server Properties.
- Go to the Filter Tab and make changes in the Options.
- Filter Databases/Schemas: Allows the user to specify the main schema browser filter for databases or schemas, on whether to include or exclude all.
- Include Databases/Schemas: A list of databases/schemas to include after the main filter is applied.
- Exclude Databases/Schemas: A list of databases/schemas to exclude after the main filter is applied.
- Filter Objects: Allows the user to specify the main schema browser filter for object types, on whether to include or exclude all.
- Include Objects: A list of objects to include after the main filter is applied.
- Exclude Objects: A list of objects to exclude after the main filter is applied.
...
- You can Create, Edit or Delete Object Folders. Default Folders are Tables, System Tables, Views, Indexes, Triggers, Procedures, Functions.
- Sometimes you would like to view only specific Schemas. Use options to INCLUDE or EXCLUDE all Schemas and Folders.
- Enter the Schema or Folder name in the specified text area and Press OK. Current Connection will be terminated. Reconnect to see the changes.
- Filters are case sensitive.
- Filter Databases works on the list of databases inside the "Databases" folder.
- Filter Folders works on the list of folders inside a particular database. Example folders: Tables, View, Procedures, etc.
- Object Folder filter works on individual folders inside a particular database. The user can define filters for each of the Tables, Views, etc. folders.
Using the drop-down at the top of the Filter Tab, quickly change your Schema or Folder View.
Filter Examples:
If your intention is to show only the tables that start with XXPKD, then you'll need to change your main filter to "Exclude All", and secondary filter to "XXPKD*". Note the "" in front of the XXPKD pattern. Also, note that the filter pattern is case sensitive.
The filter shown below is set to exclude all databases except those that start with "aq". This is similar to the above example where all schemas, except the XXPKD schema, are excluded. Then, there is an "Object Folder" filter on the Tables folder, which excludes everything except those that start with "forum".
Using Advanced Properties (Advanced Tab)
Edit the Server Properties to use Advanced Properties in Aqua Data Studio:
- Locate the Server in the Server Browser.
- Right-click on the Server - A Popup Menu Appears.
- Click on the Item which displays Server Properties.
- Go to the Advanced Tab and make changes in the Options.
With options in Advanced properties of a Server, you can:
- Change Transaction Isolation Modes.
- Manage Driver Parameters.
- Enable Database Filters in Query Analyzer.
- Change Read Only / Read- Write Mode.
- Change Object Folder Display.
- Transaction Isolation Level: Support for setting Transaction Isolation Level- TRANSACTION_NONE - A constant indicating that transactions are not supported.- TRANSACTION_READ_COMMITTED - A constant indicating that dirty reads are prevented; non-repeatable reads and phantom reads can occur.- TRANSACTION_READ_UNCOMMITTED - A constant indicating that dirty reads, non-repeatable reads, and phantom reads can occur.- TRANSACTION_REPEATABLE_READ - A constant indicating that dirty reads and non-repeatable reads are prevented; phantom reads can occur.- TRANSACTION_SERIALIZABLE - A constant indicating that dirty reads, non-repeatable reads, and phantom reads are prevented.
- JDBC Escape Processing: Allows JDBC driver to perform escape sequence before sending the SQL code to the server.
- Always Display Column Name in Results: Allows to show/hide column names in query results.
- Enable DB Filters for Query Window: Enables/Disables db filters for query windows using this connection.
- Only Display Databases that User has Access to: Displays only the databases the current user (from the Login Name on the General tab) has access to within the schema browser.
- Fetch Size: Determines the Fetch Size (number of rows physically retrieved from the database at one time by the JDBC driver).
- Connections Pooled: Enables connection pooling. Read more about connection pooling here.
Wiki Markup *Force Auto-Commit{*}: When this option is selected, it sets auto-commit to TRUE for any Query Window to this server and disables the icons on the toolbar, regardless of the option in *File > Options > Query Analyzer > \[DATABASE\] > \[Auto Commit\]{*}.
- Data Warehouse Mode: Read Only or Read/Write
Wiki Markup *Object Folder Display:* \[Default\], \[Name\], \[Schema\].\[Name\] or \[Name\](\[Schema\]) sets how the Schemas will be displayed.
- Object Folder Schema Folder Grouping: Default, Yes or No. Schema Folder Grouping makes finding tables much easier because it groups them instead of showing each object individually.
Using the drop-down at the bottom of the Advanced Tab, quickly change your Object Display in the Server Browser.
Using Driver Properties (Driver Tab)
Edit the Drivers Properties to use Driver Parameters in Aqua Data Studio:
- Locate the Server in the Server Browser.
- Right-click on the Server - A Popup Menu Appears.
- Click on the Item which displays Server Properties.
- Go to the Driver Tab and make changes in the Options.
With options in Driver properties of a Server, you can edit/view the:
- Driver: This displays the JDBC driver used for this connection.
- Driver Parameters: This option is to allow a user to specify extra JDBC parameters that a user might want use in establishing a connection.
- Connection URL: This displays the connection URL for this server connection.
- Driver Parameters Quickfilter: This option is to allow a user to search JDBC parameters that a user might want to use in establishing a connection.
Server Permissions (Permissions Tab)
Edit the Permissions in Server Properties in Aqua Data Studio:
- Locate the Server in the Server Browser.
- Right-click on the Server - A Popup Menu Appears.
- Click on the Item which displays Server Properties.
- Go to the Permissions Tab and make changes in the Options.
With options in Permissions Tab of a Server, you can:
- Control Statement execution and Properties of the Query Analyzer, Table Data Editor, and Visual Editing.
- Grant / Revoke Permissions easily for executing DDL and DML Statements in the Query Analyzer.
- Change the behavior of the Table Data Editor for manipulating rows in Tables.
- Grant / Revoke Alters, Creates, Drops and View Properties of Visual Editing Tools.
Server Scripts (Script Tab)
Edit Scripts in Server Properties in Aqua Data Studio:
- Locate the Server in the Server Browser.
- Right-click on the Server - A Popup Menu Appears
- Click on the Item which displays Server Properties.
- Go to the Script Tab and make changes in the Options.
With scripts in the Script Tab of a Server, you can:
- Enter an SQL script that is executed each time a new connection is established.
- This allows the user to SET connection settings for a server connection. (eg: SET ROLE, ALTER SESSION SET XXX)
Shell Scripts (FluidShell Tab)
Edit Shell Scripts in Server Properties in Aqua Data Studio:
- Locate the Server in the Server Browser.
- Right-click on the Server - A Popup Menu Appears
- Click on the Item which displays Server Properties.
- Go to the FluidShell Tab and make changes in the Options.
With scripts in the FluidShell Tab of a Server, you can:
- Enter a FluidShell script that is executed each time a new FluidShell connection is established.
- This allows the user to cd (change directory) for a server connection. (eg: cd /scripts)
Server Connections (Connection Monitor Tab)
Enable the connection monitor to start an application thread that monitors the status of the connection associated with the Query Window.
With the connection monitor, you can:
- Set a ping operation that allows activity on the connection to prevent the connection from being terminated.
- Set an execute operation that executes the Query Window on a periodic basis.
- Set an auto-commit operation that checks how long the auto-commit has been disabled and whether the idle threshold has been exceeded. A warning threshold can also be configured so users can turn on auto-commit before it happens.
For more information, see Connection Monitor.