Chapter 20 Using MySQL as a Document Store

To show the value that is assigned to the schema variable, issue:

mysql-js> db

If the schema value is not Schema:world_x, then set the db variable by issuing:

mysql-js> \use world_x

To create a new collection in an existing schema, use the db object's createCollection() method. The following example creates a collection called flags in the world_x schema.

mysql-js> db.createCollection("flags")

The method returns a collection object.

<Collection:flags>

To display all collections in the world_x schema, use the db object's getCollections() method. Collections returned by the server you are currently connected to appear between brackets.

mysql-js> db.getCollections()
[
    <Collection:countryinfo>,
    <Collection:flags>
]

To drop an existing collection from a schema, use the db object's dropCollection() method. For example, to drop the flags collection from the current schema, issue:

mysql-js> db.dropCollection("flags")

The dropCollection() method is also used in MySQL Shell to drop a relational table from a schema.

Use the add() method to insert one document or a list of documents into an existing collection. Insert the following document into the countryinfo collection. As this is multi-line content, press Enter twice to insert the document.

mysql-js> db.countryinfo.add(
 {
    GNP: .6,
    IndepYear: 1967,
    Name: "Sealand",
    Code: "SEA",
    demographics: {
        LifeExpectancy: 79,
        Population: 27
    },
    geography: {
        Continent: "Europe",
        Region: "British Islands",
        SurfaceArea: 193
    },
    government: {
        GovernmentForm: "Monarchy",
        HeadOfState: "Michael Bates"
    }
  }
)

The method returns the status of the operation. You can verify the operation by searching for the document. For example:

mysql-js> db.countryinfo.find("Name = 'Sealand'")
{
    "GNP": 0.6,
    "_id": "00005e2ff4af00000000000000f4",
    "Name": "Sealand",
    "Code:": "SEA",
    "IndepYear": 1967,
    "geography": {
        "Region": "British Islands",
        "Continent": "Europe",
        "SurfaceArea": 193
    },
    "government": {
        "HeadOfState": "Michael Bates",
        "GovernmentForm": "Monarchy"
    },
    "demographics": {
        "Population": 27,
        "LifeExpectancy": 79
    }
}

Note that in addition to the fields specified when the document was added, there is one more field, the _id. Each document requires an identifier field called _id. The value of the _id field must be unique among all documents in the same collection. In MySQL 8.0.11 and higher, document IDs are generated by the server, not the client, so MySQL Shell does not automatically set an _id value. A MySQL server at 8.0.11 or higher sets an _id value if the document does not contain the _id field. A MySQL server at an earlier 8.0 release or at 5.7 does not set an _id value in this situation, so you must specify it explicitly. If you do not, MySQL Shell returns error 5115 Document is missing a required field. For more information see Understanding Document IDs.

To return all documents in a collection, use the find() method without specifying search conditions. For example, the following operation returns all documents in the countryinfo collection.

mysql-js> db.countryinfo.find()
[
     {
          "GNP": 828,
          "Code:": "ABW",
          "Name": "Aruba",
          "IndepYear": null,
          "geography": {
              "Continent": "North America",
              "Region": "Caribbean",
              "SurfaceArea": 193
          },
          "government": {
              "GovernmentForm": "Nonmetropolitan Territory of The Netherlands",
              "HeadOfState": "Beatrix"
          }
          "demographics": {
              "LifeExpectancy": 78.4000015258789,
              "Population": 103000
          },
          ...
      }
 ]
240 documents in set (0.00 sec)

The method produces results that contain operational information in addition to all documents in the collection.

An empty set (no matching documents) returns the following information:

Empty set (0.00 sec)

You can include search conditions with the find() method. The syntax for expressions that form a search condition is the same as that of traditional MySQL Chapter 12, Functions and Operators. You must enclose all expressions in quotes. For the sake of brevity, some of the examples do not display output.

A simple search condition could consist of the Name field and a value we know is in a document. The following example returns a single document:

mysql-js> db.countryinfo.find("Name = 'Australia'")
[
    {
        "GNP": 351182,
        "Code:": "AUS",
        "Name": "Australia",
        "IndepYear": 1901,
        "geography": {
            "Continent": "Oceania",
            "Region": "Australia and New Zealand",
            "SurfaceArea": 7741220
        },
        "government": {
            "GovernmentForm": "Constitutional Monarchy, Federation",
            "HeadOfState": "Elisabeth II"
        }
        "demographics": {
            "LifeExpectancy": 79.80000305175781,
            "Population": 18886000
        },
    }
]

The following example searches for all countries that have a GNP higher than $500 billion. The countryinfo collection measures GNP in units of million.

mysql-js> db.countryinfo.find("GNP > 500000")
...[output removed]
10 documents in set (0.00 sec)

The Population field in the following query is embedded within the demographics object. To access the embedded field, use a period between demographics and Population to identify the relationship. Document and field names are case-sensitive.

mysql-js> db.countryinfo.find("GNP > 500000 and demographics.Population < 100000000")
...[output removed]
6 documents in set (0.00 sec)

Arithmetic operators in the following expression are used to query for countries with a GNP per capita higher than $30000. Search conditions can include arithmetic operators and most MySQL functions.

mysql-js> db.countryinfo.find("GNP*1000000/demographics.Population > 30000")
...[output removed]
9 documents in set, 7 warnings (0.00 sec)
Warning (Code 1365): Division by 0
Warning (Code 1365): Division by 0
Warning (Code 1365): Division by 0
Warning (Code 1365): Division by 0
Warning (Code 1365): Division by 0
Warning (Code 1365): Division by 0
Warning (Code 1365): Division by 0

