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
  • SELECT
    • Subdocument and Array Fields
  • FROM
  • WHERE
  • GROUP BY
  • HAVING
  • ORDER BY
  • LIMIT and OFFSET
  • UNION | UNION ALL | INTERSECT | EXCEPT
• DML Syntax
  • INSERT
  • UPDATE
  • UPSERT
  • DELETE
• DDL Syntax
  • CREATE DATABASE
  • DROP DATABASE
  • CREATE COLLECTION
  • DROP COLLECTION
  • ALTER COLLECTION
    • DROP FIELD
    • RENAME FIELD
    • CAPPED SIZE
  • RENAME COLLECTION
  • CREATE INDEX
  • DROP INDEX
  • CREATE USER
  • ALTER USER
  • DROP USER
  • GRANT ROLE TO USER
  • REVOKE ROLE FROM USER
  • CREATE ROLE
  • DROP ROLE
  • GRANT PRIVILEGE TO ROLE
  • GRANT ROLE TO ROLE
  • REVOKE PRIVILEGE FROM ROLE
  • REVOKE ROLE FROM ROLE

...

• Supported Functions
  • Aggregate Functions
    • ADDTOSET
    • AVG
    • COUNT
    • FIRST
    • LAST
    • MAX
    • MIN
    • PUSH
    • SUM
  • Arithmetic and Mathematical Functions
    • / Division operator
    • - Minus operator
    • % or MOD Modulo operator
    • + Addition operator
    • * Multiplication operator
  • Bit Functions
    • & Bitwise AND
    • | Bitwise OR
    • << Left shift
    • >> Right shift
  • Date and Timestamp Support
  • Database References
  • Object IDs
  • Regular Expressions
  • BSON Data Types
  • Configuration Functions
  • Utility Statements
  • JavaScript Functions
  • • EVAL
    • EVAL_ARRAY
  • Miscellaneous Functions

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.

...

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

Back to top

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.

...

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:

...

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

...

SELECT FLATTEN * 
   FROM zips 
   WHERE city LIKE 'H%'

Back to top

JSON

JSON returns subdocuments and arrays in JSON format.

...

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.

...

SELECT FLATTEN_ARRAY * 
   FROM zips 
   WHERE city LIKE 'H%' 

Back to top

DISTINCT | ALL option

DISTINCT

The DISTINCT keyword returns only distinct (different) values. DISTINCT only supports querying one field based on MongoDB's specifications.

...

SELECT DISTINCT state 
   FROM zips

Back to top

ALL

ALL is default. Returns all values.

...

SELECT ALL state 
   FROM zips

...

COUNT(DISTINCT <field>)

An additional DISTINCT keyword syntax is supported only for the COUNT() aggregate function. 

...

SELECT COUNT(DISTINCT state) FROM zips

Back to top

FROM clause

Determines the specific dataset to examine to retrieve data. For MongoDB, this would be indicated as a specific Collection. 

...

Back to top

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.

...

SELECT * 
   FROM zips 
   WHERE city LIKE 'KE%' 
   AND pop > 2000

WHERE SQL Mapping

Back to top

GROUP BY clause

The GROUP BY clause is used in combination with aggregate functions to group the results by one or more documents.

...

Back to top

HAVING clause

Used in conjunction with GROUP BY, allows retreiving a subset of an aggregate function.

...

SELECT city, SUM(pop) AS pop 
   FROM zips 
   GROUP BY city 
   HAVING pop > 30000

Back to top

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.

...

SELECT city, state 
   FROM zips 
   WHERE pop > 50000 
   ORDER BY state

Back to top

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.

...

Back to top

UNION | UNION ALL | INTERSECT | EXCEPT clause

UNION

UNION can be used to combine the result sets of two or more SELECT statements.

...

Back to top

UNION ALL

UNION ALL can be used to combine the result sets of two or more SELECT statements and will include duplicate rows.

...

SELECT dodad
   FROM newproduct
UNION ALL
SELECT dodad 
   FROM newproductclone
GO

Back to top

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.

...

Back to top

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.

...

Back to top

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 zips 
   VALUES ({"city": "MEER", "loc": [-86.51599, 33.584199], "pop": 6055, "state": "CA", "_id": "99999"}) 

INSERT INTO zips (city) 
   VALUES ('CRANKTON')

INSERT INTO cities (name) 
   SELECT city FROM zips

INSERT SQL MappingBack to top

UPDATE statement

UPDATE [ALL|ONE] collection_name SET field_name1=value1[, field_name2=value2 …]
WHERE condition

...

UPDATE zips SET pop='626' 
   WHERE city='CANKTON' 
   AND state='LA'

UPDATE SQL Mapping

Back to top

UPSERT statement

REPLACE [ALL|ONE] [INTO] collection_name VALUE (json_document) WHERE condition

...

UPSERT SQL Mapping

Back to top

DELETE statement

DELETE [ALL|ONE] FROM collection_name [WHERE condition]

...

DELETE FROM zips 
   WHERE city='CRAWFISH TRAP'

DELETE SQL Mapping

Back to top

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 webstore COLLECTION NewProducts

...

DROP DATABASE

DROP DATABASE db_name

DROP DATABASE removes the database and all of its Collections.

...

DROP DATABASE webstore

Back to top

CREATE COLLECTION

CREATE [ CAPPED ] COLLECTION collection_name [ SIZE sizeInBytes ] [ MAX_CAP numOfDocuments ]

...

CREATE COLLECTION products

CREATE CAPPED COLLECTION logger
   SIZE 10240 MAX_CAP 10

...

DROP COLLECTION

DROP COLLECTION collection_name

DROP COLLECTION removes a given Collection and all of its contents. This is analogous to dropping a Table.

