Versions Compared

Old Version 8

changes.mady.by.user Nadine Hinojosa

Saved on

compared with

New Version 9

changes.mady.by.user Nadine Hinojosa

Saved on

Key

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

...

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.

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.

...

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

The Fields list displayed within Aqua Data Studio's Schema Browser for a collection is generated by examining the first document in its collection. After executing a REPLACE INTO that includes fields which are new to the collection, the Schema Browser may not display these new fields. By examining the _id value next to the Fields node in the Schema Browser you can determine which document is being used to generate the Fields list.

Example


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

...

Default is DELETE ALL. If there is a WHERE criteria, DELETE ONE will delete only the first document that matches the criteria.

Example


Code Block
DELETE FROM zips 
   WHERE city='CRAWFISH TRAP'


DELETE SQL Mapping

Back to top

...

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


Code Block
CREATE DATABASE webstore COLLECTION NewProducts


Back to top

DROP DATABASE

DROP DATABASE db_name

DROP DATABASE removes the database and all of its Collections.

Example


Code Block
DROP DATABASE webstore


Back to top

CREATE COLLECTION

...

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.

Info

As a limitation of MongoDB, you cannot delete documents from a CAPPED collection. To remove the collection entirely, use DROP COLLECTION.

Example



Code Block
CREATE COLLECTION products

...


 Code Block
CREATE CAPPED COLLECTION logger
   SIZE 10240 MAX_CAP 10

Back to top

DROP COLLECTION
...
DROP COLLECTION removes a given Collection and all of its contents. This is analogous to dropping a Table.
Example

 Code Block
DROP COLLECTION products

Back to top
ALTER COLLECTION
...
This alters a Collection by dropping a specific field.
Example

 Code Block
ALTER COLLECTION products 
   DROP FIELD widget

Back to top
ALTER COLLECTION RENAME FIELD
...
This alters a Collection by renaming an existing field.
Example

 Code Block
ALTER COLLECTION products 
   RENAME FIELD dodad thingamabob

Back to top
ALTER COLLECTION CAPPED SIZE
...
This command converts a non-capped collection to a capped collection.
Example

 Code Block
ALTER COLLECTION products 
   CAPPED SIZE 2056

Back to top
RENAME COLLECTION
...
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

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

 Code Block
CREATE UNIQUE INDEX myIndex ON NewItems(itemID)

Back to top
DROP INDEX
DROP INDEX index_name ON collection_name
DROP INDEX removes an Index for a specified Collection.
Example

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