You can separate a value from the search condition by using the bind() method. For example, instead of specifying a hard-coded country name as the condition, substitute a named placeholder consisting of a colon followed by a name that begins with a letter, such as country. Then use the bind(placeholder, value) method as follows:

mysql-js> db.countryinfo.find("Name = :country").bind("country", "Italy")
{
    "GNP": 1161755,
    "_id": "00005de917d8000000000000006a",
    "Code": "ITA",
    "Name": "Italy",
    "Airports": [],
    "IndepYear": 1861,
    "geography": {
        "Region": "Southern Europe",
        "Continent": "Europe",
        "SurfaceArea": 301316
    },
    "government": {
        "HeadOfState": "Carlo Azeglio Ciampi",
        "GovernmentForm": "Republic"
    },
    "demographics": {
        "Population": 57680000,
        "LifeExpectancy": 79
    }
}
1 document in set (0.01 sec)

You can use placeholders and the bind() method to create saved searches which you can then call with different values. For example to create a saved search for a country:

mysql-js> var myFind = db.countryinfo.find("Name = :country")
mysql-js> myFind.bind('country', 'France')
{
    "GNP": 1424285,
    "_id": "00005de917d80000000000000048",
    "Code": "FRA",
    "Name": "France",
    "IndepYear": 843,
    "geography": {
        "Region": "Western Europe",
        "Continent": "Europe",
        "SurfaceArea": 551500
    },
    "government": {
        "HeadOfState": "Jacques Chirac",
        "GovernmentForm": "Republic"
    },
    "demographics": {
        "Population": 59225700,
        "LifeExpectancy": 78.80000305175781
    }
}
1 document in set (0.0028 sec)

mysql-js> myFind.bind('country', 'Germany')
{
    "GNP": 2133367,
    "_id": "00005de917d80000000000000038",
    "Code": "DEU",
    "Name": "Germany",
    "IndepYear": 1955,
    "geography": {
        "Region": "Western Europe",
        "Continent": "Europe",
        "SurfaceArea": 357022
    },
    "government": {
        "HeadOfState": "Johannes Rau",
        "GovernmentForm": "Federal Republic"
    },
    "demographics": {
        "Population": 82164700,
        "LifeExpectancy": 77.4000015258789
    }
}

1 document in set (0.0026 sec)

You can return specific fields of a document, instead of returning all the fields. The following example returns the GNP and Name fields of all documents in the countryinfo collection matching the search conditions.

Use the fields() method to pass the list of fields to return.

mysql-js> db.countryinfo.find("GNP > 5000000").fields(["GNP", "Name"])
[
    {
        "GNP": 8510700,
        "Name": "United States"
    }
]
1 document in set (0.00 sec)
 

In addition, you can alter the returned documents—adding, renaming, nesting and even computing new field values—with an expression that describes the document to return. For example, alter the names of the fields with the following expression to return only two documents.

mysql-js> db.countryinfo.find().fields(
mysqlx.expr('{"Name": upper(Name), "GNPPerCapita": GNP*1000000/demographics.Population}')).limit(2)
{
    "Name": "ARUBA",
    "GNPPerCapita": 8038.834951456311
}
{
    "Name": "AFGHANISTAN",
    "GNPPerCapita": 263.0281690140845
}

You can apply the limit(), sort(), and skip() methods to manage the number and order of documents returned by the find() method.

To specify the number of documents included in a result set, append the limit() method with a value to the find() method. The following query returns the first five documents in the countryinfo collection.

mysql-js> db.countryinfo.find().limit(5)
... [output removed]
5 documents in set (0.00 sec)

To specify an order for the results, append the sort() method to the find() method. Pass to the sort() method a list of one or more fields to sort by and, optionally, the descending (desc) or ascending (asc) attribute as appropriate. Ascending order is the default order type.

For example, the following query sorts all documents by the IndepYear field and then returns the first eight documents in descending order.

mysql-js> db.countryinfo.find().sort(["IndepYear desc"]).limit(8)
... [output removed]
8 documents in set (0.00 sec)

By default, the limit() method starts from the first document in the collection. You can use the skip() method to change the starting document. For example, to ignore the first document and return the next eight documents matching the condition, pass to the skip() method a value of 1.

mysql-js> db.countryinfo.find().sort(["IndepYear desc"]).limit(8).skip(1)
... [output removed]
8 documents in set (0.00 sec)

The modify() method works by filtering a collection to include only the documents to be modified and then applying the operations that you specify to those documents.

In the following example, the modify() method uses the search condition to identify the document to change and then the set() method replaces two values within the nested demographics object.

mysql-js> db.countryinfo.modify("Code = 'SEA'").set(
"demographics", {"LifeExpectancy": 78, "Population": 28})

After you modify a document, use the find() method to verify the change.

To remove content from a document, use the modify() and unset() methods. For example, the following query removes the GNP from a document that matches the search condition.

mysql-js> db.countryinfo.modify("Name = 'Sealand'").unset("GNP")

Use the find() method to verify the change.

mysql-js> db.countryinfo.find("Name = 'Sealand'")
{
    "_id": "00005e2ff4af00000000000000f4",
    "Name": "Sealand",
    "Code:": "SEA",
    "IndepYear": 1967,
    "geography": {
        "Region": "British Islands",
        "Continent": "Europe",
        "SurfaceArea": 193
    },
    "government": {
        "HeadOfState": "Michael Bates",
        "GovernmentForm": "Monarchy"
    },
    "demographics": {
        "Population": 27,
        "LifeExpectancy": 79
    }
}

