Versions Compared

Old Version 11

changes.mady.by.user Nadine Hinojosa

Saved on

compared with

New Version 12

changes.mady.by.user Nadine Hinojosa

Saved on

Key

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

...

Code Block
SELECT itemID + prodID
    FROM products
    WHERE dodad='whirlygig4'
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.

...

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

Example


Code Block
REPLACE INTO zips 
   VALUE ({ abc:123 })
   WHERE  (uid << 1) = 16


Back to top

Date and Timestamp Support

...

Date()MMM dd, yyyy

yyyy-MM-dd

yyyy-MM-dd HH:mm:ss

yyyy-MM-dd HH:mm:ss.SSS
 
ISODate()yyyy-MM-dd'T'HH:mm:ss±HH:mm

yyyy-MM-dd'T'HH:mm:ss.SSS±HH:mm

yyyy-MM-dd'T'HH:mm:ss.SSS'Z'

yyyy-MM-dd'T'HH:mm:ss

yyyy-MM-dd'T'HH:mm:ss'Z'

yyyy-MM-dd
BSONTimestamp(<timestamp string>, <inc value>)

where the <timestamp string> is a string that respects the

yyyy-MM-dd HH:mm:ss

or

yyyy-MM-dd HH:mm:ss.SSS

format

BSONTimestamp(<seconds_since_Unix_epoch>, <inc value>)

both arguments are integer numbers or valid strings that can be parsed to integer values

Examples



Code Block
INSERT INTO testDateTime
   VALUES( { _id : 1, timestampField : Date('2011-02-10 09:30:00') } )
GO


Code Block
UPDATE testDateTime 
   SET timestampField=Date('2013-02-10 09:30:00')
   WHERE _id=1
GO


Code Block
SELECT * 
   FROM testDateTime
   WHERE timestampField=Date('2013-02-10 09:30:00')
GO


Code Block
SELECT * 
   FROM testDateTime
   WHERE timestampField = ISODate('2012-07-14T01:00:00+01:00')
GO


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

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

Back to top
Object IDs
Use the OBJECTID(objectIdString) function to specify an ObjectId.
Example

 Code Block
SELECT * 
   FROM city 
   WHERE _id=ObjectId('512d111f7af2119d877944b8')
...


Back to top
Regular Expressions
...