5.2 Working with Document IDs

This section describes what a document ID is and how to work with it.

Every document has a unique identifier called the document ID, which can be thought of as the equivalent of a table's primary key. The document ID value is usually automatically generated by the server when the document is added, but can also be manually assigned. The assigned document ID is returned in the generatedIds property of the Result (AddResult for Connector/J) object for the collection.add() operation and can be accessed using the getGeneratedIds() method. See Section 5.3, “Understanding Document IDs” for more background information on document IDs.

The following example in JavaScript code shows adding a document to a collection, retrieving the added document's IDs and testing that duplicate IDs cannot be added.

mysql-js > var result = mycollection.add({test:'demo01'}).execute()
mysql-js > print(result.generatedIds)
[
    "00006075f6810000000000000006"
]
mysql-js > var result = mycollection.add({test:'demo02'}).add({test:'demo03'}).execute()
mysql-js > print(result.generatedIds)
[
    "00006075f6810000000000000007",
    "00006075f6810000000000000008"
]

mysql-js > mycollection.find()
{
    "_id": "00006075f6810000000000000006",
    "test": "demo01"
}
{
    "_id": "00006075f6810000000000000007",
    "test": "demo02"
}
{
    "_id": "00006075f6810000000000000008",
    "test": "demo03"
}
3 documents in set (0.0102 sec)
mysql-js > var result = mycollection.add({_id:'00006075f6810000000000000008', test:'demo04'}).execute()
Document contains a field value that is not unique but required to be (MySQL Error 5116)

As shown in the example above, the document ID is stored in the _id field of a document. The document ID is a VARBINARY() with a maximum length of 32 characters. If an _id is provided when a document is created, it is honored; if no _id is provided, one is automatically assigned to the document.

The following example illustrates how the _id value can either be provided or autogenerated. It is assumed that the test schema exists and is assigned to the variable db, that the collection my_collection exists and that custom_id is unique.

MySQL Shell JavaScript Code

// If the _id is provided, it will be honored
var result = myColl.add( { _id: 'custom_id', a : 1 } ).execute();
var document = myColl.find("a = 1").execute().fetchOne();
print("User Provided Id:", document._id);

// If the _id is not provided, one will be automatically assigned
result = myColl.add( { b: 2 } ).execute();
print("Autogenerated Id:", result.getGeneratedIds()[0]);

MySQL Shell Python Code

# If the _id is provided, it will be honored
result = myColl.add( { '_id': 'custom_id', 'a' : 1 } ).execute()
document = myColl.find('a = 1').execute().fetch_one()
print("User Provided Id: %s" % document._id)

# If the _id is not provided, one will be automatically assigned
result = myColl.add( { 'b': 2 } ).execute()
print("Autogenerated Id: %s" % result.get_generated_ids()[0])

Node.js JavaScript Code

// If the _id is provided, it will be honored
myColl.add({ _id: 'custom_id', a : 1 }).execute().then(function () {
  myColl.getOne('custom_id').then(function (doc) {
    console.log('User Provided Id:', doc._id);
  });
});

// If the _id is not provided, one will be automatically assigned
myColl.add({ b: 2 }).execute().then(function (result) {
  console.log('Autogenerated Id:', result.getGeneratedIds()[0]);
});

C# Code

// If the _id is provided, it will be honored
var result = myColl.Add(new { _id = "custom_id", a = 1 }).Execute();
Console.WriteLine("User Provided Id:", result.AutoIncrementValue);

// If the _id is not provided, one will be automatically assigned
result = myColl.Add(new { b = 2 }).Execute();
Console.WriteLine("Autogenerated Id:", result.AutoIncrementValue);

Python Code

# If the _id is provided, it will be honored
result = my_coll.add({'_id': 'custom_id', 'a': 1}).execute()
print("User Provided Id: {0}".format(result.get_last_document_id()))

# If the _id is not provided, one will be automatically assigned
result = my_coll.add({'b': 2}).execute()
print("Autogenerated Id: {0}".format(result.get_last_document_id()))

Java Code

// If the _id is provided, it will be honored
AddResult result = coll.add("{\"_id\":\"custom_id\",\"a\":1}").execute();
System.out.println("User Provided Id:" + ((JsonString) coll.getOne("custom_id").get("_id")).getString());

// If the _id is not provided, one will be automatically assigned
result = coll.add("{\"b\":2}").execute();
System.out.println("Autogenerated Id:" + result.getGeneratedIds().get(0));

C++ Code

// If the _id is provided, it will be honored
Result result = myColl.add(R"({ "_id": "custom_id", "a" : 1 })").execute();
std::vector<string> ids = result.getGeneratedIds();
if (ids.empty())
  cout << "No Autogenerated Ids" << endl;

// If the _id is not provided, one will be automatically assigned
result = myColl.add(R"({ "b": 2 })").execute();
ids = result.getGeneratedIds();
cout << "Autogenerated Id:" << ids[0] << endl;

Some documents have a natural unique key. For example, a collection that holds a list of books is likely to include the International Standard Book Number (ISBN) for each document that represents a book. The ISBN is a string with a length of 13 characters, which is well within the length limit of 32 characters for the _id field.

// using a book's unique ISBN as the object ID
myColl.add( {
_id: "978-1449374020",
title: "MySQL Cookbook: Solutions for Database Developers and Administrators"
}).execute();

Use find() to fetch the newly inserted book from the collection by its document ID.

var book = myColl.find('_id = "978-1449374020"').execute();

Currently, X DevAPI does not support using any document field other than the implicit _id as the document ID—there is no way to define another key to perform the same function.