To append an element to an array field, or insert, or delete elements in an array, use the arrayAppend(), arrayInsert(), or arrayDelete() methods. The following examples modify the countryinfo collection to enable tracking of international airports.

The first example uses the modify() and set() methods to create a new Airports field in all documents.

mysql-js> db.countryinfo.modify("true").set("Airports", [])

With the Airports field added, the next example uses the arrayAppend() method to add a new airport to one of the documents. $.Airports in the following example represents the Airports field of the current document.

mysql-js> db.countryinfo.modify("Name = 'France'").arrayAppend("$.Airports", "ORY")

Use find() to see the change.

mysql-js> db.countryinfo.find("Name = 'France'")
{
    "GNP": 1424285,
    "_id": "00005de917d80000000000000048",
    "Code": "FRA",
    "Name": "France",
    "Airports": [
        "ORY"
    ],
    "IndepYear": 843,
    "geography": {
        "Region": "Western Europe",
        "Continent": "Europe",
        "SurfaceArea": 551500
    },
    "government": {
        "HeadOfState": "Jacques Chirac",
        "GovernmentForm": "Republic"
    },
    "demographics": {
        "Population": 59225700,
        "LifeExpectancy": 78.80000305175781
    }
}

To insert an element at a different position in the array, use the arrayInsert() method to specify which index to insert in the path expression. In this case, the index is 0, or the first element in the array.

mysql-js> db.countryinfo.modify("Name = 'France'").arrayInsert("$.Airports[0]", "CDG")

To delete an element from the array, you must pass to the arrayDelete() method the index of the element to be deleted.

mysql-js> db.countryinfo.modify("Name = 'France'").arrayDelete("$.Airports[1]")

The following example passes a search condition to the remove() method. All documents matching the condition are removed from the countryinfo collection. In this example, one document matches the condition.

mysql-js> db.countryinfo.remove("Code = 'SEA'")

To remove the first document in the countryinfo collection, use the limit() method with a value of 1.

mysql-js> db.countryinfo.remove("true").limit(1)

The following example removes the last document in the countryinfo collection by country name.

mysql-js> db.countryinfo.remove("true").sort(["Name desc"]).limit(1)

You can remove all documents in a collection. To do so, use the remove("true") method without specifying a search condition.

Alternatively, use the db.drop_collection('countryinfo') operation to delete the countryinfo collection.

To create a nonunique index, pass an index name and the index information to the createIndex() method. Duplicate index names are prohibited.

The following example specifies an index named popul, defined against the Population field from the demographics object, indexed as an Integer numeric value. The final parameter indicates whether the field should require the NOT NULL constraint. If the value is false, the field can contain NULL values. The index information is a JSON document with details of one or more fields to include in the index. Each field definition must include the full document path to the field, and specify the type of the field.

mysql-js> db.countryinfo.createIndex("popul", {fields:
[{field: '$.demographics.Population', type: 'INTEGER'}]})

Here, the index is created using an integer numeric value. Further options are available, including options for use with GeoJSON data. You can also specify the type of index, which has been omitted here because the default type “index” is appropriate.

To create a unique index, pass an index name, the index definition, and the index type “unique” to the createIndex() method. This example shows a unique index created on the country name ("Name"), which is another common field in the countryinfo collection to index. In the index field description, "TEXT(40)" represents the number of characters to index, and "required": True specifies that the field is required to exist in the document.

mysql-js> db.countryinfo.createIndex("name",
{"fields": [{"field": "$.Name", "type": "TEXT(40)", "required": true}], "unique": true})

To drop an index, pass the name of the index to drop to the dropIndex() method. For example, you can drop the “popul” index as follows:

mysql-js> db.countryinfo.dropIndex("popul")

To insert a complete record, pass to the insert() method all columns in the table. Then pass to the values() method one value for each column in the table. For example, to add a new record to the city table in the world_x schema, insert the following record and press Enter twice.

mysql-js> db.city.insert("ID", "Name", "CountryCode", "District", "Info").values(
None, "Olympia", "USA", "Washington", '{"Population": 5000}')

The city table has five columns: ID, Name, CountryCode, District, and Info. Each value must match the data type of the column it represents.

The following example inserts values into the ID, Name, and CountryCode columns of the city table.

mysql-js> db.city.insert("ID", "Name", "CountryCode").values(
None, "Little Falls", "USA").values(None, "Happy Valley", "USA")

When you specify columns using the insert() method, the number of values must match the number of columns. In the previous example, you must supply three values to match the three columns specified.

To issue a query that returns all records from an existing table, use the select() method without specifying search conditions. The following example selects all records from the city table in the world_x database.

mysql-js> db.city.select()
+------+------------+-------------+------------+-------------------------+
| ID   | Name       | CountryCode | District   | Info                    |
+------+------------+-------------+------------+-------------------------+
|    1 | Kabul      | AFG         | Kabol      |{"Population": 1780000}  |
|    2 | Qandahar   | AFG         | Qandahar   |{"Population": 237500}   |
|    3 | Herat      | AFG         | Herat      |{"Population": 186800}   |
...    ...          ...           ...          ...
| 4079 | Rafah      | PSE         | Rafah      |{"Population": 92020}    |
+------+------- ----+-------------+------------+-------------------------+
4082 rows in set (0.01 sec)

An empty set (no matching records) returns the following information:

Empty set (0.00 sec)

