Unindo coleções no MongoDB com .NET Core e um pipeline de agregação

1
{
2
    "_id": ObjectId("573a1390f29313caabcd4135"),
3
    "title": "Blacksmith Scene",
4
    "plot": "Three men hammer on an anvil and pass a bottle of beer around.",
5
    "year": 1893,
6
    // ...
7
}

1
{
2
    "_id": ObjectId("61d8bb5e2d5fe0c2b8a1007d"),
3
    "username": "nraboy",
4
    "items": [
5
        "573a1390f29313caabcd42e8",
6
        "573a1391f29313caabcd8a82"
7
    ]
8
}

1
{
2
    "_id": ObjectId("61d8bb5e2d5fe0c2b8a1007d"),
3
    "username": "nraboy",
4
    "items": [
5
        {
6
            "_id": ObjectId("573a1390f29313caabcd4135"),
7
            "title": "Blacksmith Scene",
8
            "plot": "Three men hammer on an anvil and pass a bottle of beer around.",
9
            "year": 1893,
10
            // ...
11
        },
12
        {
13
            "_id": ObjectId("573a1391f29313caabcd8a82"),
14
            "title": "The Terminator",
15
            "plot": "A movie about some killer robots.",
16
            "year": 1984,
17
            // ...
18
        }
19
    ]
20
}

1
dotnet new console -o MongoExample
2
cd MongoExample
3
dotnet add package MongoDB.Driver

1
using MongoDB.Driver;
2
using MongoDB.Bson;
3
4
MongoClient client = new MongoClient("ATLAS_URI_HERE");
5
6
IMongoCollection<BsonDocument> playlistCollection  = client.GetDatabase("sample_mflix").GetCollection<BsonDocument>("playlist");
7
8
List<BsonDocument> results = playlistCollection.Find(new BsonDocument()).ToList();
9
10
foreach(BsonDocument result in results) {
11
    Console.WriteLine(result["username"] + ": " + string.Join(", ", result["items"]));
12
}

1
BsonDocument pipelineStage1 = new BsonDocument{
2
    {
3
        "$match", new BsonDocument{
4
            { "username", "nraboy" }
5
        }
6
    }
7
};

1
BsonDocument pipelineStage2 = new BsonDocument{
2
    { 
3
        "$project", new BsonDocument{
4
            { "_id", 1 },
5
            { "username", 1 },
6
            { 
7
                "items", new BsonDocument{
8
                    {
9
                        "$map", new BsonDocument{
10
                            { "input", "$items" },
11
                            { "as", "item" },
12
                            {
13
                                "in", new BsonDocument{
14
                                    {
15
                                        "$convert", new BsonDocument{
16
                                            { "input", "$$item" },
17
                                            { "to", "objectId" }
18
                                        }
19
                                    }
20
                                }
21
                            }
22
                        }
23
                    }
24
                }
25
            }
26
        }
27
    }
28
};

1
BsonDocument pipelineStage3 = new BsonDocument{
2
    {
3
        "$lookup", new BsonDocument{
4
            { "from", "movies" },
5
            { "localField", "items" },
6
            { "foreignField", "_id" },
7
            { "as", "movies" }
8
        }
9
    }
10
};

1
SELECT movies
2
FROM playlist
3
JOIN movies ON playlist.items = movies._id

1
BsonDocument pipelineStage4 = new BsonDocument{
2
    { "$unwind", "$movies" }
3
};

1
BsonDocument pipelineStage5 = new BsonDocument{
2
    {
3
        "$group", new BsonDocument{
4
            { "_id", "$_id" },
5
            { 
6
                "username", new BsonDocument{
7
                    { "$first", "$username" }
8
                } 
9
            },
10
            { 
11
                "movies", new BsonDocument{
12
                    { "$addToSet", "$movies" }
13
                }
14
            }
15
        }
16
    }
17
};

1
BsonDocument[] pipeline = new BsonDocument[] { 
2
    pipelineStage1, 
3
    pipelineStage2, 
4
    pipelineStage3, 
5
    pipelineStage4, 
6
    pipelineStage5 
7
};
8
9
List<BsonDocument> pResults = playlistCollection.Aggregate<BsonDocument>(pipeline).ToList();
10
11
foreach(BsonDocument pResult in pResults) {
12
    Console.WriteLine(pResult);
13
}

1
var pResults = playlistCollection.Aggregate()
2
    .Match(new BsonDocument{{ "username", "nraboy" }})
3
    .Project(new BsonDocument{
4
            { "_id", 1 },
5
            { "username", 1 },
6
            {
7
                "items", new BsonDocument{
8
                    {
9
                        "$map", new BsonDocument{
10
                            { "input", "$items" },
11
                            { "as", "item" },
12
                            {
13
                                "in", new BsonDocument{
14
                                    {
15
                                        "$convert", new BsonDocument{
16
                                            { "input", "$$item" },
17
                                            { "to", "objectId" }
18
                                        }
19
                                    }
20
                                }
21
                            }
22
                        }
23
                    }
24
                }
25
            }
26
        })
27
    .Lookup("movies", "items", "_id", "movies")
28
    .Unwind("movies")
29
    .Group(new BsonDocument{
30
            { "_id", "$_id" },
31
            {
32
                "username", new BsonDocument{
33
                    { "$first", "$username" }
34
                }
35
            },
36
            {
37
                "movies", new BsonDocument{
38
                    { "$addToSet", "$movies" }
39
                }
40
            }
41
        })
42
    .ToList();
43
44
foreach(var pResult in pResults) {
45
    Console.WriteLine(pResult);
46
}