You are viewing an old version of this page. View the current version.

Compare with Current View Page History

« Previous Version 11 Next »

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.


WHERE SQL Mapping

Query Reference: WHERE

MongoDBADS 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
WHERE colors CONTAINS_ALL ('orange', 'black')


db.baseball.find(
{ colors:
	{ $all: [ "orange", "black" ],
	 $size: 2 }
}
)

Note: $size matches any array with the number of elements specified.

SELECT * FROM baseball
WHERE colors CONTAINS_ALL ('orange', 'black') AND
  ARRAY_SIZE(colors) = 2

or
SELECT * FROM baseball
WHERE colors = ['orange', 'black']

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

Back to top

Other WHERE Support

Query Reference: WHERE

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

Back to top

INSERT Support

Query Reference: INSERT

MongoDBADS 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
        }
    ]
} )

Back to top

UPDATE Support

Query Reference: UPDATE

MongoDBADS 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
SET colors[$] = 'black'
WHERE teamName = 'Dodgers'

or

UPDATE baseball
SET colors = colors + [ 'black' ]
WHERE teamName = 'Dodgers'

db.baseball.update(
	{ "teamName": "Dodgers" },
	{ $pull: { "colors": "black" } }
	{ multi: true }
)

Note: The matched element is removed from the colors array field.

UPDATE baseball
SET colors[$] = NULL
WHERE teamName = 'Dodgers' AND
 colors = 'black'

or

UPDATE baseball
SET colors = colors - [ 'black' ]
WHERE teamName = 'Dodgers'

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'

Back to top

UPSERT Support

Query Reference: UPSERT

MongoDBADS 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'

Back to top

DELETE Syntax

Query Reference: DELETE

MongoDBADS MongoSQL
db.baseball.remove(
	{ "teamName": "Dodgers" },
)
DELETE FROM baseball WHERE teamName = 'Dodgers'
db.baseball.remove()
DELETE FROM baseball
db.baseball.drop()
DROP COLLECTION baseball

Back to top


  • No labels