To issue a query that returns a set of table columns, use the select() method and specify the columns to return between square brackets. This query returns the Name and CountryCode columns from the city table.

mysql-js> db.city.select(["Name", "CountryCode"])
+-------------------+-------------+
| Name              | CountryCode |
+-------------------+-------------+
| Kabul             | AFG         |
| Qandahar          | AFG         |
| Herat             | AFG         |
| Mazar-e-Sharif    | AFG         |
| Amsterdam         | NLD         |
...                 ...
| Rafah             | PSE         |
| Olympia           | USA         |
| Little Falls      | USA         |
| Happy Valley      | USA         |
+-------------------+-------------+
4082 rows in set (0.00 sec)

To issue a query that returns rows matching specific search conditions, use the where() method to include those conditions. For example, the following example returns the names and country codes of the cities that start with the letter Z.

mysql-js> db.city.select(["Name", "CountryCode"]).where("Name like 'Z%'")
+-------------------+-------------+
| Name              | CountryCode |
+-------------------+-------------+
| Zaanstad          | NLD         |
| Zoetermeer        | NLD         |
| Zwolle            | NLD         |
| Zenica            | BIH         |
| Zagazig           | EGY         |
| Zaragoza          | ESP         |
| Zamboanga         | PHL         |
| Zahedan           | IRN         |
| Zanjan            | IRN         |
| Zabol             | IRN         |
| Zama              | JPN         |
| Zhezqazghan       | KAZ         |
| Zhengzhou         | CHN         |
...                 ...
| Zeleznogorsk      | RUS         |
+-------------------+-------------+
59 rows in set (0.00 sec)

You can separate a value from the search condition by using the bind() method. For example, instead of using "Name = 'Z%' " as the condition, substitute a named placeholder consisting of a colon followed by a name that begins with a letter, such as name. Then include the placeholder and value in the bind() method as follows:

mysql-js> db.city.select(["Name", "CountryCode"]).
              where("Name like :name").bind("name", "Z%")

To issue a query using the AND operator, add the operator between search conditions in the where() method.

mysql-js> db.city.select(["Name", "CountryCode"]).where(
"Name like 'Z%' and CountryCode = 'CHN'")
+----------------+-------------+
| Name           | CountryCode |
+----------------+-------------+
| Zhengzhou      | CHN         |
| Zibo           | CHN         |
| Zhangjiakou    | CHN         |
| Zhuzhou        | CHN         |
| Zhangjiang     | CHN         |
| Zigong         | CHN         |
| Zaozhuang      | CHN         |
...              ...
| Zhangjiagang   | CHN         |
+----------------+-------------+
22 rows in set (0.01 sec)

To specify multiple conditional operators, you can enclose the search conditions in parenthesis to change the operator precedence. The following example demonstrates the placement of AND and OR operators.

mysql-js> db.city.select(["Name", "CountryCode"]).
where("Name like 'Z%' and (CountryCode = 'CHN' or CountryCode = 'RUS')")
+-------------------+-------------+
| Name              | CountryCode |
+-------------------+-------------+
| Zhengzhou         | CHN         |
| Zibo              | CHN         |
| Zhangjiakou       | CHN         |
| Zhuzhou           | CHN         |
...                 ...
| Zeleznogorsk      | RUS         |
+-------------------+-------------+
29 rows in set (0.01 sec)

You can apply the limit(), orderBy(), and offSet() methods to manage the number and order of records returned by the select() method.

To specify the number of records included in a result set, append the limit() method with a value to the select() method. For example, the following query returns the first five records in the country table.

mysql-js> db.country.select(["Code", "Name"]).limit(5)
+------+-------------+
| Code | Name        |
+------+-------------+
| ABW  | Aruba       |
| AFG  | Afghanistan |
| AGO  | Angola      |
| AIA  | Anguilla    |
| ALB  | Albania     |
+------+-------------+
5 rows in set (0.00 sec)

To specify an order for the results, append the orderBy() method to the select() method. Pass to the orderBy() method a list of one or more columns to sort by and, optionally, the descending (desc) or ascending (asc) attribute as appropriate. Ascending order is the default order type.

For example, the following query sorts all records by the Name column and then returns the first three records in descending order .

mysql-js> db.country.select(["Code", "Name"]).orderBy(["Name desc"]).limit(3)
+------+------------+
| Code | Name       |
+------+------------+
| ZWE  | Zimbabwe   |
| ZMB  | Zambia     |
| YUG  | Yugoslavia |
+------+------------+
3 rows in set (0.00 sec)

By default, the limit() method starts from the first record in the table. You can use the offset() method to change the starting record. For example, to ignore the first record and return the next three records matching the condition, pass to the offset() method a value of 1.

mysql-js> db.country.select(["Code", "Name"]).orderBy(["Name desc"]).limit(3).offset(1)
+------+------------+
| Code | Name       |
+------+------------+
| ZMB  | Zambia     |
| YUG  | Yugoslavia |
| YEM  | Yemen      |
+------+------------+
3 rows in set (0.00 sec)

The following example passes search conditions to the delete() method. All records matching the condition are deleted from the city table. In this example, one record matches the condition.

mysql-js> db.city.delete().where("Name = 'Olympia'")

To delete the first record in the city table, use the limit() method with a value of 1.

mysql-js> db.city.delete().limit(1)

You can delete all records in a table. To do so, use the delete() method without specifying a search condition.

The dropCollection() method is also used in MySQL Shell to drop a relational table from a database. For example, to drop the citytest table from the world_x database, issue:

