MySQL Shell API 9.0.0
Unified development interface for MySQL Products
Methods | List of all members
Collection Class Reference

A Collection is a container that may be used to store Documents in a MySQL database. More...

Methods

CollectionAdd add (...)
 Creates a document addition handler. More...
 
CollectionFind find (...)
 Creates a handler which can be used to find documents. More...
 
CollectionModify modify (String searchCondition)
 Creates a collection update handler. More...
 
CollectionRemove remove (String searchCondition)
 Creates a document deletion handler. More...
 
Result createIndex (String name, JSON indexDefinition)
 Creates an index on a collection. More...
 
Undefined dropIndex (String name)
 Drops an index from a collection.
 
Result replaceOne (String id, Document doc)
 Replaces an existing document with a new document. More...
 
Result addOrReplaceOne (String id, Document doc)
 Replaces or adds a document in a collection. More...
 
Document getOne (String id)
 Fetches the document with the given _id from the collection. More...
 
Result removeOne (String id)
 Removes document with the given _id value. More...
 
Integer count ()
 Returns the number of documents in the collection.
 
- Methods inherited from DatabaseObject
String getName ()
 Returns the name of this database object.

Returns
The name this database object.

 
Object getSession ()
 Returns the Session object of this database object.

Returns
The Session object used to get to this object.
More...
 
Object getSchema ()
 Returns the Schema object of this database object.

Returns
The Schema object used to get to this object.
More...
 
Bool existsInDatabase ()
 Verifies if this object exists in the database.

Returns
A boolean indicating if the object still exists on the database.

 

Additional Inherited Members

- Properties inherited from DatabaseObject
String name
 The name of this database object.
 
Object session
 The Session object of this database object.
 
Object schema
 The Schema object of this database object.
 

Detailed Description

A Collection is a container that may be used to store Documents in a MySQL database.

A Document is a set of key and value pairs, as represented by a JSON object.

Member Function Documentation

◆ add()

CollectionAdd add (   ...)

Creates a document addition handler.

Full Syntax

Collection.add(...)
.execute()

.add(...)

Overloads
  • add‌(documentList)
  • add‌(document[, document, ...])
  • add‌(mysqlx.expr(...))

This function receives one or more document definitions to be added into a collection. The documents are cached in an internal list and are added to the collection when the execute() method is called.

A document definition may be provided in two ways:

  • Using a dictionary containing the document fields.
  • Using A JSON string as a document expression.

There are three ways to add multiple documents:

  • Passing several parameters to the function, each parameter should be a document definition.
  • Passing a list of document definitions.
  • Calling this function several times before calling execute().

To be added, every document must have a string property named '_id' ideally with a universal unique identifier (UUID) as value. If the '_id' property is missing, it is automatically set with an internally generated UUID.

.execute()

Executes the add operation, the documents are added to the target collection.

See also
CollectionAdd

Examples

Using a Document List

Adding document using an existing document list

var result = collection.add([{ "name": 'my sexth', "passed": 'again', "count": 5 }, mysqlx.expr('{"name": "my senevth", "passed": "yep again", "count": 5}')]).execute();
print("Affected Rows Mixed List:", result.affectedItemsCount, "\n");

Multiple Parameters

Adding document using a separate parameter for each document on a single call to add(...)

var result = collection.add({ "name": 'my eigth', "passed": 'yep', "count": 6 }, mysqlx.expr('{"name": "my nineth", "passed": "yep again", "count": 6}')).execute();
print("Affected Rows Multiple Params:", result.affectedItemsCount, "\n");

Chaining Addition

Adding documents using chained calls to add(...)

var result = collection.add({ name: 'my fourth', passed: 'again', count: 4 }).add({ name: 'my fifth', passed: 'once again', count: 5 }).execute();
print("Affected Rows Chained:", result.affectedItemsCount, "\n");

JSON as Document Expressions

A document can be represented as a JSON expression as follows:

var result = collection.add(mysqlx.expr('{"name": "my fifth", "passed": "document", "count": 1}')).execute();
print("Affected Rows Single Expression:", result.affectedItemsCount, "\n");

◆ find()

CollectionFind find (   ...)

Creates a handler which can be used to find documents.

Full Syntax

