The following section explains the individual functions of the Collection object.
The most common operations to be performed on a Collection are the Create Read Update Delete (CRUD) operations. In order to speed up find operations it is recommended to make proper use of indexes.
The Collection.add()
function is used to
store documents in a collection, similar to the
INSERT statement for a SQL
database. It takes a single document or a list of documents as
its argument, and is executed by the
execute()
function.
The collection needs to be created with the
Schema.createCollection()
function before
documents can be inserted. To insert documents into an existing
collection use the Schema.getCollection()
function to retrieve the Collection object.
The following example shows how to use the
Collection.add()
function. The example
assumes that the test schema exists and that the collection
my_collection
does not exist.
MySQL Shell JavaScript Code
// Create a new collection
var myColl = db.createCollection('my_collection');
// Insert a document
myColl.add( {name: 'Laurie', age: 19 } ).execute();
// Insert several documents at once
myColl.add( [
{name: 'Nadya', age: 54 },
{name: 'Lukas', age: 32 } ] ).execute();
MySQL Shell Python Code
# Create a new collection
myColl = db.create_collection('my_collection')
# Insert a document
myColl.add( {'name': 'Laurie', 'age': 19 } ).execute()
# Insert several documents at once
myColl.add( [
{'name': 'Nadya', 'age': 54 },
{'name': 'Lukas', 'age': 32 } ] ).execute()
Node.js JavaScript Code
// Create a new collection
db.createCollection('myCollection').then(function (myColl) {
return Promise.all([
// Insert a document
myColl
.add({ name: 'Laurie', age: 19 })
.execute(),
// Insert several documents at once
myColl
.add([
{ name: 'Nadya', age: 54 },
{ name: 'Lukas', age: 32 }
])
.execute()
])
});
C# Code
// Assumptions: test schema assigned to db, my_collection collection not exists
// Create a new collection
var myColl = db.CreateCollection("my_collection");
// Insert a document
myColl.Add(new { name = "Laurie", age = 19 }).Execute();
// Insert several documents at once
myColl.Add(new[] {
new { name = "Nadya", age = 54 },
new { name = "Lukas", age = 32 } }).Execute();
Python Code
# Create a new collection
my_coll = my_schema.create_collection('my_collection')
# Insert a document
my_coll.add({'name': 'Laurie', 'age': 19}).execute()
# Insert several documents at once
my_coll.add([
{'name': 'Nadya', 'age': 54},
{'name': 'Lukas', 'age': 32}
]).execute()
Java Code
// Create a new collection
Collection coll = db.createCollection("payments");
// Insert a document
coll.add("{\"name\":\"Laurie\", \"age\":19}").execute();
// Insert several documents at once
coll.add("{\"name\":\"Nadya\", \"age\":54}",
"{\"name\":\"Lukas\", \"age\":32}").execute();
C++ Code
// Create a new collection
Collection coll = db.createCollection("payments");
// Insert a document
coll.add(R"({"name":"Laurie", "age":19})").execute();
// Insert several documents at once
std::list<DbDoc> docs = {
DbDoc(R"({"name":"Nadya", "age":54})"),
DbDoc(R"({"name":"Lukas", "age":32})")
};
coll.add(docs).execute();
See also CollectionAddFunction for
the syntax of add()
in EBNF.
The find()
function is used to search for
documents in a collection, similar to the
SELECT statement for a SQL
database. It takes a search condition string
(SearchConditionStr) as a parameter
to specify the documents that should be returned from the
database. The execute()
function triggers the
actual execution of the find()
operation.
The SearchConditionStr can be in one of these forms:
-
If no SearchConditionStr is specified, the
find()
operation returns all the documents in the collection.// Get a collection var myColl = session.getSchema("world_x").getCollection("countryinfo"); // To return all documents in world_x: myColl.find().execute();
-
The most common form for a SearchConditionStr is:
JSON-path [ operator { value | JSON-path} ]
Here are some explanations for the different parts of a SearchConditionStr:
-
JSON-path
: A JSON path identifies an element in a JSON document; see JSON Path Syntax for details . Here is a short summary of the JSON path syntax:A JSON path starts with a scope: in MySQL's JSON document implementation, the scope of the path is always the document being operated on, represented as
$
, which is always implicitly assumed, so it can be skipped in most cases (except when, for example, the**
wildcard is used; see the discussion on wildcards below). For example, the path$.geography.Region
is equivalent togeography.Region
.After the scope, a path consists of one or more path legs. A path leg leads from one level of the JSON tree down to the next, and consecutive paths are separated by a period (
.
). For example:myColl.find("geography.Continent = 'Africa'")
finds all documents that have the valueAfrica
for the fieldContinent
under the fieldgeography
.-
Elements in arrays are represented by
[
, whereN
]N
is an array index, which has to be a non-negative integer.myColl.add({name:'John', favorNums: [1, 3, 5, 7, 9]}).execute(); myColl.find("favorNums[0] = 1").execute(); //Returns the document just added }
-
The wildcard tokens
*
and**
can be used in JSON paths as follows:
represents the values of all members under the memberobject
.*object
. For example, in thecountryinfo
collection in the sampleworld_x
schema,geography.*
represents all members under the objectgeography
, andmyColl.find("'Africa' in geography.*")
returns all documents that have the valueAfrica
in any of the members undergeography
.-
represents the values of all elements in an array. For example:array
[*]myColl.add({name:'John', favorNums: [1, 3, 5, 7, 9]}).execute(); myColl.add({name:'Jane', favorNums: [2, 4, 6, 8, 10]}).execute(); myColl.find("1 in favorNums[*]").execute(); //Returns the first document added above myColl.find("2 in favorNums[*]").execute(); //Returns the second document added above }
-
[
represents all paths under the documentprefix
]**suffix
prefix
that end withsuffix
, regardless of the depth of the path. The following examples illustrate how ** can be used to return different results:mysql-js> myColl.find().execute(); { "a": "bar", "b": { "b1": 6, "b2": 7, "b3": { "b1": 99, "b2": 98, "b3": { "b1": 999, "b2": 998 } } }, "_id": "000061313aa10000000000000001" } { "a": "baz", "b": { "b1": 1, "b2": 7 }, "_id": "000061313aa10000000000000002" } { "a": "bbr", "c": 37, "_id": "0000613247ed0000000000000001" } 3 documents in set (0.0007 sec) mysql-js> myColl.find("$**.b2").execute(); { "a": "bar", "b": { "b1": 6, "b2": 7, "b3": { "b1": 99, "b2": 98, "b3": { "b1": 999, "b2": 998 } } }, "_id": "000061313aa10000000000000001" } { "a": "baz", "b": { "b1": 1, "b2": 7 }, "_id": "000061313aa10000000000000002" } 2 documents in set, 1 warning (0.0008 sec) ... mysql-js> myColl.find("$**.b3**.b2").execute(); { "a": "bar", "b": { "b1": 6, "b2": 7, "b3": { "b1": 99, "b2": 98, "b3": { "b1": 999, "b2": 998 } } }, "_id": "000061313aa10000000000000001" } 1 document in set, 1 warning (0.0011 sec) ...
The following requirements apply when using the
**
wildcard:prefix
should be$
or an element that is a document itself.suffix
should be a path leg and is always required (that is, a path expression may not end in**
).A path expression may not contain the sequence
***
.
-
value
is a value to be compared to an element on theJSON-path
. The%
and_
wildcard characters can be used invalue
with theLIKE
operator, just like in a MySQLWHERE
clause. For example:myColl.find("Name LIKE 'Austra%'") myColl.find("geography.Continent LIKE 'Asi_'")
-
operator
: The following operators can be used in a SearchConditionStr:OR (||)
,AND (&&)
,XOR
,IS
,NOT
,BETWEEN
,IN
,LIKE
,OVERLAPS
,!=
,<>
,>
,>=
,<
,<=
,&
,|
,<<
,>>
,+
,-
,*
,/
,~
, and%
. Here are some examples for using the operators:myColl.find("Name = 'Australia'") myColl.find("demographics.Population >= 1000000" ) myColl.find("demographics.LifeExpectancy BETWEEN 50 AND 60") myColl.find("government.HeadOfState = 'Elizabeth II' AND geography.Region = 'Caribbean'")
If no operator and subsequent JSON path is supplied,
find()
returns all documents for which the JSON path supplied points to some non-null elements. For example:myColl.find("demographics.Population" ).execute();
Returns all documents that have a
demographics.Population
element:{ "GNP": 828, "_id": "00005de917d80000000000000000", "Code": "ABW", "Name": "Aruba", "IndepYear": null, "geography": { "Region": "Caribbean", "Continent": "North America", "SurfaceArea": 193 }, "government": { "HeadOfState": "Beatrix", "GovernmentForm": "Nonmetropolitan Territory of The Netherlands" }, "demographics": { "Population": 103000, "LifeExpectancy": 78.4000015258789 } } { "GNP": 5976, "_id": "00005de917d80000000000000001", ... 232 documents in set, 1 warning (0.0013 sec) Warning (code 3986): Evaluating a JSON value in SQL boolean context does an implicit comparison against JSON integer 0; if this is not what you want, consider converting JSON to a SQL numeric type with JSON_VALUE RETURNING
Use the
IN
operator in the SearchConditionStr to check for a value within all the members covered by a wildcard:mysql-js> myColl.find("$**.b1").execute(); { "a": "bar", "b": { "b1": 6, "b2": 7, "b3": { "b1": 99, "b2": 98, "b3": { "b1": 999, "b2": 998 } } }, "_id": "000061313aa10000000000000001" } { "a": "baz", "b": { "b1": 1, "b2": 7 }, "_id": "000061313aa10000000000000002" } 2 documents in set, 1 warning (0.0012 sec) ... mysql-js> myColl.find("99 IN $**.b1").execute(); { "a": "bar", "b": { "b1": 6, "b2": 7, "b3": { "b1": 99, "b2": 98, "b3": { "b1": 999, "b2": 998 } } }, "_id": "000061313aa10000000000000001" } 1 document in set (0.0016 sec) ...
The
OVERLAPS
operator compares two JSON fragments and returns true (1) if the two fragments have any values in any key-value pair or array element in common. For example:mysql-js> myColl.find("list").execute(); { "_id": "1", "list": [ 1, 4 ] } { "_id": "2", "list": [ 4, 7 ] } 2 documents in set, 1 warning (0.0010 sec) mysql-js> myColl.find("[1,2,3] OVERLAPS $.list") { "_id": "1", "list": [ 1, 4 ] } 1 document in set (0.0006 sec)
-
Several methods such as fields()
,
sort()
, skip()
, and
limit()
can be chained to the
find()
function to further refine the result.
For example:
myColl.find("Name LIKE 'Austra%'").fields("Code")
myColl.find("geography.Continent LIKE 'A%'").limit(10)
Parameter binding using bind()
is also
supported. The following example illustrates the use of
bind()
with find()
:
MySQL Shell JavaScript Code
// Use the collection 'my_collection'
var myColl = db.getCollection('my_collection');
// Find a single document that has a field 'name' that starts with 'L'
var docs = myColl.find('name like :param').
limit(1).bind('param', 'L%').execute();
print(docs.fetchOne());
// Get all documents with a field 'name' that starts with 'L'
docs = myColl.find('name like :param').
bind('param','L%').execute();
var myDoc;
while (myDoc = docs.fetchOne()) {
print(myDoc);
}
MySQL Shell Python Code
# Use the collection 'my_collection'
myColl = db.get_collection('my_collection')
# Find a single document that has a field 'name' that starts with 'L'
docs = myColl.find('name like :param').limit(1).bind('param', 'L%').execute()
print(docs.fetch_one())
# Get all documents with a field 'name' that starts with 'L'
docs = myColl.find('name like :param').bind('param','L%').execute()
myDoc = docs.fetch_one()
while myDoc:
print(myDoc)
myDoc = docs.fetch_one()
Node.js JavaScript Code
// Use the collection 'my_collection'
var myColl = db.getCollection('my_collection');
// Find a single document that has a field 'name' that starts with 'L'
myColl
.find('name like :name')
.bind('name', 'L%')
.limit(1)
.execute(function (doc) {
console.log(doc);
})
.then(function () {
// handle details
});
// Get all documents with a field 'name' that starts with 'L'
myColl
.find('name like :name')
.bind('name', 'L%')
.execute(function (doc) {
console.log(doc);
})
.then(function () {
// handle details
});
C# Code
// Use the collection "my_collection"
var myColl = db.GetCollection("my_collection");
// Find a single document that has a field "name" that starts with "L"
var docs = myColl.Find("name like :param")
.Limit(1).Bind("param", "L%").Execute();
Console.WriteLine(docs.FetchOne());
// Get all documents with a field "name" that starts with "L"
docs = myColl.Find("name like :param")
.Bind("param", "L%").Execute();
while (docs.Next())
{
Console.WriteLine(docs.Current);
}
Python Code
# Use the collection 'my_collection'
my_coll = my_schema.get_collection('my_collection')
# Find a single document that has a field 'name' that starts with 'L'
docs = my_coll.find('name like :param').limit(1).bind('param', 'L%').execute()
print(docs.fetch_one())
# Get all documents with a field 'name' that starts with 'L'
docs = my_coll.find('name like :param').bind('param', 'L%').execute()
doc = docs.fetch_one()
print(doc)
Java Code
// Use the collection 'my_collection'
Collection myColl = db.getCollection("my_collection");
// Find a single document that has a field 'name' that starts with 'L'
DocResult docs = myColl.find("name like :name").bind("name", "L%").execute();
System.out.println(docs.fetchOne());
// Get all documents with a field 'name' that starts with 'L'
docs = myColl.find("name like :name").bind("name", "L%").execute();
while (docs.hasNext()) {
DbDoc myDoc = docs.next();
System.out.println(myDoc);
}
C++ Code
// Use the collection 'my_collection'
Collection myColl = db.getCollection("my_collection");
// Find a single document that has a field 'name' that starts with 'L'
DocResult docs = myColl.find("name like :param")
.limit(1).bind("param", "L%").execute();
cout << docs.fetchOne() << endl;
// Get all documents with a field 'name' that starts with 'L'
docs = myColl.find("name like :param")
.bind("param","L%").execute();
DbDoc myDoc;
while ((myDoc = docs.fetchOne()))
{
cout << myDoc << endl;
}
See also CollectionFindFunction for
the syntax of find()
in EBNF.
The Collection.remove()
function is used to
remove documents in a collection, similar to the
DELETE statement for a SQL
database. It takes a search condition string
(SearchConditionStr) as a parameter
to specify the documents that should be removed from the
collection (a detailed explanation of the
SearchConditionStr can be found in
Collection.find()). remove()
returns an error if no search condition string is provided, or
if an empty string is provided. All documents in the collection
are removed if any expression that evaluates to true without
matching any document (for example,
“true
” or “_id IS NOT
NULL
”) is passed as the search condition string.
The following methods can be chained to the
remove()
method to configure the deletion:
limit(
: Limits the number of documents to be deleted toint
)
.int
-
sort(
: Sort the order in which documents are to be deleted according tosortCriteriaList
)sortCriteriaList
, which is either a comma-separated list or an array of
. EachsortCriteria
consists of a component name and a search order (sortCriteria
asc
for ascending, ordesc
for descending). For example:sort('name asc', 'age desc')
sort(['name asc', 'age desc'])
The method is usually used in combination with the
limit()
method to determine which of the documents matched by the search condition string are to be deleted.
Parameter binding using bind()
is also
supported, and the execute()
function
triggers the actual execution of the remove operation. The
following example shows how to use the
Collection.remove()
function. It assumes some
documents have been added to the collection as illustrated by
the code example in Collection.add():
MySQL Shell JavaScript Code
// Use the collection 'my_collection'
var myColl = db.getCollection('my_collection');
// Remove documents by criteria
myColl.remove('name like :name AND age < :age').
limit(1).bind('name','N%').bind('age', 60).execute();
MySQL Shell Python Code
# Use the collection 'my_collection'
myColl = db.get_collection('my_collection')
# Remove documents by criteria
myColl.remove('name like :name AND age < :age') \
.limit(1).bind('name','N%').bind('age', 60).execute()
Node.js JavaScript Code
// Use the collection 'my_collection'
var myColl = db.getCollection('my_collection');
// Remove documents by criteria
myColl
.remove('name like :name && age < :age')
.limit(1)
.bind({ name: 'N%', age: 60 })
.execute();
C# Code
// Use the collection "my_collection"
var myColl = db.GetCollection("my_collection");
// Remove documents by criteria
myColl.Remove("name like :name AND age < :age").Limit(1).Bind("name","N%").Bind("age", 60).Execute();
Python Code
# Use the collection "my_collection"
my_coll = my_schema.get_collection('my_collection')
# Remove documents by criteria
my_coll.remove("name like :name AND age < :age").limit(1).bind("name","N%").bind("age", 60).execute();
Java Code
// Use the collection 'my_collection'
Collection myColl = db.getCollection("my_collection");
// Remove documents by criteria
myColl.remove("name like :name AND age < :age").limit(1)
.bind("name","N%").bind("age", 60).execute();
C++ Code
// Use the collection 'my_collection'
Collection myColl = db.getCollection("my_collection");
// Remove documents by criteria
myColl.remove("name like :name AND age < :age").limit(1)
.bind("name","N%").bind("age", 60).execute();
See also CollectionRemoveFunction
for the syntax of remove()
in EBNF.