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

Back to top

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

Back to top

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.

...

WHERE managerName.first = 'Joe'

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

...

expr | expr
expr & expr
expr << expr
expr >> expr
expr + expr
expr – expr
expr * expr
expr / expr
expr % expr

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

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

WHERE SQL MappingBack 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.

Example

SELECT city 
   FROM zips 
   WHERE pop > 100000 
   GROUP BY state 

Back to top

HAVING clause

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

Example

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.

Example

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.

Example

SELECT city 
   FROM zips LIMIT 10
GO

SELECT city
   FROM zips LIMIT 10 OFFSET 5
GO

...

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.

Example

SELECT dodad
   FROM newproduct
UNION
SELECT dodad 
   FROM newproductclone
GO

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.

Example

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.

Example

SELECT customerid
   FROM customers
INTERSECT
SELECT customerid
   FROM orders
GO

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.

Example

SELECT customerid
   FROM customers
EXCEPT
SELECT customerid
   FROM orders
GO

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

Examples

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

...

Default is UPDATE ALL. UPDATE ONE updates only the first document that matches the criteria. UPDATE updates the contents of a Collection.

Example

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

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.  

Example

...

...

...

...

...

...

...

...

...

...

...

...

...

UPSERT SQL Mapping

Back to top

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

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]

...

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

CREATE DATABASE webstore COLLECTION NewProducts

...

DROP DATABASE

DROP DATABASE db_name

DROP DATABASE removes the database and all of its Collections.

Example

DROP DATABASE webstore

Back to top

CREATE COLLECTION

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

...

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.

Example

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.

Example

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.

Example

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.

Example

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

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.

Example

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.

Example

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

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 generates a user for the MongoDB server.

Example

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

Example

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

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.

Example

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

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

Example

CREATE ROLE comrole PRIVILEGE DB mydatabase COLLECTION mycollection ACTIONS "find"

Example

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

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

Example

GRANT PRIVILEGE DB mydatabase COLLECTION mycollection ACTIONS "find" TO ROLE comrole

Example

GRANT PRIVILEGE DB sports COLLECTION test ACTIONS "find", "insert", "remove", "update" TO ROLE all_baseball_role

Example

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

Example

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

Example

REVOKE PRIVILEGE DB mydatabase COLLECTION mycollection ACTIONS "find" FROM ROLE comrole

Example

REVOKE PRIVILEGE DB sports COLLECTION test ACTIONS "insert", "remove", "update" FROM ROLE all_baseball_role

Example

REVOKE PRIVILEGE DB "user" COLLECTION "user" ACTIONS "dropCollection","dropRole","dropUser","emptycapped","enableProfiler"
FROM ROLE "test_deployment"

Back to top

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

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.

...

Multiplies one or more numbers and returns their product.

Example

SELECT itemID + prodID
    FROM products
    WHERE dodad='whirlygig4'

Back to top

Bit Functions

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

...

The bitwise operator that shifts all bits to the right by the specified number of positions.

Example

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

...

Examples

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.

Example

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

...

Object IDs

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

Example

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.

Example

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:

...

Find text which has numbers inside (<flags> value of 0 means no flag is used)

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:

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.

Example

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

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.

...

Use MAXKEY() function for the BSON MaxKey object.

Example

INSERT INTO testMongoTypes (maxkey) 
    VALUES (MAXKEY())

...

MinKey

Use MINKEY() function for the BSON MinKey object.

Example

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

...

Back to top

Configuration Functions

SELECT @@version

This function returns MongoDB version information.

Example

SELECT @@version

SELECT @@spid

This function returns the server process ID.

Example

SELECT @@spid

SELECT @@current_database

This function returns information on the current database context.

Example

USE test
go
SELECT @@current_database

SELECT @@username

This function returns the current user.

Example

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.

Example

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

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

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

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

The EVAL_ARRAY command takes a MongoDB JavaScript command and displays the result set in grid and tree views. Array data is flattened.

Example

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

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.

Example

SELECT * 
   FROM NewProducts 
   WHERE ARRAY_SIZE(city) = 12

Back to top