Collection.find([searchCondition])
[.fields(...)]
[.groupBy(...)[.having(condition)]]
[.sort(...)]
[.limit(numberOfDocs)[.offset(quantity)]]
[.lockShared([lockContention])|.lockExclusive([lockContention])]
<tr><td></td><td>[.bind(name, value)]</td></tr>
<tr><td></td><td>.execute()</td></tr>

.find([searchCondition])

Sets the search condition to identify the Documents to be retrieved from the owner Collection. If the search condition is not specified the find operation will be executed over all the documents in the collection.

The search condition supports parameter binding.

.fields(...)

Overloads
  • fields‌(fieldList)
  • fields‌(field[, field, ...])
  • fields‌(mysqlx.expr(...))

This function sets the fields to be retrieved from each document matching the criteria on this find operation.

A field is defined as a string value containing an expression defining the field to be retrieved.

The fields to be retrieved can be set using any of the next methods:

  • Passing each field definition as an individual string parameter.
  • Passing a list of strings containing the field definitions.
  • Passing a JSON expression representing a document projection to be generated.

.groupBy(...)

Overloads
  • groupBy‌(fieldList)
  • groupBy‌(field[, field, ...]) Sets a grouping criteria for the resultset.

.having(condition)

Sets a condition for records to be considered in aggregate function operations.

.sort(...)

Overloads
  • sort‌(sortCriteriaList)
  • sort‌(sortCriterion[, sortCriterion, ...])

If used, the CollectionFind operation will return the records sorted with the defined criteria.

Every defined sort criterion follows the format:

name [ ASC | DESC ]

ASC is used by default if the sort order is not specified.

.limit(numberOfDocs)

If used, the operation will return at most numberOfDocs documents.

.offset(quantity)

If used, the first quantity records will not be included on the result.

.lockShared([lockContention])

When this function is called, the selected documents will be locked for write operations, they may be retrieved on a different session, but no updates will be allowed.

The acquired locks will be released when the current transaction is committed or rolled back.

The lockContention parameter defines the behavior of the operation if another session contains an exclusive lock to matching documents.

The lockContention can be specified using the following constants:

  • mysqlx.LockContention.DEFAULT
  • mysqlx.LockContention.NOWAIT
  • mysqlx.LockContention.SKIP_LOCKED

The lockContention can also be specified using the following string literals (no case sensitive):

  • 'DEFAULT'
  • 'NOWAIT'
  • 'SKIP_LOCKED'

If no lockContention or the default is specified, the operation will block if another session already holds an exclusive lock on matching documents until the lock is released.

If lockContention is set to NOWAIT and another session already holds an exclusive lock on matching documents, the operation will not block and an error will be generated.

If lockContention is set to SKIP_LOCKED and another session already holds an exclusive lock on matching documents, the operation will not block and will return only those documents not having an exclusive lock.

This operation only makes sense within a transaction.

.lockExclusive([lockContention])

When this function is called, the selected documents will be locked for read operations, they will not be retrievable by other session.

The acquired locks will be released when the current transaction is committed or rolled back.

The lockContention parameter defines the behavior of the operation if another session contains a lock to matching documents.

The lockContention can be specified using the following constants:

  • mysqlx.LockContention.DEFAULT
  • mysqlx.LockContention.NOWAIT
  • mysqlx.LockContention.SKIP_LOCKED

The lockContention can also be specified using the following string literals (no case sensitive):

  • 'DEFAULT'
  • 'NOWAIT'
  • 'SKIP_LOCKED'

If no lockContention or the default is specified, the operation will block if another session already holds a lock on matching documents.

If lockContention is set to NOWAIT and another session already holds a lock on matching documents, the operation will not block and an error will be generated.

If lockContention is set to SKIP_LOCKED and another session already holds a lock on matching documents, the operation will not block and will return only those documents not having a lock.

This operation only makes sense within a transaction.

.bind(name, value)

Binds the given value to the placeholder with the specified name.

An error will be raised if the placeholder indicated by name does not exist.

This function must be called once for each used placeholder or an error will be raised when the execute() method is called.

.execute()

Executes the find operation with all the configured options.

See also
CollectionFind

Examples

Retrieving All Documents