...

DROP COLLECTION products

Back to top

ALTER COLLECTION

ALTER COLLECTION modifies an existing Collection by deleting or modifying its contained documents. This is analogous to altering a Table.

Back to top

ALTER COLLECTION DROP FIELD

ALTER COLLECTION collection_name
DROP FIELD field_name

This alters a Collection by dropping a specific field.

...

ALTER COLLECTION products 
   DROP FIELD widget

Back to top

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.

...

ALTER COLLECTION products 
   RENAME FIELD dodad thingamabob

Back to top

ALTER COLLECTION CAPPED SIZE

ALTER COLLECTION collection_name
CAPPED SIZE sizeInBytes

This command converts a non-capped collection to a capped collection.

...

ALTER COLLECTION products 
   CAPPED SIZE 2056

Back to top

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.

...

RENAME COLLECTION products TO NewProducts

Back to top

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.

...

CREATE UNIQUE INDEX myIndex ON NewItems(itemID)

Back to top

DROP INDEX

DROP INDEX index_name ON collection_name

DROP INDEX removes an Index for a specified Collection.

...

DROP INDEX myIndex ON NewItems

Back to top

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/)

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

MongoDB 2.2 CREATE USER user_name GRANT [ READWRITE | READ ] IDENTIFIED BY <password>

...

CREATE USER jpizzle 
   GRANT READWRITE, USERADMIN IDENTIFIED BY '1337@55H@X0|2'

Back to top

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/)

...

ALTER USER jpizzle IDENTIFIED BY '1337@55H@X0|2'

...

DROP USER

DROP USER user_name

DROP USER removes a user for the MongoDB Server

...

DROP USER jpizzle

Back to top

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.

...

GRANT ROLE comrole DB mydatabase TO USER jpizzle

Back to top

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.

...

REVOKE ROLE comrole DB mydatabase FROM USER jpizzle

Back to top

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/)

...

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

...

DROP ROLE comrole

Back to top

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/)

...

GRANT PRIVILEGE DB "admin" COLLECTION "baseball" Actions "createCollection","createIndex","createRole","createUser","dropCollection"
TO ROLE "test_deployment"

Back to top

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/)

...

GRANT ROLE newrole DB mydatabase TO ROLE comrole

Back to top

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/)

...

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/)

...

REVOKE ROLE newrole DB mydatabase FROM ROLE comrole

Back to top

Supported Functions

A limited set of MongoDB's Aggregate and Math functions (http://docs.mongodb.org/manual/reference/aggregation/#_S_group) are available.

Aggregate Functions

ADDTOSET

Returns an array of all the values found in the selected field among the documents in that group. Every unique value only appears once in the result set. There is no ordering guarantee for the output documents.

AVG

Returns the average of the values.

COUNT

Returns the number of instances of the value.

FIRST

Returns the first instance of the value.

LAST

Returns the last instance of the value.

MAX

Returns the highest value encountered.

MIN

Returns the lowest value encountered.

PUSH

Returns an array of all the values found in the selected field among the documents in that group. A value may appear more than once in the result set if more than one field in the grouped documents has that value.

SUM

Sums the values.

Example

SELECT city, SUM(pop) FROM zips GROUP BY city

Back to top

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.

...

Back to top

Bit Functions

Bit Functions perform bit manipulations between two expressions of any of the integer data type.

...

REPLACE INTO zips 
   VALUE ({ abc:123 })
   WHERE  (uid << 1) = 16

Back to top

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').

...

INSERT INTO testDateTime
   VALUES( { _id : 1, timestampField : Date('2011-02-10 09:30:00') } )
GO

UPDATE testDateTime 
   SET timestampField=Date('2013-02-10 09:30:00')
   WHERE _id=1
GO

SELECT * 
   FROM testDateTime
   WHERE timestampField=Date('2013-02-10 09:30:00')
GO

SELECT * 
   FROM testDateTime
   WHERE timestampField = ISODate('2012-07-14T01:00:00+01:00')
GO

REPLACE INTO testDateTime
   VALUE ({"timestampField" : ISODate('2012-07-14T01:00:00+01:00')}) 
   WHERE dodad='this'
GO

Back to top

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.

...

INSERT INTO child
   VALUES({"mydad" : DBREF('Parent_collection','518d39f7ecde1f4f0ab442f2','Parent_DB')})
GO

Back to top

Object IDs

Use the OBJECTID(objectIdString) function to specify an ObjectId.

...

SELECT * 
   FROM city 
   WHERE _id=ObjectId('512d111f7af2119d877944b8')

Back to top

Regular Expressions

Use the PATTERN(regExpr, flags) function. See java.util.regex.Pattern below the example for the list of flags.

...

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:

...

INSERT INTO baseball(patt) 
   VALUES (pattern('[0-9]',0))
go
SELECT teamName 
   FROM baseball 
   WHERE teamName=pattern(patt,0)
go
-- unknown tree node error

Back to top

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.

...

INSERT INTO testMongoTypes (minkey) 
    VALUES (MINKEY())

Back to top

Configuration Functions

SELECT @@version

This function returns MongoDB version information.

...

SELECT @@username

Back to top

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.

...

USE baseball

See the SHOW Commands page for additional information about commands used to describe database schema objects.

Back to top

JavaScript Functions

EVAL(JavaScript command) New in 17.0 

...

EVAL(db.baseball.find())

EVAL(db.baseball.explain().find())

EVAL(db.baseball.insert({"teamName" : "Yankees", "city" : "New York"}))

Back to top

EVAL_ARRAY(JavaScript command) New in 17.0

...

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.

...

SELECT * 
   FROM NewProducts 
   WHERE ARRAY_SIZE(city) = 12

Back to top