该
功能用于在集合中搜索文档,类似于 SQL 数据库的SELECT语句。它采用搜索条件字符串 ( SearchConditionStr ) 作为参数来指定应从数据库返回的文档。该函数触发操作的实际执行。
find(
SearchConditionStr
)execute()
find()
SearchConditionStr可以采用以下形式之一 :
-
如果未指定SearchConditionStr,则
find()
操作返回集合中的所有文档。// Get a collection var myColl = session.getSchema("world_x").getCollection("countryinfo"); // To return all documents in world_x: myColl.find().execute();
-
SearchConditionStr 最常见的形式 是:
JSON-path [ operator { value | JSON-path} ]
以下是对SearchConditionStr 的不同部分的一些解释 :
-
JSON-path
:JSON 路径标识 JSON 文档中的元素;有关详细信息,请参阅 JSON 路径语法。以下是 JSON 路径语法的简短摘要:-
JSON路径以作用域开始:在MySQL的JSON文档实现中,路径的作用域始终是被操作的文档,表示为
$
,它总是被隐式假定,因此大多数情况下可以跳过;例如,路径$.geography.Region
等同于geography.Region
.笔记在某些情况下,
$
不能省略;例如:当使用
**
通配符时(例如,find("$**.b1")
请参阅下面关于通配符的讨论),当 JSON 路径仅包含一个文字字符串 if
$
被省略时(例如,find("$.'country_name'")
用于查找具有country name
字段的所有文档。
在范围之后,路径由一个或多个路径分支组成。一条路径从 JSON 树的一层向下延伸到下一层,连续的路径由句点 (
.
) 分隔。例如:myColl.find("geography.Continent = 'Africa'")
查找Africa
fieldContinent
下具有该字段 值的所有文档geography
。-
数组中的元素用 表示 ,其中是数组索引,必须是非负整数。
[
N
]N
myColl.add({ name:'John', favorNums: [1, 3, 5, 7, 9] }).execute(); myColl.find("favorNums[0] = 1").execute(); //Returns the document just added }
-
-
通配符可以在 JSON 路径中使用,如下所示
*
:**
代表 member 下所有成员的值object
.*object
。例如,在示例 架构中的countryinfo
集合中,表示对象 下的所有成员 ,并 返回在 下的任何成员中具有值的所有文档。world_x
geography.*
geography
myColl.find("'Africa' in geography.*")
Africa
geography
-
表示数组中所有元素的值。例如:array
[*]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
prefix
结尾suffix
的路径,与路径的深度无关。以下示例说明了如何使用 ** 返回不同的结果: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) ...
**
使用通配符 时适用以下要求 :prefix
应该是$
或者是一个文档本身的元素。suffix
应该是路径段并且始终是必需的(也就是说,路径表达式不能以 结尾**
)。路径表达式可能不包含序列
***
。
-
value
是要与 上的元素进行比较的值JSON-path
。和 通配符可以与运算符一起使用%
,就像 在 MySQL子句中一样。例如:_
value
LIKE
WHERE
myColl.find("Name LIKE 'Austra%'") myColl.find("geography.Continent LIKE 'Asi_'")
-
operator
: 可以在 SearchConditionStr中使用以下运算符:OR (||)
,AND (&&)
,XOR
,IS
,NOT
,BETWEEN
,IN
,LIKE
,OVERLAPS
,!=
,<>
,>
,>=
,<
,<=
,&
,|
,<<
,>>
,+
,-
,*
,/
和~
。%
以下是使用运算符的一些示例: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'")
如果未提供运算符和后续 JSON 路径,则
find()
返回提供的 JSON 路径指向某些非空元素的所有文档。例如:myColl.find("demographics.Population" ).execute();
返回具有
demographics.Population
元素的所有文档:{ "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
使用SearchConditionStr
IN
中的运算符 检查通配符涵盖的所有成员中的值: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) ...
该
OVERLAPS
运算符比较两个 JSON 片段,如果这两个片段在任何键值对或数组元素中有任何共同的值,则返回真 (1)。例如: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)
-
fields()
、
sort()
、skip()
和
等几种方法limit()
可以链接到find()
函数以进一步优化结果。例如:
myColl.find("Name LIKE 'Austra%'").fields("Code")
myColl.find("geography.Continent LIKE 'A%'").limit(10)
bind()
还支持
使用参数绑定。以下示例说明了
bind()
with的用法find()
:
MySQL 外壳 JavaScript 代码
// 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 外壳 Python 代码
# 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 代码
// 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#代码
// 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代码
# 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代码
// 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++代码
// 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;
}
另请参阅CollectionFindFunction了解 EBNF 中的语法find()
。