var records = collection.find().execute().fetchAll();
print("All:", records.length, "\n");

Filtering

var records = collection.find('gender = "male"').execute().fetchAll();
print("Males:", records.length, "\n");
var records = collection.find('gender = "female"').execute().fetchAll();
print("Females:", records.length, "\n");

Field Selection

Using a field selection list

var result = collection.find('name = "jack"').fields(['ucase(name) as FirstName', 'age as Age']).execute();
var record = result.fetchOne();
print('First Name:', record.FirstName, '\n');
print('Age:', record.Age, '\n');

Using separate field selection parameters

var result = collection.find('name = "jack"').fields('ucase(name) as FirstName', 'age as Age').execute();
var record = result.fetchOne();
print('First Name:', record.FirstName, '\n');
print('Age:', record.Age, '\n');

Using a projection expression

var result = collection.find('name = "jack"').fields(mysqlx.expr('{"FirstName":ucase(name), "InThreeYears":age + 3}')).execute();
var record = result.fetchOne();
print('First Name:', record.FirstName, '\n');
print('In Three Years:', record.InThreeYears, '\n');

Sorting

var records = collection.find().sort('name').execute().fetchAll();
for (index = 0; index < 7; index++) {
print('Find Asc', index, ':', records[index].name, '\n');
}
var records = collection.find().sort(['name desc']).execute().fetchAll();
for (index = 0; index < 7; index++) {
print('Find Desc', index, ':', records[index].name, '\n');
}

Using Limit and Offset

var records = collection.find().limit(4).execute().fetchAll();
print('Limit-Offset 0 :', records.length, '\n');
for (index = 1; index < 8; index++) {
var records = collection.find().limit(4).offset(index).execute().fetchAll();
print('Limit-Offset', index, ':', records.length, '\n');
}

Parameter Binding

var records = collection.find('age = :years and gender = :heorshe').bind('years', 13).bind('heorshe', 'female').execute().fetchAll();
print('Find Binding Length:', records.length, '\n');
print('Find Binding Name:', records[0].name, '\n');

◆ modify()

CollectionModify modify ( String  searchCondition)

Creates a collection update handler.

Full Syntax

Collection.modify(searchCondition)
[.set(attribute, value)]
[.unset(...)]
[.patch(document)]
[.arrayInsert(docPath, value)]
[.arrayAppend(docPath, value)]
<tr><td></td><td>[.sort(...)]</td></tr>
<tr><td></td><td>[.limit(numberOfDocs)]</td></tr>
<tr><td></td><td>[.bind(name, value)]</td></tr>
<tr><td></td><td>.execute()</td></tr>

.modify(searchCondition)

Parameters
searchConditionAn expression to identify the documents to be updated.

Creates a handler to update documents in the collection.

A condition must be provided to this function, all the documents matching the condition will be updated.

To update all the documents, set a condition that always evaluates to true, for example '1'.

.set(attribute, value)

Adds an operation into the modify handler to set an attribute on the documents that were included on the selection filter and limit.

  • If the attribute is not present on the document, it will be added with the given value.
  • If the attribute already exists on the document, it will be updated with the given value.

Using Expressions for Values

The received values are set into the document in a literal way unless an expression is used.

When an expression is used, it is evaluated on the server and the resulting value is set into the document.

To define an expression use:

mysqlx.expr(expression)

The expression also can be used for Parameter Binding.

The attribute addition will be done on the collection's documents once the execute method is called.

.unset(...)

Overloads
  • unset‌(attributeList)
  • unset‌(attribute[, attribute, ...])

Removes attributes from documents in a collection.

The attribute removal will be done on the collection's documents once the execute() method is called.

.patch(document)

Performs modifications on a document based on a patch JSON object.

This function adds an operation to update the documents of a collection, the patch operation follows the algorithm described on the JSON Merge Patch RFC7386.

The patch JSON object will be used to either add, update or remove fields from documents in the collection that match the filter specified on the call to the modify() function.

The operation to be performed depends on the attributes defined at the patch JSON object:

  • Any attribute with value equal to null will be removed if exists.
  • Any attribute with value different than null will be updated if exists.
  • Any attribute with value different than null will be added if does not exists.

