Page History
SQL Mapping
The mapping below displays side-by-side how MongoDB syntax maps to ADS MongoSQL syntax so that you can determine how to write queries in the Aqua Data Studio's Query Analyzer when connecting to MongoDB. These examples use the baseball.json file attached to the SQL Query Reference page.
To test the SQL Mapping, you can first create a new collection using CREATE COLLECTION and then insert data using INSERT, or you can just insert data using INSERT and a collection will be created if it does not exist. There is no direct mapping for CREATE TABLE.
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 |