1Collections
2===========
3
4Documents of the same type are grouped together and stored in the database as collections. The X DevAPI uses Collection objects to store and retrieve documents.
5
6Creating collections
7--------------------
8
9In order to create a new collection call the :func:`mysqlx.Schema.create_collection()` function from a :class:`mysqlx.Schema` object. It returns a Collection object that can be used right away, for example to insert documents into the collection.
10
11Optionally, the argument ``reuse_existing`` can be set to ``True`` to prevent an error being generated if a collection with the same name already exists.
12
13.. code-block:: python
14
15   import mysqlx
16
17   # Connect to server on localhost
18   session = mysqlx.get_session({
19       'host': 'localhost',
20       'port': 33060,
21       'user': 'mike',
22       'password': 's3cr3t!'
23   })
24
25   schema = session.get_schema('test')
26
27   # Create 'my_collection' in schema
28   schema.create_collection('my_collection', reuse_existing=True)
29
30Schema validation
31~~~~~~~~~~~~~~~~~
32
33Optionally, the argument ``validation`` can be set to create a server-side document validation schema. This argument should be a :class:`dict`, which includes a ``schema`` key matching a valid `JSON schema <https://json-schema.org/>`_ definition. You should also include the ``level`` key to effectively enable (`STRICT`) or disable (`OFF`) it.
34
35.. code-block:: python
36
37   validation = {
38       "level": "STRICT",
39       "schema": {
40           "id": "http://json-schema.org/geo",
41           "$schema": "http://json-schema.org/draft-07/schema#",
42           "title": "Longitude and Latitude Values",
43           "description": "A geographical coordinate",
44           "required": ["latitude", "longitude"],
45           "type": "object",
46           "properties": {
47               "latitude": {
48                   "type": "number",
49                    "minimum": -90,
50                   "maximum": 90
51               },
52               "longitude": {
53                   "type": "number",
54                   "minimum": -180,
55                   "maximum": 180
56               }
57           },
58       }
59   }
60
61   # Create 'my_collection' in schema with a schema validation
62   schema.create_collection('my_collection', validation=validation)
63
64When trying to insert a document that violates the schema definition for the collection, an error is thrown.
65
66Modifying collections
67---------------------
68
69To enable a JSON schema validation on an existing collection (or to update it if already exists), you can use :func:`mysqlx.Schema.modify_collection()` function.
70
71.. code-block:: python
72
73   # Using the same 'validation' dictionary used above, we can
74   # modify 'my_collection' to include a schema validation
75   schema.modify_collection('my_collection', validation=validation)
76
77Using Collection patch (:func:`mysqlx.ModifyStatement.patch()`)
78---------------------------------------------------------------
79
80First we need to get a session and a schema.
81
82.. code-block:: python
83
84   import mysqlx
85
86   # Connect to server on localhost
87   session = mysqlx.get_session({
88       'host': 'localhost',
89       'port': 33060,
90       'user': 'mike',
91       'password': 's3cr3t!'
92   })
93
94   schema = session.get_schema('test')
95
96Next step is create a sample collection and add some sample data.
97
98.. code-block:: python
99
100   # Create 'collection_GOT' in schema
101   schema.create_collection('collection_GOT')
102
103   # Get 'collection_GOT' from schema
104   collection = schema.get_collection('collection_GOT')
105
106   collection.add(
107       {"name": "Bran", "family_name": "Stark", "age": 18,
108        "parents": ["Eddard Stark", "Catelyn Stark"]},
109       {"name": "Sansa", "family_name": "Stark", "age": 21,
110        "parents": ["Eddard Stark", "Catelyn Stark"]},
111        {"name": "Arya", "family_name": "Stark", "age": 20,
112        "parents": ["Eddard Stark", "Catelyn Stark"]},
113       {"name": "Jon", "family_name": "Snow", "age": 30},
114       {"name": "Daenerys", "family_name": "Targaryen", "age": 30},
115       {"name": "Margaery", "family_name": "Tyrell", "age": 35},
116       {"name": "Cersei", "family_name": "Lannister", "age": 44,
117        "parents": ["Tywin Lannister, Joanna Lannister"]},
118       {"name": "Tyrion", "family_name": "Lannister", "age": 48,
119        "parents": ["Tywin Lannister, Joanna Lannister"]},
120   ).execute()
121
122This example shows how to add a new field to a matching  documents in a
123collection, in this case the new field name will be ``_is`` with the value
124of ``young`` for those documents with ``age`` field equal or smaller than 21 and
125the value ``old`` for documents with ``age`` field value greater than 21.
126
127.. code-block:: python
128
129   collection.modify("age <= 21").patch(
130       '{"_is": "young"}').execute()
131   collection.modify("age > 21").patch(
132       '{"_is": "old"}').execute()
133
134   for doc in mys.collection.find().execute().fetch_all():
135       if doc.age <= 21:
136           assert(doc._is == "young")
137       else:
138           assert(doc._is == "old")
139
140This example shows how to add a new field with an array value.
141The code will add the field "parents" with the value of
142``["Mace Tyrell", "Alerie Tyrell"]``
143to documents whose ``family_name`` field has value ``Tyrell``.
144
145.. code-block:: python
146
147   collection.modify('family_name == "Tyrell"').patch(
148       {"parents": ["Mace Tyrell", "Alerie Tyrell"]}).execute()
149   doc = collection.find("name = 'Margaery'").execute().fetch_all()[0]
150
151   assert(doc.parents == ["Mace Tyrell", "Alerie Tyrell"])
152
153
154This example shows how to add a new field ``dragons`` with a JSON document as
155value.
156
157.. code-block:: python
158
159   collection.modify('name == "Daenerys"').patch('''
160   {"dragons":{"drogon": "dark grayish with red markings",
161               "Rhaegal": "green with bronze markings",
162               "Viserion": "creamy white, with gold markings",
163               "count": 3}}
164               ''').execute()
165   doc = collection.find("name = 'Daenerys'").execute().fetch_all()[0]
166   assert(doc.dragons == {"count": 3,
167                          "drogon": "dark grayish with red markings",
168                          "Rhaegal": "green with bronze markings",
169                          "Viserion": "creamy white, with gold markings"})
170
171
172This example uses the previews one to show how to remove of the nested field
173``Viserion`` on ``dragons`` field and at the same time how to update the value of
174the ``count`` field with a new value based in the current one.
175
176.. note:: In the :func:`mysqlx.ModifyStatement.patch()` all strings are considered literals,
177          for expressions the usage of the :func:`mysqlx.expr()` is required.
178
179.. code-block:: python
180
181   collection.modify('name == "Daenerys"').patch(mysqlx.expr('''
182       JSON_OBJECT("dragons", JSON_OBJECT("count", $.dragons.count -1,
183                                           "Viserion", Null))
184       ''')).execute()
185   doc = mys.collection.find("name = 'Daenerys'").execute().fetch_all()[0]
186   assert(doc.dragons == {'count': 2,
187                          'Rhaegal': 'green with bronze markings',
188