MySQL :: X DevAPI User Guide :: 4.3.2 Collection.find()

X DevAPI User Guide / ... / Collection.find()

4.3.2 Collection.find()

The find(SearchConditionStr) function is for searching documents in a collection, similar to the SELECT statement for an SQL database. It takes a search condition string (SearchConditionStr) as a parameter to specify the documents that should be returned from the database. The execute() function triggers the actual execution of the find() operation.

The SearchConditionStr can be in one of these forms:

If no SearchConditionStr is specified, the find() operation returns all the documents in the collection.

// Get a collection
var myColl = session.getSchema("world_x").getCollection("countryinfo");
// To return all documents in world_x: 
myColl.find().execute();

The most common form for a SearchConditionStr is:

JSON-path [ operator { value | JSON-path} ]

Here are some explanations for the different parts of a SearchConditionStr:

JSON-path: A JSON path identifies an element in a JSON document; see JSON Path Syntax for details . Here is a short summary of the JSON path syntax:
- A JSON path starts with a scope: in MySQL's JSON document implementation, the scope of the path is always the document being operated on, represented as $, which is always implicitly assumed, so it can be skipped in most cases; for example, the path $.geography.Region is equivalent to geography.Region.
  Note
  
  In some cases, $ cannot be omitted; for example:
  When the ** wildcard is used (for example, find("$**.b1"); see the discussion on wildcards below),
  
  When the JSON path only contains a literal string if $ is omitted (for example, find("$.'country_name'") for finding all documents that have a country name field.
- After the scope, a path consists of one or more path legs. A path leg leads from one level of the JSON tree down to the next, and consecutive paths are separated by a period (.). For example: myColl.find("geography.Continent = 'Africa'") finds all documents that have the value Africa for the field Continent under the field geography .
- Elements in arrays are represented by [N], where N is an array index, which has to be a non-negative integer.
```
myColl.add({ name:'John', favorNums: [1, 3, 5, 7, 9] }).execute();
myColl.find("favorNums[0] = 1").execute(); //Returns the document just added
}
```

The wildcard tokens * and ** can be used in JSON paths as follows:

object.* represents the values of all members under the member object. For example, in the countryinfo collection in the sample world_x schema, geography.* represents all members under the object geography, and myColl.find("'Africa' in geography.*") returns all documents that have the value Africa in any of the members under geography.

array[*] represents the values of all elements in an array. For example:

myColl.add({ name:'John', favorNums: [1, 3, 5, 7, 9] }).execute();
myColl.add({ name:'Jane', favorNums: [2, 4, 6, 8, 10] }).execute();
myColl.find("1 in favorNums[*]").execute(); //Returns the first document added above
myColl.find("2 in favorNums[*]").execute(); //Returns the second document added above
}

[prefix]**suffix represents all paths under the document prefix that end with suffix, regardless of the depth of the path. The following examples illustrate how ** can be used to return different results:

mysql-js> myColl.find().execute();
{
    "a": "bar",
    "b": {
        "b1": 6,
        "b2": 7,
        "b3": {
            "b1": 99,
            "b2": 98,
            "b3": {
                "b1": 999,
                "b2": 998
            }
        }
    },
    "_id": "000061313aa10000000000000001"
}
{
    "a": "baz",
    "b": {
        "b1": 1,
        "b2": 7
    },
    "_id": "000061313aa10000000000000002"
}
{
    "a": "bbr",
    "c": 37,
    "_id": "0000613247ed0000000000000001"
}
3 documents in set (0.0007 sec)
mysql-js> myColl.find("$**.b2").execute();
{
    "a": "bar",
    "b": {
        "b1": 6,
        "b2": 7,
        "b3": {
            "b1": 99,
            "b2": 98,
            "b3": {
                "b1": 999,
                "b2": 998
            }
        }
    },
    "_id": "000061313aa10000000000000001"
}
{
    "a": "baz",
    "b": {
        "b1": 1,
        "b2": 7
    },
    "_id": "000061313aa10000000000000002"
}
2 documents in set, 1 warning (0.0008 sec)
...
mysql-js> myColl.find("$**.b3**.b2").execute();
{
    "a": "bar",
    "b": {
        "b1": 6,
        "b2": 7,
        "b3": {
            "b1": 99,
            "b2": 98,
            "b3": {
                "b1": 999,
                "b2": 998
            }
        }
    },
    "_id": "000061313aa10000000000000001"
}
1 document in set, 1 warning (0.0011 sec)
...