mysql-js> session.dropCollection("world_x", "citytest")

To show the value that is assigned to the schema variable, issue:

mysql-py> db

If the schema value is not Schema:world_x, then set the db variable by issuing:

mysql-py> \use world_x

To create a new collection in an existing schema, use the db object's createCollection() method. The following example creates a collection called flags in the world_x schema.

mysql-py> db.create_collection("flags")

The method returns a collection object.

<Collection:flags>

To display all collections in the world_x schema, use the db object's get_collections() method. Collections returned by the server you are currently connected to appear between brackets.

mysql-py> db.get_collections()
[
    <Collection:countryinfo>,
    <Collection:flags>
]

To drop an existing collection from a schema, use the db object's drop_collection() method. For example, to drop the flags collection from the current schema, issue:

mysql-py> db.drop_collection("flags")

The drop_collection() method is also used in MySQL Shell to drop a relational table from a schema.

Use the add() method to insert one document or a list of documents into an existing collection. Insert the following document into the countryinfo collection. As this is multi-line content, press Enter twice to insert the document.

mysql-py> db.countryinfo.add(
 {
    "GNP": .6,
    "IndepYear": 1967,
    "Name": "Sealand",
    "Code:": "SEA",
    "demographics": {
        "LifeExpectancy": 79,
        "Population": 27
    },
    "geography": {
        "Continent": "Europe",
        "Region": "British Islands",
        "SurfaceArea": 193
    },
    "government": {
        "GovernmentForm": "Monarchy",
        "HeadOfState": "Michael Bates"
    }
  }
)

The method returns the status of the operation. You can verify the operation by searching for the document. For example:

mysql-py> db.countryinfo.find("Name = 'Sealand'")
{
    "GNP": 0.6,
    "_id": "00005e2ff4af00000000000000f4",
    "Name": "Sealand",
    "Code:": "SEA",
    "IndepYear": 1967,
    "geography": {
        "Region": "British Islands",
        "Continent": "Europe",
        "SurfaceArea": 193
    },
    "government": {
        "HeadOfState": "Michael Bates",
        "GovernmentForm": "Monarchy"
    },
    "demographics": {
        "Population": 27,
        "LifeExpectancy": 79
    }
}

Note that in addition to the fields specified when the document was added, there is one more field, the _id. Each document requires an identifier field called _id. The value of the _id field must be unique among all documents in the same collection. In MySQL 8.0.11 and higher, document IDs are generated by the server, not the client, so MySQL Shell does not automatically set an _id value. A MySQL server at 8.0.11 or higher sets an _id value if the document does not contain the _id field. A MySQL server at an earlier 8.0 release or at 5.7 does not set an _id value in this situation, so you must specify it explicitly. If you do not, MySQL Shell returns error 5115 Document is missing a required field. For more information see Understanding Document IDs.

To return all documents in a collection, use the find() method without specifying search conditions. For example, the following operation returns all documents in the countryinfo collection.

mysql-py> db.countryinfo.find()
[
     {
          "GNP": 828,
          "Code:": "ABW",
          "Name": "Aruba",
          "IndepYear": null,
          "geography": {
              "Continent": "North America",
              "Region": "Caribbean",
              "SurfaceArea": 193
          },
          "government": {
              "GovernmentForm": "Nonmetropolitan Territory of The Netherlands",
              "HeadOfState": "Beatrix"
          }
          "demographics": {
              "LifeExpectancy": 78.4000015258789,
              "Population": 103000
          },
          ...
      }
 ]
240 documents in set (0.00 sec)

The method produces results that contain operational information in addition to all documents in the collection.

An empty set (no matching documents) returns the following information:

Empty set (0.00 sec)

You can include search conditions with the find() method. The syntax for expressions that form a search condition is the same as that of traditional MySQL Chapter 12, Functions and Operators. You must enclose all expressions in quotes. For the sake of brevity, some of the examples do not display output.

A simple search condition could consist of the Name field and a value we know is in a document. The following example returns a single document:

mysql-py> db.countryinfo.find("Name = 'Australia'")
[
    {
        "GNP": 351182,
        "Code:": "AUS",
        "Name": "Australia",
        "IndepYear": 1901,
        "geography": {
            "Continent": "Oceania",
            "Region": "Australia and New Zealand",
            "SurfaceArea": 7741220
        },
        "government": {
            "GovernmentForm": "Constitutional Monarchy, Federation",
            "HeadOfState": "Elisabeth II"
        }
        "demographics": {
            "LifeExpectancy": 79.80000305175781,
            "Population": 18886000
        },
    }
]

The following example searches for all countries that have a GNP higher than $500 billion. The countryinfo collection measures GNP in units of million.

mysql-py> db.countryinfo.find("GNP > 500000")
...[output removed]
10 documents in set (0.00 sec)

The Population field in the following query is embedded within the demographics object. To access the embedded field, use a period between demographics and Population to identify the relationship. Document and field names are case-sensitive.

mysql-py> db.countryinfo.find("GNP > 500000 and demographics.Population < 100000000")
...[output removed]
6 documents in set (0.00 sec)

Arithmetic operators in the following expression are used to query for countries with a GNP per capita higher than $30000. Search conditions can include arithmetic operators and most MySQL functions.

mysql-py> db.countryinfo.find("GNP*1000000/demographics.Population > 30000")
...[output removed]
9 documents in set, 7 warnings (0.00 sec)
Warning (Code 1365): Division by 0
Warning (Code 1365): Division by 0
Warning (Code 1365): Division by 0
Warning (Code 1365): Division by 0
Warning (Code 1365): Division by 0
Warning (Code 1365): Division by 0
Warning (Code 1365): Division by 0

