MySQL Shell API 8.4.1
Unified development interface for MySQL Products
|
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 (str search_condition) |
Creates a collection update handler. More... | |
CollectionRemove | remove (str search_condition) |
Creates a document deletion handler. More... | |
Result | create_index (str name, JSON indexDefinition) |
Creates an index on a collection. More... | |
None | drop_index (str name) |
Drops an index from a collection. | |
Result | replace_one (str id, document doc) |
Replaces an existing document with a new document. More... | |
Result | add_or_replace_one (str id, document doc) |
Replaces or adds a document in a collection. More... | |
Document | get_one (str id) |
Fetches the document with the given _id from the collection. More... | |
Result | remove_one (str id) |
Removes document with the given _id value. More... | |
int | count () |
Returns the number of documents in the collection. | |
![]() | |
str | get_name () |
Returns the name of this database object.
| |
object | get_session () |
Returns the Session object of this database object.
| |
object | get_schema () |
Returns the Schema object of this database object.
| |
bool | exists_in_database () |
Verifies if this object exists in the database.
| |
Additional Inherited Members | |
![]() | |
str | 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. | |
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.
CollectionAdd add | ( | ... | ) |
Creates a document addition handler.
Collection | .add(...) |
.execute() |
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:
There are three ways to add multiple documents:
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.
Executes the add operation, the documents are added to the target collection.
Adding document using an existing document list
Adding document using a separate parameter for each document on a single call to add(...)
Adding documents using chained calls to add(...)
JSON as Document Expressions
A document can be represented as a JSON expression as follows:
CollectionFind find | ( | ... | ) |
Creates a handler which can be used to find documents.
Collection | .find([searchCondition]) |
[.fields(...)] | |
[.group_by(...)[.having(condition)]] | |
[.sort(...)] | |
[.limit(numberOfDocs)[.offset(quantity)]] | |
[.lock_shared(lockContention)|.lock_exclusive(lockContention)] <tr><td></td><td>[.bind(name, value)]</td></tr> <tr><td></td><td>.execute()</td></tr> |
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.
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:
Sets a condition for records to be considered in aggregate function operations.
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.
If used, the operation will return at most numberOfDocs documents.
If used, the first quantity records will not be included on the result.
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:
The lockContention can also be specified using the following string literals (no case sensitive):
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.
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:
The lockContention can also be specified using the following string literals (no case sensitive):
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.
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.
Executes the find operation with all the configured options.
Using a field selection list
Using separate field selection parameters
Using a projection expression
CollectionModify modify | ( | str | searchCondition | ) |
Creates a collection update handler.
Collection | .modify(searchCondition) |
[.set(attribute, value)] | |
[.unset(...)] | |
[.patch(document)] | |
[.array_insert(docPath, value)] | |
[.array_append(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> |
searchCondition | An 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'.
Adds an operation into the modify handler to set an attribute on the documents that were included on the selection filter and limit.
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:
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.
Removes attributes from documents in a collection.
The attribute removal will be done on the collection's documents once the execute() method is called.
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:
Special considerations:
The patch operations will be done on the collection's documents once the execute() method is called.
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.
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.
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.
This method is usually used in combination with sort to fix the amount of documents to be updated.
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.
Executes the update operations added to the handler with the configured filter and limit.
CollectionRemove remove | ( | str | searchCondition | ) |
Creates a document deletion handler.
Collection | .remove(searchCondition) |
[.sort(...)] | |
[.limit(numberOfDocs)] | |
[.bind(name, value)] | |
.execute() |
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.
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.
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.
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.
Executes the document deletion with the configured filter and limit.
Result create_index | ( | str | name, |
JSON | indexDefinition | ||
) |
Creates an index on a collection.
name | the name of the index to be created. |
indexDefinition | a JSON document with the index information. |
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:
A single index_field description consists of the following fields:
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].
Result replace_one | ( | str | id, |
document | doc | ||
) |
Replaces an existing document with a new document.
id | identifier of the document to be replaced. |
doc | the new document. |
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.
Result add_or_replace_one | ( | str | id, |
document | doc | ||
) |
Replaces or adds a document in a collection.
id | the identifier of the document to be replaced. |
doc | the new document. |
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:
document get_one | ( | str | id | ) |
Fetches the document with the given _id from the collection.
id | The identifier of the document to be retrieved. |