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

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'.

.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

name	the name of the index to be created.
indexDefinition	a 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

id	identifier of the document to be replaced.
doc	the 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

id	the identifier of the document to be replaced.
doc	the 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

id	The 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

id	The 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.

Methods

Additional Inherited Members

Detailed Description

Member Function Documentation

◆ add()

Full Syntax

.add(...)

Overloads

.execute()

Examples

Using a Document List

Multiple Parameters

Chaining Addition

◆ find()

Full Syntax

.find([searchCondition])

.fields(...)

Overloads

.groupBy(...)

Overloads

.having(condition)

.sort(...)

Overloads

.limit(numberOfDocs)

.offset(quantity)

.lockShared([lockContention])

.lockExclusive([lockContention])

.bind(name, value)

.execute()

Examples

Retrieving All Documents

Filtering

Field Selection

Sorting

Using Limit and Offset

Parameter Binding

◆ modify()

Full Syntax

.modify(searchCondition)

.set(attribute, value)

Using Expressions for Values

.unset(...)

Overloads

.patch(document)

.arrayInsert(docPath, value)

.arrayAppend(docPath, value)

.sort(...)

Overloads

.limit(numberOfDocs)

.bind(name, value)

.execute()

Examples

Modify multiple attributes

Modify an attribute with an array value

Unset an attribute

Unset multiple attributes using an array

Patch multiple attributes

Append to an array attribute

Insert into an array attribute

Sorting and setting a limit

◆ remove()

Full Syntax

.remove(searchCondition)

.sort(...)

Overloads

.limit(numberOfDocs)

.bind(name, value)

.execute()

Examples

Remove under condition

Remove with binding

Full remove

◆ createIndex()

◆ replaceOne()

◆ addOrReplaceOne()

◆ getOne()

◆ removeOne()