The following requirements apply when using the ** wildcard:

prefix should be $ or an element that is a document itself.
suffix should be a path leg and is always required (that is, a path expression may not end in **).
A path expression may not contain the sequence ***.

value is a value to be compared to an element on the JSON-path. The % and _ wildcard characters can be used in value with the LIKE operator, just like in a MySQL WHERE clause. For example:
```
myColl.find("Name LIKE 'Austra%'")
myColl.find("geography.Continent LIKE 'Asi_'")
```

operator: The following operators can be used in a SearchConditionStr: OR (||), AND (&&), XOR, IS, NOT, BETWEEN, IN, LIKE, OVERLAPS, !=, <>, >, >=, <, <=, &, |, <<, >>, +, -, *, /, ~, and %. Here are some examples for using the operators:

myColl.find("Name = 'Australia'")
myColl.find("demographics.Population >= 1000000" )
myColl.find("demographics.LifeExpectancy BETWEEN 50  AND 60")
myColl.find("government.HeadOfState = 'Elizabeth II' AND geography.Region = 'Caribbean'")

If no operator and subsequent JSON path is supplied, find() returns all documents for which the JSON path supplied points to some non-null elements. For example:

myColl.find("demographics.Population" ).execute();

Returns all documents that have a demographics.Population element:

{
    "GNP": 828,
    "_id": "00005de917d80000000000000000",
    "Code": "ABW",
    "Name": "Aruba",
    "IndepYear": null,
    "geography": {
        "Region": "Caribbean",
        "Continent": "North America",
        "SurfaceArea": 193
    },
    "government": {
        "HeadOfState": "Beatrix",
        "GovernmentForm": "Nonmetropolitan Territory of The Netherlands"
    },
    "demographics": {
        "Population": 103000,
        "LifeExpectancy": 78.4000015258789
    }
}
{
    "GNP": 5976,
    "_id": "00005de917d80000000000000001",
...
232 documents in set, 1 warning (0.0013 sec)
Warning (code 3986): Evaluating a JSON value in SQL boolean context does an implicit comparison against JSON integer 0;
if this is not what you want, consider converting JSON to an SQL numeric type with JSON_VALUE RETURNING

Use the IN operator in the SearchConditionStr to check for a value within all the members covered by a wildcard:

mysql-js> myColl.find("$**.b1").execute();
{
    "a": "bar",
    "b": {
        "b1": 6,
        "b2": 7,
        "b3": {
            "b1": 99,
            "b2": 98,
            "b3": {
                "b1": 999,
                "b2": 998
            }
        }
    },
    "_id": "000061313aa10000000000000001"
}
{
    "a": "baz",
    "b": {
        "b1": 1,
        "b2": 7
    },
    "_id": "000061313aa10000000000000002"
}
2 documents in set, 1 warning (0.0012 sec)
...
mysql-js> myColl.find("99 IN $**.b1").execute();
{
    "a": "bar",
    "b": {
        "b1": 6,
        "b2": 7,
        "b3": {
            "b1": 99,
            "b2": 98,
            "b3": {
                "b1": 999,
                "b2": 998
            }
        }
    },
    "_id": "000061313aa10000000000000001"
}
1 document in set (0.0016 sec)
...

The OVERLAPS operator compares two JSON fragments and returns true (1) if the two fragments have any values in any key-value pair or array element in common. For example:

mysql-js> myColl.find("list").execute();
{
    "_id": "1",
    "list": [
        1,
        4
    ]
}
{
    "_id": "2",
    "list": [
        4,
        7
    ]
}
2 documents in set, 1 warning (0.0010 sec)
mysql-js> myColl.find("[1,2,3] OVERLAPS $.list")
{
    "_id": "1",
    "list": [
        1,
        4
    ]
}
1 document in set (0.0006 sec)

Several methods such as fields(), sort() , and limit() can be chained to the find() function to further refine the result. For example:

myColl.find("Name LIKE 'Austra%'").fields("Code")
myColl.find("geography.Continent LIKE 'A%'").limit(10)

Parameter binding using bind() is also supported. The following example illustrates the use of bind() with find():

MySQL Shell JavaScript Code

// Use the collection 'my_collection'
var myColl = db.getCollection('my_collection');

// Find a single document that has a field 'name' that starts with 'L'
var docs = myColl.find('name like :param').
            limit(1).bind('param', 'L%').execute();

print(docs.fetchOne());

// Get all documents with a field 'name' that starts with 'L'
docs = myColl.find('name like :param').
        bind('param','L%').execute();

var myDoc;
while (myDoc = docs.fetchOne()) {
  print(myDoc);
}

MySQL Shell Python Code

# Use the collection 'my_collection'
myColl = db.get_collection('my_collection')

# Find a single document that has a field 'name' that starts with 'L'
docs = myColl.find('name like :param').limit(1).bind('param', 'L%').execute()

print(docs.fetch_one())

# Get all documents with a field 'name' that starts with 'L'
docs = myColl.find('name like :param').bind('param','L%').execute()

myDoc = docs.fetch_one()
while myDoc:
  print(myDoc)
  myDoc = docs.fetch_one()

Node.js JavaScript Code

// Use the collection 'my_collection'
var myColl = db.getCollection('my_collection');

// Find a single document that has a field 'name' that starts with 'L'
myColl
  .find('name like :name')
  .bind('name', 'L%')
  .limit(1)
  .execute(function (doc) {
    console.log(doc);
  })
  .then(function () {
    // handle details
  });

// Get all documents with a field 'name' that starts with 'L'
myColl
  .find('name like :name')
  .bind('name', 'L%')
  .execute(function (doc) {
    console.log(doc);
  })
  .then(function () {
    // handle details
  });

C# Code

// Use the collection "my_collection"
var myColl = db.GetCollection("my_collection");

// Find a single document that has a field "name" that starts with "L"
var docs = myColl.Find("name like :param")
.Limit(1).Bind("param", "L%").Execute();

Console.WriteLine(docs.FetchOne());

// Get all documents with a field "name" that starts with "L"
docs = myColl.Find("name like :param")
.Bind("param", "L%").Execute();

while (docs.Next())
{
    Console.WriteLine(docs.Current);
}

Python Code

# Use the collection 'my_collection'
my_coll = my_schema.get_collection('my_collection')

# Find a single document that has a field 'name' that starts with 'L'
docs = my_coll.find('name like :param').limit(1).bind('param', 'L%').execute()

print(docs.fetch_one())

# Get all documents with a field 'name' that starts with 'L'
docs = my_coll.find('name like :param').bind('param', 'L%').execute()

doc = docs.fetch_one()
print(doc)

Java Code

// Use the collection 'my_collection'
Collection myColl = db.getCollection("my_collection");

// Find a single document that has a field 'name' that starts with 'L'
DocResult docs = myColl.find("name like :name").bind("name", "L%").execute();

System.out.println(docs.fetchOne());

// Get all documents with a field 'name' that starts with 'L'
docs = myColl.find("name like :name").bind("name", "L%").execute();

while (docs.hasNext()) {
    DbDoc myDoc = docs.next();
    System.out.println(myDoc);
}

C++ Code

// Use the collection 'my_collection'
Collection myColl = db.getCollection("my_collection");

// Find a single document that has a field 'name' that starts with 'L'
DocResult docs = myColl.find("name like :param")
                       .limit(1).bind("param", "L%").execute();

cout << docs.fetchOne() << endl;

// Get all documents with a field 'name' that starts with 'L'
docs = myColl.find("name like :param")
             .bind("param","L%").execute();

DbDoc myDoc;
while ((myDoc = docs.fetchOne()))
{
  cout << myDoc << endl;
}

See also CollectionFindFunction for the syntax of find() in EBNF.

PREV HOME UP NEXT