-
Type:
Bug
-
Resolution: Won't Fix
-
Priority:
Minor - P4
-
None
-
Affects Version/s: 2.0
-
Component/s: LINQ3
-
None
-
None
-
None
-
None
-
None
-
None
-
None
-
None
When using the $lookup method, the server wraps the field in an array, since it has no way of knowing if more than one document is going to match the query. If you use an _id, there will be only one result, so it is reasonable to try to project it back as a singular, instead of plural field.
In this first example (using LINQPad)
async Task Main()
{
var client = new MongoClient();
var db = client.GetDatabase("test");
var foos = db.GetCollection<Foo>("foo");
var bars = db.GetCollection<Bar>("bar");
var bar = new Bar { Id = ObjectId.GenerateNewId(), Timestamp = DateTime.UtcNow };
var foo = new Foo { Id = ObjectId.GenerateNewId(), Widgets = new[] { new Widget { BarId = bar.Id } } };
await bars.InsertOneAsync(bar);
await foos.InsertOneAsync(foo);
var fooBars = foos.Aggregate()
.Match(Builders<Foo>.Filter.Eq(x => x.Id, foo.Id))
.Unwind<FooAggregateUnwindResult>(new ExpressionFieldDefinition<Foo, Widget[]>(x => x.Widgets))
.Lookup<Bar, FooAggregateLookupResult>("bar",
new ExpressionFieldDefinition<FooAggregateUnwindResult, ObjectId>(x => x.Widgets.BarId),
new ExpressionFieldDefinition<Bar, ObjectId>(x => x.Id),
new ExpressionFieldDefinition<FooAggregateLookupResult, Bar[]>(x => x.Bars))
.Project(Builders<FooAggregateLookupResult>.Projection
.Include(x => x.Id)
.Include(x => x.Bars.ElementAt(0)));
Console.WriteLine(fooBars.ToString());
}
// Define other methods and classes here
public class Foo
{
public ObjectId Id { get; set; }
public Widget[] Widgets { get; set; }
}
public class Widget
{
public ObjectId BarId { get; set; }
}
public class Bar
{
public ObjectId Id { get; set; }
public DateTime Timestamp { get; set; }
}
public class FooAggregateUnwindResult
{
public ObjectId Id { get; set; }
public Widget Widgets { get; set; }
}
public class FooAggregateLookupResult
{
public ObjectId Id { get; set; }
public Widget Widgets { get; set; }
public Bar[] Bars { get; set; }
}
I would expect the pipeline to look something like
[{ "$match" : { "_id" : ObjectId("56c68284f292455188aa2e73") } }, { "$unwind" : "$Widgets" }, { "$lookup" : { "from" : "bar", "localField" : "Widgets.BarId", "foreignField" : "_id", "as" : "Bars" } }, { "$project" : { "_id" : "$_id", "Bar" : { "$arrayElemAt" : ["$Bars", 0] } } }]
This results in the following output on the Mongo Shell
> db.foo.aggregate([{ "$match" : { "_id" : ObjectId("56c68284f292455188aa2e73") } }, { "$unwind" : "$Widgets" }, { "$lookup" : { "from" : "bar", "localField" : "Widgets.BarId", "foreignField" : "_id", "as" : "Bars" } }, { "$project" : { "_id" : "$_id", "Bar" : { "$arrayElemAt" : ["$Bars", 0] } } }])
{ "_id" : ObjectId("56c68284f292455188aa2e73"), "Bar" : { "_id" : ObjectId("56c68284f292455188aa2e72"), "Timestamp" : ISODate("2016-02-19T02:48:36.887Z") } }
The resulting pipeline actually looks like
aggregate([{ "$match" : { "_id" : ObjectId("56c68402f292455188aa2e77") } }, { "$unwind" : "$Widgets" }, { "$lookup" : { "from" : "bar", "localField" : "Widgets.BarId", "foreignField" : "_id", "as" : "Bars" } }, { "$project" : { "_id" : 1, "Bars.0" : 1 } }])
Notice it is using a . operator instead of the $arrayElemAt.
Here is the output as run on the Mongo Shell
> db.foo.aggregate([{ "$match" : { "_id" : ObjectId("56c68402f292455188aa2e77") } }, { "$unwind" : "$Widgets" }, { "$lookup" : { "from" : "bar", "localField" : "Widgets.BarId", "foreignField" : "_id", "as" : "Bars" } }, { "$project" : { "_id" : 1, "Bars.0" : 1 } }])
{ "_id" : ObjectId("56c68402f292455188aa2e77"), "Bars" : [ { } ] }
If I use .First() in the projection like the tests seem to indicate I should be using
var fooBars = foos.Aggregate() .Match(Builders<Foo>.Filter.Eq(x => x.Id, foo.Id)) .Unwind<FooAggregateUnwindResult>(new ExpressionFieldDefinition<Foo, Widget[]>(x => x.Widgets)) .Lookup<Bar, FooAggregateLookupResult>("bar", new ExpressionFieldDefinition<FooAggregateUnwindResult, ObjectId>(x => x.Widgets.BarId), new ExpressionFieldDefinition<Bar, ObjectId>(x => x.Id), new ExpressionFieldDefinition<FooAggregateLookupResult, Bar[]>(x => x.Bars)) .Project(Builders<FooAggregateLookupResult>.Projection .Include(x => x.Id) .Include(x => x.Bars.First())); Console.WriteLine(fooBars.ToString());
I get a serialization exception
System.InvalidOperationException: Unable to determine the serialization information for x => x.Bars.First(). at MongoDB.Driver.ExpressionFieldDefinition`1.Render(IBsonSerializer`1 documentSerializer, IBsonSerializerRegistry serializerRegistry) at MongoDB.Driver.SingleFieldProjectionDefinition`1.Render(IBsonSerializer`1 sourceSerializer, IBsonSerializerRegistry serializerRegistry) at MongoDB.Driver.CombinedProjectionDefinition`1.Render(IBsonSerializer`1 sourceSerializer, IBsonSerializerRegistry serializerRegistry) at MongoDB.Driver.KnownResultTypeProjectionDefinitionAdapter`2.Render(IBsonSerializer`1 sourceSerializer, IBsonSerializerRegistry serializerRegistry) at MongoDB.Driver.AggregateFluent`2.<>c__DisplayClass17_0`1.<Project>b__0(IBsonSerializer`1 s, IBsonSerializerRegistry sr) at MongoDB.Driver.DelegatedPipelineStageDefinition`2.Render(IBsonSerializer`1 inputSerializer, IBsonSerializerRegistry serializerRegistry) at MongoDB.Driver.PipelineStageDefinition`2.MongoDB.Driver.IPipelineStageDefinition.Render(IBsonSerializer inputSerializer, IBsonSerializerRegistry serializerRegistry) at MongoDB.Driver.PipelineStagePipelineDefinition`2.Render(IBsonSerializer`1 inputSerializer, IBsonSerializerRegistry serializerRegistry) at MongoDB.Driver.AggregateFluent`2.ToString() at UserQuery.<Main>d__0.MoveNext() in C:\Users\Peter\AppData\Local\Temp\LINQPad5\_qibkmnwh\query_edxwvy.cs:line 57
Finally, I am able to build the Projection the way I intend using a BsonDocumentProjectionDefinition.
var fooBars = foos.Aggregate() .Match(Builders<Foo>.Filter.Eq(x => x.Id, foo.Id)) .Unwind<FooAggregateUnwindResult>(new ExpressionFieldDefinition<Foo, Widget[]>(x => x.Widgets)) .Lookup<Bar, FooAggregateLookupResult>("bar", new ExpressionFieldDefinition<FooAggregateUnwindResult, ObjectId>(x => x.Widgets.BarId), new ExpressionFieldDefinition<Bar, ObjectId>(x => x.Id), new ExpressionFieldDefinition<FooAggregateLookupResult, Bar[]>(x => x.Bars)) .Project(new BsonDocumentProjectionDefinition<FooAggregateLookupResult, BsonDocument>( new BsonDocument("_id", "$_id") .Add("Bar", new BsonDocument("$arrayElemAt", new BsonArray().Add("$Bars").Add(0))))); Console.WriteLine(fooBars.ToString());
This yields the desired pipeline
aggregate([{ "$match" : { "_id" : ObjectId("56c6852af292455188aa2e7d") } }, { "$unwind" : "$Widgets" }, { "$lookup" : { "from" : "bar", "localField" : "Widgets.BarId", "foreignField" : "_id", "as" : "Bars" } }, { "$project" : { "_id" : "$_id", "Bar" : { "$arrayElemAt" : ["$Bars", 0] } } }])
Here is my server build info to show it is a capable mongod server.
> db.runCommand({buildInfo:1})
{
"version" : "3.2.1",
"gitVersion" : "a14d55980c2cdc565d4704a7e3ad37e4e535c1b2",
"targetMinOS" : "Windows 7/Windows Server 2008 R2",
"modules" : [ ],
"allocator" : "tcmalloc",
"javascriptEngine" : "mozjs",
"sysInfo" : "deprecated",
"versionArray" : [
3,
2,
1,
0
],
"openssl" : {
"running" : "disabled",
"compiled" : "disabled"
},
"buildEnvironment" : {
"distmod" : "2008plus",
"distarch" : "x86_64",
"cc" : "cl: Microsoft (R) C/C++ Optimizing Compiler Version 18.00.31101 for x64",
"ccflags" : "/nologo /EHsc /W3 /wd4355 /wd4800 /wd4267 /wd4244 /wd4290 /wd4068 /wd4351 /we4013 /we4099 /we4930 /Z7 /errorReport:none /MT /O2 /Oy- /Gw /Gy /Zc:inline",
"cxx" : "cl: Microsoft (R) C/C++ Optimizing Compiler Version 18.00.31101 for x64",
"cxxflags" : "/TP",
"linkflags" : "/nologo /DEBUG /INCREMENTAL:NO /LARGEADDRESSAWARE /OPT:REF",
"target_arch" : "x86_64",
"target_os" : "windows"
},
"bits" : 64,
"debug" : false,
"maxBsonObjectSize" : 16777216,
"storageEngines" : [
"devnull",
"ephemeralForTest",
"mmapv1",
"wiredTiger"
],
"ok" : 1
}