...

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.

...

DELETE FROM zips 
   WHERE city='CRAWFISH TRAP'

DELETE SQL Mapping

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.

...

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

...

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

...

ALTER COLLECTION products 
   RENAME FIELD dodad thingamabob

ALTER COLLECTION CAPPED SIZE

ALTER COLLECTION collection_name
CAPPED SIZE sizeInBytes

...

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 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 all_baseball_role PRIVILEGE DB sports COLLECTION baseball ACTIONS "find", "insert", "remove", "update"

...

DROP ROLE

MongoDB 2.6+ DROP ROLE role_name

...

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 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 newrole DB mydatabase TO ROLE comrole

Back to top

REVOKE PRIVILEGE FROM ROLE

...

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

...

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

Back to top

Arithmetic and Mathematical Functions

...

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

...

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

...

SELECT @@username

Back to top

Utility Statements

EXPLAIN <statement>

...

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 

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.

...

EVAL(db.baseball.find())

...

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

...

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.

...

EVAL_ARRAY(db.baseball.find()) 

Back to top

Miscellaneous Functions

FIELD_EXISTS(fieldName)

...

SELECT * 
   FROM NewProducts 
   WHERE ARRAY_SIZE(city) = 12

Back to top