Page History
...
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], ...
...
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
...
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.
...
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.
...
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.
...
GROUP BY clause
The GROUP BY clause is used in combination with aggregate functions to group the results by one or more documents.
...
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.
...
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.
...
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.
...
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.
...
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) …]
...
UPDATE statement
UPDATE [ALL|ONE] collection_name SET field_name1=value1[, field_name2=value2 …]
WHERE condition
...
UPSERT statement
REPLACE [ALL|ONE] [INTO] collection_name VALUE (json_document) WHERE condition
...
DELETE statement
DELETE [ALL|ONE] FROM collection_name [WHERE condition]
...
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]
...
Code Block |
---|
CREATE DATABASE webstore COLLECTION NewProducts |
DROP DATABASE
DROP DATABASE db_name
DROP DATABASE removes the database and all of its Collections.
...
Code Block |
---|
DROP DATABASE webstore |
CREATE COLLECTION
CREATE [ CAPPED ] COLLECTION collection_name [ SIZE sizeInBytes ] [ MAX_CAP numOfDocuments ]
...
Code Block |
---|
CREATE CAPPED COLLECTION logger SIZE 10240 MAX_CAP 10 |
DROP COLLECTION
DROP COLLECTION collection_name
...
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
...
Code Block |
---|
ALTER COLLECTION products DROP FIELD widget |
ALTER COLLECTION RENAME FIELD
ALTER COLLECTION collection_name
RENAME FIELD old_field_name new_field_name
...
Code Block |
---|
ALTER COLLECTION products RENAME FIELD dodad thingamabob |
ALTER COLLECTION CAPPED SIZE
ALTER COLLECTION collection_name
CAPPED SIZE sizeInBytes
...
Code Block |
---|
ALTER COLLECTION products CAPPED SIZE 2056 |
RENAME COLLECTION
RENAME COLLECTION collection_name TO new_name [ DROP_TARGET ]
...
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]
...
Code Block |
---|
CREATE UNIQUE INDEX myIndex ON NewItems(itemID) |
DROP INDEX
DROP INDEX index_name ON collection_name
...
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>]
...
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>]
...
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
...
Code Block |
---|
DROP USER jpizzle |
GRANT ROLE TO USER
MongoDB 2.6+ GRANT ROLE <role_name> [DB <db>] TO USER <user>
...
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>
...
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> ...]
...
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
...
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>
...
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>
...
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>
...
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>
...
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.
...
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.
...
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.
...
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').
...
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.
...
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.
...
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.
...
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:
...
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.
...
Code Block |
---|
INSERT INTO testMongoTypes (minkey) VALUES (MINKEY()) |
Configuration Functions
SELECT @@version
This function returns MongoDB version information.
...
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.
...
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
...
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.
...
Code Block |
---|
SELECT * FROM NewProducts WHERE ARRAY_SIZE(city) = 12 |
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.