You can separate a value from the search condition by using the bind() method. For example, instead of specifying a hard-coded country name as the condition, substitute a named placeholder consisting of a colon followed by a name that begins with a letter, such as country. Then use the bind(placeholder, value) method as follows:

mysql-py> db.countryinfo.find("Name = :country").bind("country", "Italy")
{
    "GNP": 1161755,
    "_id": "00005de917d8000000000000006a",
    "Code": "ITA",
    "Name": "Italy",
    "Airports": [],
    "IndepYear": 1861,
    "geography": {
        "Region": "Southern Europe",
        "Continent": "Europe",
        "SurfaceArea": 301316
    },
    "government": {
        "HeadOfState": "Carlo Azeglio Ciampi",
        "GovernmentForm": "Republic"
    },
    "demographics": {
        "Population": 57680000,
        "LifeExpectancy": 79
    }
}
1 document in set (0.01 sec)

You can use placeholders and the bind() method to create saved searches which you can then call with different values. For example to create a saved search for a country:

mysql-py> myFind = db.countryinfo.find("Name = :country")
mysql-py> myFind.bind('country', 'France')
{
    "GNP": 1424285,
    "_id": "00005de917d80000000000000048",
    "Code": "FRA",
    "Name": "France",
    "IndepYear": 843,
    "geography": {
        "Region": "Western Europe",
        "Continent": "Europe",
        "SurfaceArea": 551500
    },
    "government": {
        "HeadOfState": "Jacques Chirac",
        "GovernmentForm": "Republic"
    },
    "demographics": {
        "Population": 59225700,
        "LifeExpectancy": 78.80000305175781
    }
}
1 document in set (0.0028 sec)

mysql-py> myFind.bind('country', 'Germany')
{
    "GNP": 2133367,
    "_id": "00005de917d80000000000000038",
    "Code": "DEU",
    "Name": "Germany",
    "IndepYear": 1955,
    "geography": {
        "Region": "Western Europe",
        "Continent": "Europe",
        "SurfaceArea": 357022
    },
    "government": {
        "HeadOfState": "Johannes Rau",
        "GovernmentForm": "Federal Republic"
    },
    "demographics": {
        "Population": 82164700,
        "LifeExpectancy": 77.4000015258789
    }
}

1 document in set (0.0026 sec)

You can return specific fields of a document, instead of returning all the fields. The following example returns the GNP and Name fields of all documents in the countryinfo collection matching the search conditions.

Use the fields() method to pass the list of fields to return.

mysql-py> db.countryinfo.find("GNP > 5000000").fields(["GNP", "Name"])
[
    {
        "GNP": 8510700,
        "Name": "United States"
    }
]
1 document in set (0.00 sec)
 

In addition, you can alter the returned documents—adding, renaming, nesting and even computing new field values—with an expression that describes the document to return. For example, alter the names of the fields with the following expression to return only two documents.

mysql-py> db.countryinfo.find().fields(
mysqlx.expr('{"Name": upper(Name), "GNPPerCapita": GNP*1000000/demographics.Population}')).limit(2)
{
    "Name": "ARUBA",
    "GNPPerCapita": 8038.834951456311
}
{
    "Name": "AFGHANISTAN",
    "GNPPerCapita": 263.0281690140845
}

You can apply the limit(), sort(), and skip() methods to manage the number and order of documents returned by the find() method.

To specify the number of documents included in a result set, append the limit() method with a value to the find() method. The following query returns the first five documents in the countryinfo collection.

mysql-py> db.countryinfo.find().limit(5)
... [output removed]
5 documents in set (0.00 sec)

To specify an order for the results, append the sort() method to the find() method. Pass to the sort() method a list of one or more fields to sort by and, optionally, the descending (desc) or ascending (asc) attribute as appropriate. Ascending order is the default order type.

For example, the following query sorts all documents by the IndepYear field and then returns the first eight documents in descending order.

mysql-py> db.countryinfo.find().sort(["IndepYear desc"]).limit(8)
... [output removed]
8 documents in set (0.00 sec)

By default, the limit() method starts from the first document in the collection. You can use the skip() method to change the starting document. For example, to ignore the first document and return the next eight documents matching the condition, pass to the skip() method a value of 1.

mysql-py> db.countryinfo.find().sort(["IndepYear desc"]).limit(8).skip(1)
... [output removed]
8 documents in set (0.00 sec)

The modify() method works by filtering a collection to include only the documents to be modified and then applying the operations that you specify to those documents.

In the following example, the modify() method uses the search condition to identify the document to change and then the set() method replaces two values within the nested demographics object.

mysql-py> db.countryinfo.modify("Code = 'SEA'").set(
"demographics", {"LifeExpectancy": 78, "Population": 28})

After you modify a document, use the find() method to verify the change.

To remove content from a document, use the modify() and unset() methods. For example, the following query removes the GNP from a document that matches the search condition.

mysql-py> db.countryinfo.modify("Name = 'Sealand'").unset("GNP")

Use the find() method to verify the change.

mysql-py> db.countryinfo.find("Name = 'Sealand'")
{
    "_id": "00005e2ff4af00000000000000f4",
    "Name": "Sealand",
    "Code:": "SEA",
    "IndepYear": 1967,
    "geography": {
        "Region": "British Islands",
        "Continent": "Europe",
        "SurfaceArea": 193
    },
    "government": {
        "HeadOfState": "Michael Bates",
        "GovernmentForm": "Monarchy"
    },
    "demographics": {
        "Population": 27,
        "LifeExpectancy": 79
    }
}

