Versions Compared

Old Version 24

changes.mady.by.user Nadine Hinojosa

Saved on

compared with

New Version 25

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

Anchor
backtotop
backtotop

Table of Contents
stylenone

...

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

...

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.

...

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

Back to top

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.

...

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

Back to top

DISTINCT | ALL option

DISTINCT

...

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.

...

Code Block
SELECT ALL state 
   FROM zips

Back to top

COUNT(DISTINCT <field>)

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

...

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

...

Info

There is no JOIN support in MongoSQL. Only one collection can be specified in a FROM statement. The following query:

SELECT * FROM baseball A, basketball B WHERE city IN ['Chicago'] AND A.city=B.city

would generate an error.

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.

...

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.

...

Info

Each document can have a different set of fields in MongoDB. This is very different from a RDBMS where a table has a defined set of columns and each record has the same set of columns. In MongoDB, a field may exist for one record but not for another record. A WHERE filter can cause some fields to become non-existing. Our implementation of MongoSQL is limited by the behaviors of MongoDB. Consider this example:

SELECT foo FROM baseball WHERE managerName.first='Joe' GROUP BY foo, bar

The MongoShell equivalent for this query, as it is behaves on Aqua Data Studio is:

db.baseball.aggregate({$group: {"_id": {foo:"$foo", bar: "$bar"}}}, {$project: {foo: "$_id.foo", bar: "$_id.bar", _id:0}}).result

which returns a single row with an empty object:

[ { } ]

Back to top

HAVING clause

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

...

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

...

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

LIMIT and OFFSET apply to the documents being queried, not to the flattened records when the FLATTEN_ARRAY option is used.

Consider the following result set.

SELECT teamName, colors FROM baseball

 teamName     colors                         
 -----------  ------------------------------
 Cubs         [ "blue", "white" ]            
 Cardinals    [ "red", "white" ]             
 Yankees      [ "navy", "white" ]            
 Mets         [ "blue", "white" ]            
 Giants       [ "orange", "black" ]          
 Orioles      [ "orange", "black", "white" ]
 Angels       [ "red", "white" ]             
 A's          [ "green", "gold", "white" ]   
 Rays         [ "blue", "white" ]            
 White Sox    [ "black", "silver", "white" ]

SELECT FLATTEN_ARRAY teamName, colors FROM baseball LIMIT 1 OFFSET 1

 teamName     colors    
 -----------  ---------
 Cardinals    red       
 Cardinals    white     

The query retrieves one document with an offset of 1 and then flattens its fields.

Back to top

UNION | UNION ALL | INTERSECT | EXCEPT clause

...

Info

Nested UNION, EXCEPT, and INTERSECT are not supported.

...

UNION ALL

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

...

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

...

Info

Nested UNION, EXCEPT, and INTERSECT are not supported.

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.

...

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 Mapping

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

Back to top

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

Back to top

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.

Back to top

ALTER COLLECTION DROP FIELD

...

Code Block
ALTER COLLECTION products 
   DROP FIELD widget

Back to top

ALTER COLLECTION RENAME FIELD

...

Code Block
ALTER COLLECTION products 
   RENAME FIELD dodad thingamabob

Back to top

ALTER COLLECTION CAPPED SIZE

...

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 UNIQUE INDEX myIndex ON NewItems(itemID)

...

DROP INDEX
DROP INDEX index_name ON collection_name
...
 Code Block
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>]
...
 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'
Back to top
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>
...