无需解析 JSON。这里的一切实际上都可以直接使用 LINQ 或 Aggregate Fluent 接口来完成。
只是使用一些演示课程,因为这个问题并没有真正提供太多内容。
Setup
基本上我们这里有两个集合,分别是
entities
{ "_id" : ObjectId("5b08ceb40a8a7614c70a5710"), "name" : "A" }
{ "_id" : ObjectId("5b08ceb40a8a7614c70a5711"), "name" : "B" }
and others
{
"_id" : ObjectId("5b08cef10a8a7614c70a5712"),
"entity" : ObjectId("5b08ceb40a8a7614c70a5710"),
"name" : "Sub-A"
}
{
"_id" : ObjectId("5b08cefd0a8a7614c70a5713"),
"entity" : ObjectId("5b08ceb40a8a7614c70a5711"),
"name" : "Sub-B"
}
还有几个将它们绑定到的类,就像非常基本的示例一样:
public class Entity
{
public ObjectId id;
public string name { get; set; }
}
public class Other
{
public ObjectId id;
public ObjectId entity { get; set; }
public string name { get; set; }
}
public class EntityWithOthers
{
public ObjectId id;
public string name { get; set; }
public IEnumerable<Other> others;
}
public class EntityWithOther
{
public ObjectId id;
public string name { get; set; }
public Other others;
}
Queries
流畅的界面
var listNames = new[] { "A", "B" };
var query = entities.Aggregate()
.Match(p => listNames.Contains(p.name))
.Lookup(
foreignCollection: others,
localField: e => e.id,
foreignField: f => f.entity,
@as: (EntityWithOthers eo) => eo.others
)
.Project(p => new { p.id, p.name, other = p.others.First() } )
.Sort(new BsonDocument("other.name",-1))
.ToList();
请求发送到服务器:
[
{ "$match" : { "name" : { "$in" : [ "A", "B" ] } } },
{ "$lookup" : {
"from" : "others",
"localField" : "_id",
"foreignField" : "entity",
"as" : "others"
} },
{ "$project" : {
"id" : "$_id",
"name" : "$name",
"other" : { "$arrayElemAt" : [ "$others", 0 ] },
"_id" : 0
} },
{ "$sort" : { "other.name" : -1 } }
]
可能是最容易理解的,因为 Fluent 接口与通用 BSON 结构基本相同。这$lookup https://docs.mongodb.com/manual/reference/operator/aggregation/lookup/阶段有所有相同的论点,并且$arrayElemAt https://docs.mongodb.com/manual/reference/operator/aggregation/arrayElemAt/表示为First()
。为了$sort https://docs.mongodb.com/manual/reference/operator/aggregation/sort/您只需提供 BSON 文档或其他有效表达式即可。
另一种是更新的表达形式$lookup https://docs.mongodb.com/manual/reference/operator/aggregation/lookup/带有 MongoDB 3.6 及更高版本的子管道语句。
BsonArray subpipeline = new BsonArray();
subpipeline.Add(
new BsonDocument("$match",new BsonDocument(
"$expr", new BsonDocument(
"$eq", new BsonArray { "$$entity", "$entity" }
)
))
);
var lookup = new BsonDocument("$lookup",
new BsonDocument("from", "others")
.Add("let", new BsonDocument("entity", "$_id"))
.Add("pipeline", subpipeline)
.Add("as","others")
);
var query = entities.Aggregate()
.Match(p => listNames.Contains(p.name))
.AppendStage<EntityWithOthers>(lookup)
.Unwind<EntityWithOthers, EntityWithOther>(p => p.others)
.SortByDescending(p => p.others.name)
.ToList();
请求发送到服务器:
[
{ "$match" : { "name" : { "$in" : [ "A", "B" ] } } },
{ "$lookup" : {
"from" : "others",
"let" : { "entity" : "$_id" },
"pipeline" : [
{ "$match" : { "$expr" : { "$eq" : [ "$$entity", "$entity" ] } } }
],
"as" : "others"
} },
{ "$unwind" : "$others" },
{ "$sort" : { "others.name" : -1 } }
]
Fluent“Builder”尚不直接支持语法,LINQ 表达式也不支持$expr https://docs.mongodb.com/manual/reference/operator/query/expr/运算符,但是您仍然可以使用BsonDocument
and BsonArray
或其他有效的表达方式。在这里我们还“输入”$unwind https://docs.mongodb.com/manual/reference/operator/aggregation/unwind/结果以便应用$sort https://docs.mongodb.com/manual/reference/operator/aggregation/sort/使用表达式而不是BsonDocument
如前所示。
除了其他用途之外,“子管道”的主要任务是减少目标数组中返回的文档$lookup https://docs.mongodb.com/manual/reference/operator/aggregation/lookup/。还有$unwind https://docs.mongodb.com/manual/reference/operator/aggregation/unwind/这里的目的实际上是被“合并” https://docs.mongodb.com/manual/core/aggregation-pipeline-optimization/#lookup-unwind-coalescence进入$lookup https://docs.mongodb.com/manual/reference/operator/aggregation/lookup/服务器执行上的语句,因此这通常比仅获取结果数组的第一个元素更有效。
可查询的群组加入
var query = entities.AsQueryable()
.Where(p => listNames.Contains(p.name))
.GroupJoin(
others.AsQueryable(),
p => p.id,
o => o.entity,
(p, o) => new { p.id, p.name, other = o.First() }
)
.OrderByDescending(p => p.other.name);
请求发送到服务器:
[
{ "$match" : { "name" : { "$in" : [ "A", "B" ] } } },
{ "$lookup" : {
"from" : "others",
"localField" : "_id",
"foreignField" : "entity",
"as" : "o"
} },
{ "$project" : {
"id" : "$_id",
"name" : "$name",
"other" : { "$arrayElemAt" : [ "$o", 0 ] },
"_id" : 0
} },
{ "$sort" : { "other.name" : -1 } }
]
这几乎是相同的,但只是使用不同的接口并产生略有不同的 BSON 语句,实际上只是因为功能语句中的简化命名。这确实带来了另一种可能性,即简单地使用$unwind https://docs.mongodb.com/manual/reference/operator/aggregation/unwind/生产自SelectMany()
:
var query = entities.AsQueryable()
.Where(p => listNames.Contains(p.name))
.GroupJoin(
others.AsQueryable(),
p => p.id,
o => o.entity,
(p, o) => new { p.id, p.name, other = o }
)
.SelectMany(p => p.other, (p, other) => new { p.id, p.name, other })
.OrderByDescending(p => p.other.name);
请求发送到服务器:
[
{ "$match" : { "name" : { "$in" : [ "A", "B" ] } } },
{ "$lookup" : {
"from" : "others",
"localField" : "_id",
"foreignField" : "entity",
"as" : "o"
}},
{ "$project" : {
"id" : "$_id",
"name" : "$name",
"other" : "$o",
"_id" : 0
} },
{ "$unwind" : "$other" },
{ "$project" : {
"id" : "$id",
"name" : "$name",
"other" : "$other",
"_id" : 0
}},
{ "$sort" : { "other.name" : -1 } }
]
通常放置一个$unwind https://docs.mongodb.com/manual/reference/operator/aggregation/unwind/直接跟随$lookup https://docs.mongodb.com/manual/reference/operator/aggregation/lookup/实际上是一个“优化模式” https://docs.mongodb.com/manual/core/aggregation-pipeline-optimization/#lookup-unwind-coalescence为聚合框架。然而,.NET 驱动程序确实通过强制$project
在两者之间而不是使用隐含的命名"as"
。如果不是因为这个,这实际上比$arrayElemAt https://docs.mongodb.com/manual/reference/operator/aggregation/arrayElemAt/当您知道您有“一个”相关结果时。如果你想要$unwind https://docs.mongodb.com/manual/reference/operator/aggregation/unwind/“合并”,那么您最好使用流畅的界面,或稍后演示的不同形式。
可质疑的自然
var query = from p in entities.AsQueryable()
where listNames.Contains(p.name)
join o in others.AsQueryable() on p.id equals o.entity into joined
select new { p.id, p.name, other = joined.First() }
into p
orderby p.other.name descending
select p;
请求发送到服务器:
[
{ "$match" : { "name" : { "$in" : [ "A", "B" ] } } },
{ "$lookup" : {
"from" : "others",
"localField" : "_id",
"foreignField" : "entity",
"as" : "joined"
} },
{ "$project" : {
"id" : "$_id",
"name" : "$name",
"other" : { "$arrayElemAt" : [ "$joined", 0 ] },
"_id" : 0
} },
{ "$sort" : { "other.name" : -1 } }
]
一切都非常熟悉,而且实际上只是功能性命名。就像使用$unwind https://docs.mongodb.com/manual/reference/operator/aggregation/unwind/ option:
var query = from p in entities.AsQueryable()
where listNames.Contains(p.name)
join o in others.AsQueryable() on p.id equals o.entity into joined
from sub_o in joined.DefaultIfEmpty()
select new { p.id, p.name, other = sub_o }
into p
orderby p.other.name descending
select p;
请求发送到服务器:
[
{ "$match" : { "name" : { "$in" : [ "A", "B" ] } } },
{ "$lookup" : {
"from" : "others",
"localField" : "_id",
"foreignField" : "entity",
"as" : "joined"
} },
{ "$unwind" : {
"path" : "$joined", "preserveNullAndEmptyArrays" : true
} },
{ "$project" : {
"id" : "$_id",
"name" : "$name",
"other" : "$joined",
"_id" : 0
} },
{ "$sort" : { "other.name" : -1 } }
]
实际上正在使用“优化合并” https://docs.mongodb.com/manual/core/aggregation-pipeline-optimization/#lookup-unwind-coalescence形式。译者仍然坚持添加一个$project https://docs.mongodb.com/manual/reference/operator/aggregation/project/因为我们需要中间select
以使该声明有效。
Summary
因此,有很多方法可以本质上得到具有完全相同结果的基本相同的查询语句。虽然您“可以”将 JSON 解析为BsonDocument
形成并将其提供给流利的Aggregate()
命令,通常最好使用自然构建器或 LINQ 接口,因为它们可以轻松映射到同一语句。
选项与$unwind https://docs.mongodb.com/manual/reference/operator/aggregation/unwind/很大程度上显示,因为即使使用“单一”匹配,“合并”形式实际上也比使用更优化$arrayElemAt https://docs.mongodb.com/manual/reference/operator/aggregation/arrayElemAt/获取“第一个”数组元素。考虑到诸如 BSON 限制之类的事情,这一点甚至变得更加重要,其中$lookup https://docs.mongodb.com/manual/reference/operator/aggregation/lookup/目标数组可能会导致父文档超过 16MB,而无需进一步过滤。这里还有另一篇文章聚合 $lookup 匹配管道中文档的总大小超过最大文档大小 https://stackoverflow.com/a/45726501/2313887我实际上讨论了如何通过使用此类选项或其他选项来避免达到该限制Lookup()
目前仅适用于流畅界面的语法。