To append an element to an array field, or insert, or delete elements in an array, use the array_append(), array_insert(), or array_delete() methods. The following examples modify the countryinfo collection to enable tracking of international airports.

The first example uses the modify() and set() methods to create a new Airports field in all documents.

mysql-py> db.countryinfo.modify("true").set("Airports", [])

With the Airports field added, the next example uses the array_append() method to add a new airport to one of the documents. $.Airports in the following example represents the Airports field of the current document.

mysql-py> db.countryinfo.modify("Name = 'France'").array_append("$.Airports", "ORY")

Use find() to see the change.

mysql-py> db.countryinfo.find("Name = 'France'")
{
    "GNP": 1424285,
    "_id": "00005de917d80000000000000048",
    "Code": "FRA",
    "Name": "France",
    "Airports": [
        "ORY"
    ],
    "IndepYear": 843,
    "geography": {
        "Region": "Western Europe",
        "Continent": "Europe",
        "SurfaceArea": 551500
    },
    "government": {
        "HeadOfState": "Jacques Chirac",
        "GovernmentForm": "Republic"
    },
    "demographics": {
        "Population": 59225700,
        "LifeExpectancy": 78.80000305175781
    }
}

To insert an element at a different position in the array, use the array_insert() method to specify which index to insert in the path expression. In this case, the index is 0, or the first element in the array.

mysql-py> db.countryinfo.modify("Name = 'France'").array_insert("$.Airports[0]", "CDG")

To delete an element from the array, you must pass to the array_delete() method the index of the element to be deleted.

mysql-py> db.countryinfo.modify("Name = 'France'").array_delete("$.Airports[1]")

The following example passes a search condition to the remove() method. All documents matching the condition are removed from the countryinfo collection. In this example, one document matches the condition.

mysql-py> db.countryinfo.remove("Code = 'SEA'")

To remove the first document in the countryinfo collection, use the limit() method with a value of 1.

mysql-py> db.countryinfo.remove("true").limit(1)

The following example removes the last document in the countryinfo collection by country name.

mysql-py> db.countryinfo.remove("true").sort(["Name desc"]).limit(1)

You can remove all documents in a collection. To do so, use the remove("true") method without specifying a search condition.

Alternatively, use the db.drop_collection('countryinfo') operation to delete the countryinfo collection.

To create a nonunique index, pass an index name and the index information to the create_index() method. Duplicate index names are prohibited.

The following example specifies an index named popul, defined against the Population field from the demographics object, indexed as an Integer numeric value. The final parameter indicates whether the field should require the NOT NULL constraint. If the value is false, the field can contain NULL values. The index information is a JSON document with details of one or more fields to include in the index. Each field definition must include the full document path to the field, and specify the type of the field.

mysql-py> db.countryinfo.createIndex("popul", {fields:
[{field: '$.demographics.Population', type: 'INTEGER'}]})

Here, the index is created using an integer numeric value. Further options are available, including options for use with GeoJSON data. You can also specify the type of index, which has been omitted here because the default type “index” is appropriate.

To create a unique index, pass an index name, the index definition, and the index type “unique” to the create_index() method. This example shows a unique index created on the country name ("Name"), which is another common field in the countryinfo collection to index. In the index field description, "TEXT(40)" represents the number of characters to index, and "required": True specifies that the field is required to exist in the document.

mysql-py> db.countryinfo.create_index("name",
{"fields": [{"field": "$.Name", "type": "TEXT(40)", "required": True}], "unique": True})

To drop an index, pass the name of the index to drop to the drop_index() method. For example, you can drop the “popul” index as follows:

mysql-py> db.countryinfo.drop_index("popul")

To insert a complete record, pass to the insert() method all columns in the table. Then pass to the values() method one value for each column. For example, to add a new record to the city table in the world_x database, insert the following record and press Enter twice.

mysql-py> db.city.insert("ID", "Name", "CountryCode", "District", "Info").values(
None, "Olympia", "USA", "Washington", '{"Population": 5000}')

The city table has five columns: ID, Name, CountryCode, District, and Info. Each value must match the data type of the column it represents.

The following example inserts values into the ID, Name, and CountryCode columns of the city table.

mysql-py> db.city.insert("ID", "Name", "CountryCode").values(
None, "Little Falls", "USA").values(None, "Happy Valley", "USA")

When you specify columns using the insert() method, the number of values must match the number of columns. In the previous example, you must supply three values to match the three columns specified.

To issue a query that returns all records from an existing table, use the select() method without specifying search conditions. The following example selects all records from the city table in the world_x database.

mysql-py> db.city.select()
+------+------------+-------------+------------+-------------------------+
| ID   | Name       | CountryCode | District   | Info                    |
+------+------------+-------------+------------+-------------------------+
|    1 | Kabul      | AFG         | Kabol      |{"Population": 1780000}  |
|    2 | Qandahar   | AFG         | Qandahar   |{"Population": 237500}   |
|    3 | Herat      | AFG         | Herat      |{"Population": 186800}   |
...    ...          ...           ...          ...
| 4079 | Rafah      | PSE         | Rafah      |{"Population": 92020}    |
+------+------- ----+-------------+------------+-------------------------+
4082 rows in set (0.01 sec)

An empty set (no matching records) returns the following information:

Empty set (0.00 sec)

