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).
...
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.
...
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
...
Code Block |
---|
SELECT FLATTEN * FROM zips WHERE city LIKE 'H%' |
JSON
JSON returns subdocuments and arrays in JSON format.
...
Info |
---|
The following DISTINCT select statements are not supported. They will generate an error. |
ALL
ALL is default. Returns all values.
...
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.
...
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.
...
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 |
---|
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 |
---|
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 |
---|
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.
...
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
...
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
...
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]
...
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.
...
Code Block |
---|
CREATE DATABASE webstore COLLECTION NewProducts |
...
DROP DATABASE
DROP DATABASE db_name
...
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 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
...
Code Block |
---|
DROP USER jpizzle |
GRANT ROLE TO USER
MongoDB 2.6+ GRANT ROLE <role_name> [DB <db>] TO USER <user>
...
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.
...
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.
...
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.
...
Code Block |
---|
EVAL_ARRAY(db.baseball.find()) |
...
Miscellaneous Functions
FIELD_EXISTS(fieldName)
...