Page History

Math expressions ( + , - , *, / , % ) are supported on SELECT fields ( e.g. select itemID + prodID from NewItems ) and inside WHERE filters (e.g. select * from NewItems where itemID + prodID < 1000 ).

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

SELECT city 
   FROM zips 
   WHERE pop > 100000 
   GROUP BY state 

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:

[ { } ]

On an aggregate SELECT query (i.e. when the GROUP BY clause is present), the WHERE clause filters the documents before performing the GROUP BY operation while the HAVING clause filters the result set after the aggregation step.  In the WHERE clause, the collection's field names are used when processing the WHERE filters.  However, the field names specified in the HAVING clause are matched with the alias names specified in the SELECT fields first when processing the HAVING filters.

Examples:

SELECT worldChampionships+1 as worldChampionships FROM baseball
WHERE worldChampionships=9

The above query returns the document where the value of the worldChampionships field is 9.

SELECT worldChampionships+1 as worldChampionships FROM baseball
GROUP BY worldChampionships HAVING worldChampionships=9

The above query uses an alias worldChampionships for the value of worldChampionships+1.  TheworldChampionships field in the HAVING clause refers to the value of worldChampionships+1.  Therefore, the query returns the document where worldChampionships+1=9.

SELECT worldChampionships+1 as worldChampionships FROM baseball
GROUP BY worldChampionships HAVING worldChampionships=10

The above query returns the document where worldChampionships+1=10.

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

ORDER BY columnIndex cannot be used with a SELECT * query. This limitation occurs because the $sort operator from MongoDB's Aggregation Framework requires a field name argument for the order-key parameter.

If ORDER BY is used on a query that has a GROUP BY clause or contains aggregate functions or arithmetic expressions inside the WHAT selector, then the order-by-key should match the name of the column that will be displayed in the result set. As an alternative, a column index can be used inside the ORDER BY clause:

SELECT a+1 FROM order_coll ORDER BY a+1

or

SELECT city, avg(ranking), sum(ranking), count(ranking) FROM baseball GROUP BY city ORDER BY 3

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

SELECT city 
   FROM zips LIMIT 10
GO

SELECT city
   FROM zips LIMIT 10 OFFSET 5
GO

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.

SELECT dodad
   FROM newproduct
UNION
SELECT dodad 
   FROM newproductclone
GO

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.

Nested UNION, EXCEPT, and INTERSECT are not supported.