To issue a query that returns a set of table columns, use the select() method and specify the columns to return between square brackets. This query returns the Name and CountryCode columns from the city table.

mysql-py> db.city.select(["Name", "CountryCode"])
+-------------------+-------------+
| Name              | CountryCode |
+-------------------+-------------+
| Kabul             | AFG         |
| Qandahar          | AFG         |
| Herat             | AFG         |
| Mazar-e-Sharif    | AFG         |
| Amsterdam         | NLD         |
...                 ...
| Rafah             | PSE         |
| Olympia           | USA         |
| Little Falls      | USA         |
| Happy Valley      | USA         |
+-------------------+-------------+
4082 rows in set (0.00 sec)

To issue a query that returns rows matching specific search conditions, use the where() method to include those conditions. For example, the following example returns the names and country codes of the cities that start with the letter Z.

mysql-py> db.city.select(["Name", "CountryCode"]).where("Name like 'Z%'")
+-------------------+-------------+
| Name              | CountryCode |
+-------------------+-------------+
| Zaanstad          | NLD         |
| Zoetermeer        | NLD         |
| Zwolle            | NLD         |
| Zenica            | BIH         |
| Zagazig           | EGY         |
| Zaragoza          | ESP         |
| Zamboanga         | PHL         |
| Zahedan           | IRN         |
| Zanjan            | IRN         |
| Zabol             | IRN         |
| Zama              | JPN         |
| Zhezqazghan       | KAZ         |
| Zhengzhou         | CHN         |
...                 ...
| Zeleznogorsk      | RUS         |
+-------------------+-------------+
59 rows in set (0.00 sec)

You can separate a value from the search condition by using the bind() method. For example, instead of using "Name = 'Z%' " as the condition, substitute a named placeholder consisting of a colon followed by a name that begins with a letter, such as name. Then include the placeholder and value in the bind() method as follows:

mysql-py> db.city.select(["Name", "CountryCode"]).where(
"Name like :name").bind("name", "Z%")

To issue a query using the AND operator, add the operator between search conditions in the where() method.

mysql-py> db.city.select(["Name", "CountryCode"]).where(
"Name like 'Z%' and CountryCode = 'CHN'")
+----------------+-------------+
| Name           | CountryCode |
+----------------+-------------+
| Zhengzhou      | CHN         |
| Zibo           | CHN         |
| Zhangjiakou    | CHN         |
| Zhuzhou        | CHN         |
| Zhangjiang     | CHN         |
| Zigong         | CHN         |
| Zaozhuang      | CHN         |
...              ...
| Zhangjiagang   | CHN         |
+----------------+-------------+
22 rows in set (0.01 sec)

To specify multiple conditional operators, you can enclose the search conditions in parenthesis to change the operator precedence. The following example demonstrates the placement of AND and OR operators.

mysql-py> db.city.select(["Name", "CountryCode"]).where(
"Name like 'Z%' and (CountryCode = 'CHN' or CountryCode = 'RUS')")
+-------------------+-------------+
| Name              | CountryCode |
+-------------------+-------------+
| Zhengzhou         | CHN         |
| Zibo              | CHN         |
| Zhangjiakou       | CHN         |
| Zhuzhou           | CHN         |
...                 ...
| Zeleznogorsk      | RUS         |
+-------------------+-------------+
29 rows in set (0.01 sec)

You can apply the limit(), order_by(), and offset() methods to manage the number and order of records returned by the select() method.

To specify the number of records included in a result set, append the limit() method with a value to the select() method. For example, the following query returns the first five records in the country table.

mysql-py> db.country.select(["Code", "Name"]).limit(5)
+------+-------------+
| Code | Name        |
+------+-------------+
| ABW  | Aruba       |
| AFG  | Afghanistan |
| AGO  | Angola      |
| AIA  | Anguilla    |
| ALB  | Albania     |
+------+-------------+
5 rows in set (0.00 sec)

To specify an order for the results, append the order_by() method to the select() method. Pass to the order_by() method a list of one or more columns to sort by and, optionally, the descending (desc) or ascending (asc) attribute as appropriate. Ascending order is the default order type.

For example, the following query sorts all records by the Name column and then returns the first three records in descending order .

mysql-py> db.country.select(["Code", "Name"]).order_by(["Name desc"]).limit(3)
+------+------------+
| Code | Name       |
+------+------------+
| ZWE  | Zimbabwe   |
| ZMB  | Zambia     |
| YUG  | Yugoslavia |
+------+------------+
3 rows in set (0.00 sec)

By default, the limit() method starts from the first record in the table. You can use the offset() method to change the starting record. For example, to ignore the first record and return the next three records matching the condition, pass to the offset() method a value of 1.

mysql-py> db.country.select(["Code", "Name"]).order_by(["Name desc"]).limit(3).offset(1)
+------+------------+
| Code | Name       |
+------+------------+
| ZMB  | Zambia     |
| YUG  | Yugoslavia |
| YEM  | Yemen      |
+------+------------+
3 rows in set (0.00 sec)

The example that follows passes search conditions to the delete() method. All records matching the condition are deleted from the city table. In this example, one record matches the condition.

mysql-py> db.city.delete().where("Name = 'Olympia'")

To delete the first record in the city table, use the limit() method with a value of 1.

mysql-py> db.city.delete().limit(1)

You can delete all records in a table. To do so, use the delete() method without specifying a search condition.

The drop_collection() method is also used in MySQL Shell to drop a relational table from a database. For example, to drop the citytest table from the world_x database, issue:

mysql-py> db.drop_collection("citytest")