4.3 Collection CRUD Function Overview

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.

Collection.add()

The Collection.add() function is used to store documents in the database. It takes a single document or a list of documents 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.

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();

For more information, see CollectionAddFunction.

Collection.find()

The find() function is used to search for documents in the collection, the equivalent of retrieving records from a database. It takes a SearchConditionStr as a parameter to specify the documents that should be returned from the database. How you form a SearchConditionStr and the required expression depends on the structure of the JSON documents being searched. To search a collection based on nested elements in the documents you need the JSON path to the element you want to search for. See JSON Path Syntax for information about how to identify a specific element in a JSON document. For example, suppose a collection contains documents about countries such as:

 {
    GNP: .6,
    IndepYear: 1967,
    Name: "Sealand",
    Code: "SEA",
    demographics: {
        LifeExpectancy: 79,
        Population: 27
    },
    geography: {
        Continent: "Europe",
        Region: "British Islands",
        SurfaceArea: 193
    },
    government: {
        GovernmentForm: "Monarchy",
        HeadOfState: "Michael Bates"
    }
  }
)

To find countries that have the Continent value set to Europe, the path leg to the element is geography. That leg can contain multiple elements, which is notated using the [*] syntax. To separate the leg to the Continent element, use a period (.) character. Therefore the find could consist of a documentPathLastItem expression in the form of:

'Europe' in $.geography[*].Continent

For paths used in MySQL JSON functions, the scope is always the document being searched or otherwise operated on, represented by a leading $ character. Thus, the leading $. should be optional. Depending on the language you are using, the full find function could be:

collection.find(':name in geography[*].Continent')
  .bind('name', 'Europe')
  .execute()

Several methods such as fields(), sort(), skip() and limit() can be chained to the find() function to further refine the result. For more information, see Section 3.2, “Method Chaining”.

The fetch() function actually triggers the execution of the operation. The example assumes that the test schema exists and that the collection my_collection exists.

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;
}

For more information, see CollectionFindFunction.

Collection.modify()

Figure 4.1 Collection.modify() Syntax Diagram

Collection.remove()

Figure 4.2 Collection.remove() Syntax Diagram