Special considerations:

  • The _id of the documents is immutable, so it will not be affected by the patch operation even if it is included on the patch JSON object.
  • The patch JSON object accepts expression objects as values. If used they will be evaluated at the server side.

The patch operations will be done on the collection's documents once the execute() method is called.

.arrayInsert(docPath, value)

Adds an operation into the modify handler to insert a value into an array attribute on the documents that were included on the selection filter and limit.

The insertion of the value will be done on the collection's documents once the execute() method is called.

.arrayAppend(docPath, value)

Adds an operation into the modify handler to append a value into an array attribute on the documents that were included on the selection filter and limit.

.sort(...)

Overloads
  • sort‌(sortCriteriaList)
  • sort‌(sortCriterion[, sortCriterion, ...])

Every defined sort criterion follows the format:

name [ ASC | DESC ]

ASC is used by default if the sort order is not specified.

This method is usually used in combination with limit to fix the amount of documents to be updated.

.limit(numberOfDocs)

This method is usually used in combination with sort to fix the amount of documents to be updated.

.bind(name, value)

Binds the given value to the placeholder with the specified name.

An error will be raised if the placeholder indicated by name does not exist.

This function must be called once for each used placeholder or an error will be raised when the execute() method is called.

.execute()

Executes the update operations added to the handler with the configured filter and limit.

See also
CollectionModify

Examples

Modify multiple attributes

var result = collection.modify('name = "brian"').set('alias', 'bri').set('last_name', 'black').set('age', mysqlx.expr('13+1')).execute();
print('Set Affected Rows:', result.affectedItemsCount, '\n');

Modify an attribute with an array value

var result = collection.modify('name = "brian"').set('hobbies', mysqlx.expr(':list')).bind('list', ['soccer', 'dance', 'read']).execute();
print('Set Affected Rows:', result.affectedItemsCount, '\n');

Unset an attribute

var result = collection.modify('name = "brian"').unset('last_name').execute();
print('Unset Affected Rows:', result.affectedItemsCount, '\n');

Unset multiple attributes using an array

var result = collection.modify('name = "brian"').unset(['alias', 'age']).execute();
print('Unset Affected Rows:', result.affectedItemsCount, '\n');

Patch multiple attributes

var result = collection.modify('name = "brian"').patch({'last_name': 'white', 'age': 14, 'alias': 'bw', 'girlfriends': ['lois', 'jane'] }).execute();
print('Patch Affected Rows:', result.affectedItemsCount, '\n');

Append to an array attribute

var result = collection.modify('name = "brian"').arrayAppend('hobbies', 'running').execute();
print('Array Append Affected Rows:', result.affectedItemsCount, '\n');

Insert into an array attribute

var result = collection.modify('name = "brian"').arrayInsert('hobbies[1]', 'cycling').execute();
print('Array Insert Affected Rows:', result.affectedItemsCount, '\n');

Sorting and setting a limit

var result = collection.modify('age = 15').set('sample', 'in_limit').sort(['name']).limit(2).execute();
print('Affected Rows:', result.affectedItemsCount, '\n');

◆ remove()

CollectionRemove remove ( String  searchCondition)

Creates a document deletion handler.

Full Syntax

Collection.remove(searchCondition)
[.sort(...)]
[.limit(numberOfDocs)]
[.bind(name, value)]
.execute()

.remove(searchCondition)

Creates a handler for the deletion of documents on the collection.

A condition must be provided to this function, all the documents matching the condition will be removed from the collection.

To delete all the documents, set a condition that always evaluates to true, for example '1'.

The searchCondition supports parameter binding.

.sort(...)

Overloads
  • sort‌(sortCriteriaList)
  • sort‌(sortCriterion[, sortCriterion, ...])

Every defined sort criterion follows the format:

name [ ASC | DESC ]

ASC is used by default if the sort order is not specified.

This method is usually used in combination with limit to fix the amount of documents to be deleted.

.limit(numberOfDocs)

Sets a limit for the documents to be deleted.

This method is usually used in combination with sort to fix the amount of documents to be deleted.

.bind(name, value)

Binds the given value to the placeholder with the specified name.

An error will be raised if the placeholder indicated by name does not exist.

This function must be called once for each used placeholder or an error will be raised when the execute() method is called.

