Versions Compared

Old Version 4

changes.mady.by.user Nadine Hinojosa

Saved on

compared with

New Version 5

changes.mady.by.user Nadine Hinojosa

Saved on

Key

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

...

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.


Table of Contents

Contents

...

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

...