Page History
...
Table of Contents |
---|
WHERE SQL Mapping
MongoDB | ADS MongoSQL |
---|---|
db.baseball.find( { colors: "white" } ) | SELECT * FROM baseball WHERE colors = 'white' |
db.baseball.find( { colors: { $all: [ "orange", "black" ] } } ) Note: $all selects the documents where the field holds an array and contains all specified elements. | SELECT * FROM baseball |
db.baseball.find( { colors: { $all: [ "orange", "black" ], $size: 2 } } ) Note: $size matches any array with the number of elements specified. | SELECT * FROM baseball or |
db.baseball.find( { "stats.year": { "$all": [ 2010, 2011 ] } } ) | SELECT * FROM baseball WHERE stats.year CONTAINS_ALL ( 2010, 2011 ) |
db.baseball.find( { stats: { $elemMatch: { wins: { $gt: 80 }, winPercentage: { $gt: 0.58 } } } } ) Note: $elemMatch matches more than one component within an array element. | SELECT * FROM baseball WHERE stats.wins > 80 AND stats.winPercentage > 0.58 |
Other WHERE Support
MongoDB | ADS MongoSQL |
---|---|
db.baseball.find( { city: { $in: [ "New York", "Chicago" ] } } ) Note: $in selects the documents where the field value equals any value in the specified list. | SELECT * FROM baseball WHERE city IN ( 'New York', 'Chicago') or SELECT * FROM baseball WHERE city = 'New York' OR city = 'Chicago' |
db.baseball.find( { worldChampionships: { $exists: true, $gt: 10 } } ) Note: This query returns all documents where the worldChampionships field exists and its value > 10. | SELECT * FROM baseball WHERE FIELD_EXISTS(worldChampionships) AND worldChampionships > 10 |
db.baseball.find( { worldChampionships: { $exists: false } } ) Note: This query returns all documents that do not contain the worldChampionships field. | SELECT * FROM baseball WHERE NOT FIELD_EXISTS(worldChampionships) |
db.baseball.find( ... ).explain() | EXPLAIN SELECT * FROM baseball ... |
INSERT Support
MongoDB | ADS MongoSQL |
---|---|
db.baseball.insert( { "teamName": "Dodgers", "city": "Los Angeles", "division":"NL", "managerName": { "first":"Joe", "last":"Torre" }, "colors": [ "blue", "white" ], "worldChampionships": 6, "stats": [ { "year":2010, "wins":80, "losses":82, "winPercentage":0.494 }, { "year":2011, "wins":82, "losses":79, "winPercentage":0 }, { "year":2012, "wins":0, "losses":0, "winPercentage":0 } ] } ) | INSERT INTO baseball VALUES ( { "teamName":"Dodgers", "city":"Los Angeles", "division":"NL", "managerName": { "first":"Joe", "last":"Torre" }, "colors": [ "blue", "white" ], "worldChampionships": 6, "stats": [ { "year":2010, "wins":80, "losses":82, "winPercentage":0.494 }, { "year":2011, "wins":82, "losses":79, "winPercentage":0 }, { "year":2012, "wins":0, "losses":0, "winPercentage":0 } ] } ) |
UPDATE Support
MongoDB | ADS MongoSQL |
---|---|
db.baseball.update( { "teamName": "Dodgers" }, { $set: { "ranking": 5, "division": "NL West", "managerName.first": "D", "managerName.last": "Mattingly" } }, { multi: true } ) Note: The ranking field is added and the division and managerName fields are updated. | UPDATE baseball SET ranking=5, division='NL West', managerName.first='D', managerName.last='Mattingly' WHERE teamName = 'Dodgers' |
db.baseball.update( { "teamName": "Dodgers" }, { $set: { "managerName": { "first": "Don", "last": "Mattingly" } } }, { multi: true } ) | UPDATE baseball SET managerName = { "first": "Don", "last": "Mattingly" } WHERE teamName = 'Dodgers' |
db.baseball.update( { "teamName": "Dodgers" }, { $set: { "worldChampionships": NULL } }, { multi: true } ) | UPDATE baseball SET worldChampionships = NULL WHERE teamName = 'Dodgers' |
db.baseball.update( { "teamName": "Dodgers" }, { $unset: { "worldChampionships": 1 } }, { multi: true } ) Note: To remove a field from matched documents only. | not supported |
db.baseball.update( { }, { $unset: { "worldChampionships": 1 } }, { multi: true } ) Note: To remove a field from all documents in the collection. | ALTER COLLECTION baseball DROP FIELD worldChampionships |
db.baseball.update( { "teamName": "Dodgers" }, { $set: { "colors": ["blue", "red"] } } { multi: true } ) | UPDATE baseball SET colors = ['blue', 'red'] WHERE teamName = 'Dodgers' |
db.baseball.update( { "teamName": "Dodgers" }, { $set: { "colors.1": "gray" } } { multi: true } ) Note: The second element of the colors array field is updated. | UPDATE baseball SET colors[1] = 'gray' WHERE teamName = 'Dodgers' |
db.baseball.update( { "teamName": "Dodgers", "colors": "gray" }, { $set: { "colors.$": "white" } } { multi: true } ) Note: The matched element of the colors array field is updated. | UPDATE baseball SET colors[$] = 'white' WHERE teamName = 'Dodgers' AND colors = 'gray' |
db.baseball.update( { "teamName": "Dodgers" }, { $push: { "colors": "black" } } { multi: true } ) Note: An element is added to the colors array field. | UPDATE baseball or UPDATE baseball |
db.baseball.update( { "teamName": "Dodgers" }, { $pull: { "colors": "black" } } { multi: true } ) Note: The matched element is removed from the colors array field. | UPDATE baseball or UPDATE baseball |
db.baseball.update( { "teamName":"Dodgers" }, { $pull: { "stats" : { "year" : 2012 } } } { multi: true } ) Note: The stats array entry that has year = 2012 is deleted. | UPDATE baseball SET stats[$] = NULL WHERE teamName = 'Dodgers' AND stats.year = 2012 |
db.baseball.update( { "teamName":"Dodgers", "stats.year":2011 }, { $set: { "stats.$.winPercentage":0.509 } } { multi: true } ) | UPDATE baseball SET stats[$].winPercentage=0.509 WHERE teamName = 'Dodgers' AND stats.year = 2011 |
db.baseball.update( { "teamName": "Dodgers" }, { $push: { "stats": { "year":2012, "wins":86, "losses":76, "winPercentage":0.531 } } ) Note: In this example, there is no search criteria for an existing stats array entry and therefore a new stats array entry is added. | UPDATE baseball SET stats[$] = { "year": 2012, "wins":86, "losses":76, "winPercentage":0.531 } WHERE teamName = 'Dodgers' |
UPSERT Support
MongoDB | ADS MongoSQL |
---|---|
db.baseball.update( { "teamName": "Dodgers" }, { $set { "teamName":"Dodgers", "city":"Los Angeles", "division":"NL West", "ranking":5, "managerName": { "first":"Don", "last":"Mattingly" }, "colors": [ "blue", "white" ], "worldChampionships": 6, "stats": [ { "year":2010, "wins":80, "losses":82, "winPercentage":0.494 }, { "year":2011, "wins":82, "losses":79, "winPercentage":0.509 }, { "year":2012, "wins":86, "losses":76, "winPercentage":0.531 } ] } } { upsert: true } ) | REPLACE INTO baseball VALUE ( { "teamName":"Dodgers", "city":"Los Angeles", "division":"NL West", "ranking":5, "managerName": { "first":"Don", "last":"Mattingly" }, "colors": [ "blue", "white" ], "worldChampionships": 6, "stats": [ { "year":2010, "wins":80, "losses":82, "winPercentage":0.494 }, { "year":2011, "wins":82, "losses":79, "winPercentage":0.509 }, { "year":2012, "wins":86, "losses":76, "winPercentage":0.531 } ] } ) WHERE teamName = 'Dodgers' |
DELETE Syntax
MongoDB | ADS MongoSQL |
---|---|
db.baseball.remove( { "teamName": "Dodgers" }, ) | DELETE FROM baseball WHERE teamName = 'Dodgers' |
db.baseball.remove() | DELETE FROM baseball |
db.baseball.drop() | DROP COLLECTION baseball |
...