.execute()

Executes the document deletion with the configured filter and limit.

See also
CollectionRemove

Examples

Remove under condition

var result = collection.remove('age = 15').execute();
print('Affected Rows:', result.affectedItemsCount, '\n');
var docs = collection.find().execute().fetchAll();
print('Records Left:', docs.length, '\n');

Remove with binding

var result = collection.remove('gender = :heorshe').limit(2).bind('heorshe', 'male').execute();
print('Affected Rows:', result.affectedItemsCount, '\n');
var docs = collection.find().execute().fetchAll();
print('Records Left:', docs.length, '\n');

Full remove

var result = collection.remove('1').execute();
print('Affected Rows:', result.affectedItemsCount, '\n');
var docs = collection.find().execute().fetchAll();
print('Records Left:', docs.length, '\n');

◆ createIndex()

Result createIndex ( String  name,
JSON  indexDefinition 
)

Creates an index on a collection.

Parameters
namethe name of the index to be created.
indexDefinitiona JSON document with the index information.
Returns
a Result object.

This function will create an index on the collection using the information provided in indexDefinition.

The indexDefinition is a JSON document with the next information:

{
fields : [<index_field>, ...],
type : <type>
}
  • fields array of index_field objects, each describing a single document member to be included in the index.
  • type string, (optional) the type of index. One of INDEX or SPATIAL. Default is INDEX and may be omitted.

A single index_field description consists of the following fields:

{
field : <field>,
type : <type>,
required : <boolean>,
options : <uint>,
srid : <uint>,
array : <boolean>
}
  • field: string, the full document path to the document member or field to be indexed.
  • type: string, one of the supported SQL column types to map the field into. For numeric types, the optional UNSIGNED keyword may follow. For the TEXT type, the length to consider for indexing may be added.
  • required: bool, (optional) true if the field is required to exist in the document. defaults to false, except for GEOJSON where it defaults to true.
  • options: uint, (optional) special option flags for use when decoding GEOJSON data.
  • srid: uint, (optional) srid value for use when decoding GEOJSON data.
  • array: bool, (optional) true if the field is an array.

The 'options' and 'srid' fields can and must be present only if 'type' is set to 'GEOJSON'.

The 'array' field can only be present if 'type' is any of: BINARY(N), CHAR(N), DATE, DATETIME, TIME, DECIMAL(M[,D]), SIGNED [INTEGER] or UNSIGNED [INTEGER].

◆ replaceOne()

Result replaceOne ( String  id,
Document  doc 
)

Replaces an existing document with a new document.

Parameters
ididentifier of the document to be replaced.
docthe new document.
Returns
A Result object containing the number of affected rows.

Replaces the document identified with the given id. If no document is found matching the given id the returned Result will indicate 0 affected items.

Only one document will be affected by this operation.

The id of the document remain immutable, if the new document contains a different id, it will be ignored.

Any constraint (unique key) defined on the collection is applicable:

The operation will fail if the new document contains a unique key which is already defined for any document in the collection except the one being replaced.

◆ addOrReplaceOne()

Result addOrReplaceOne ( String  id,
Document  doc 
)

Replaces or adds a document in a collection.

Parameters
idthe identifier of the document to be replaced.
docthe new document.
Returns
A Result object containing the number of affected rows.

Replaces the document identified with the given id. If no document is found matching the given id the given document will be added to the collection.

Only one document will be affected by this operation.

The id of the document remains immutable, if the new document contains a different id, it will be ignored.

Any constraint (unique key) defined on the collection is applicable on both the replace and add operations:

  • The replace operation will fail if the new document contains a unique key which is already defined for any document in the collection except the one being replaced.
  • The add operation will fail if the new document contains a unique key which is already defined for any document in the collection.

◆ getOne()

Document getOne ( String  id)

Fetches the document with the given _id from the collection.

Parameters
idThe identifier of the document to be retrieved.
Returns
The Document object matching the given id or NULL if no match is found.

◆ removeOne()

Result removeOne ( String  id)

Removes document with the given _id value.

Parameters
idThe id of the document to be removed.
Returns
A Result object containing the number of affected rows.

If no document is found matching the given id, the Result object will indicate 0 as the number of affected rows.