Versions Compared

Old Version 26

changes.mady.by.user Nadine Hinojosa

Saved on

compared with

New Version Current

changes.mady.by.user Nadine Hinojosa

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

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

Back to top

FLATTEN | JSON | FLATTEN_ARRAY option

...

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

Back to top

JSON

JSON returns subdocuments and arrays in JSON format.

...

Info

The following DISTINCT select statements are not supported. They will generate an error.

SELECT DISTINCT * FROM zips
GO

SELECT DISTINCT city, state, pop FROM zips
GO

Back to top

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

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.

...

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

...

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.

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.

...

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

...

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

...

Info

REPLACE INTO zips VALUE({"city": "GRONTOWN", "loc": [-92.533765, 30.025565], "pop": 83, "state": "LA"}) WHERE city='GRONTOWN'

UPSERT SQL Mapping

Back to top

DELETE statement

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

...

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

...

Code Block
CREATE DATABASE webstore COLLECTION NewProducts

...

DROP DATABASE
DROP DATABASE db_name
...
 Code Block
DROP DATABASE webstore
Back to top
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
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.
...
 Code Block
ALTER COLLECTION products 
   CAPPED SIZE 2056
Back to top
RENAME COLLECTION
RENAME COLLECTION collection_name TO new_name [ DROP_TARGET ]
...
 Code Block
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]
...
 Code Block
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>]
...
 Code Block
ALTER USER jpizzle IDENTIFIED BY '1337@55H@X0|2'
...
DROP USER
DROP USER user_name
...
 Code Block
DROP USER jpizzle
Back to top
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
Back to top
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
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>]
...
 Code Block
CREATE ROLE all_baseball_role PRIVILEGE DB sports COLLECTION baseball ACTIONS "find", "insert", "remove", "update"
Back to top
DROP ROLE
MongoDB 2.6+ DROP ROLE role_name
...
 Code Block
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>
...
 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
Back to top
REVOKE PRIVILEGE FROM ROLE
...
 Code Block
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>
...
 Code Block
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.
...
 Code Block
SELECT city, SUM(pop) FROM zips GROUP BY city
Back to top
Arithmetic and Mathematical Functions
...
 Info
Operators from the Aggregation Framework have some limitations. For example, given the baseball collection containing documents like:

{ "teamName" : "Cardinals",
  "city" : "St. Louis",
  "division" : "NL Central",
  "ranking" : 1,
  "managerName" : { "first" : "Mike", "last" : "Matheny" },
  "colors" : [ "red", "white" ],
  "worldChampionships" : 11,
  "stats" : [ { "year" : 2010, "wins" : 86, "losses" : 76, "winPercentage" : 0.531 },
              { "year" : 2011, "wins" : 90, "losses" : 72, "winPercentage" : 0.556 },
              { "year" : 2012, "wins" : 88, "losses" : 74, "winPercentage" : 0.543 }
            ]
} 

The query:

SELECT stats.wins + stats.losses FROM baseball

will translate to

db.baseball.aggregate({$project: { "sum": { $add: ["$stats.wins", "$stats.losses"] } } })

which will throw an exception:

"exception: Exception occurred during aggregation: exception: can't convert from BSON type Array to double "

as the aggregation operator cannot work inside arrays.
Back to top
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
Back to top
Date and Timestamp Support
...
 Code Block
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.
...
 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')
Back to top
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 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.
...
 Code Block
INSERT INTO testMongoTypes (minkey) 
    VALUES (MINKEY())
Back to top
Configuration Functions
SELECT @@version
...
 Code Block
SELECT @@username
Back to top
Utility Statements
EXPLAIN <statement>
...
 Code Block
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 
...
 Code Block
EVAL(db.baseball.insert({"teamName" : "Yankees", "city" : "New York"}))
Back to top
EVAL_ARRAY(JavaScript command) New in 17.0
...
 Code Block
EVAL_ARRAY(db.baseball.find()) 
...
Miscellaneous Functions
FIELD_EXISTS(fieldName)
...
 Code Block
SELECT * 
   FROM NewProducts 
   WHERE ARRAY_SIZE(city) = 12
Back to top