HiveBrain v1.2.0
Get Started
← Back to all entries
patterncsharpMinor

Nesting BsonDocuments in MongoDB queries

Submitted by: @import:stackexchange-codereview··
0
Viewed 0 times
bsondocumentsqueriesnestingmongodb

Problem

I have a MongoDB collection setup where each document's JSON looks like this:

{
    "_id" : "Stuff",
    "project_id" : "1234",
    "VehicleEntry" : [
        {
            // Irrelevant fields removed.
            "VehicleStatus" : "PASSED"
        },
        {
            // Irrelevant fields removed.
            "VehicleStatus" : "FAILED"
        }
    ]
}


I want to write a query that gives me the number of vehicles passed and failed for each project identifier. I have done so in this very long aggregation query:

```
var aggregate = collection.Aggregate()
.Match(new BsonDocument
{
{
"project_id", "1234"
}
})
.Unwind(i => i["VehicleEntry"])
.Group(new BsonDocument
{
{
"_id", "$project_id"
},
{
"passed", new BsonDocument
{
{
"$sum", new BsonDocument
{
{
"$cond", new BsonArray
{
new BsonDocument
{
{
"$eq", new BsonArray
{
"$VehicleEntry.VehicleStatus",
"PASSED"
}
}
},
1,
0
}
}
}
}
}
},
{
"failed", new BsonDocument
{
{
"$sum", new BsonDocument
{
{
"$cond", new BsonArray
{
new B

Solution

I found one way to simplify this which helps break up the number of rows required.

Following this StackOverflow question about converting a JSON string to Bson, I wrote part of my query as it would be in a console. I could only figure out how to do this for the match and group part:

var matchJSON = "{ project_id : '1234' }";
var groupJSON = "{ " + 
    "_id : { project_id : '$project_id', date : '$VehicleEntry.@Date' }, " + 
    "passed : { $sum : { $cond : [ { $eq : [ '$VehicleEntry.VehicleStatus', 'PASSED' ] }, 1, 0 ] } }, " + 
    "failed : { $sum : { $cond : [ { $eq : [ '$VehicleEntry.VehicleStatus', 'FAILED' ] }, 1, 0 ] } } }";


Then I converted it to BsonDocuments:

var matchBson = MongoDB.Bson.Serialization.BsonSerializer.Deserialize(matchJSON);
var groupBson = MongoDB.Bson.Serialization.BsonSerializer.Deserialize(groupJSON);


and then ran my aggregate query just like it was previously:

var aggregate = collection.Aggregate()
    .Match(matchBson)
    .Unwind(i => i["VehicleEntry"])
    .Group(groupBson);

Code Snippets

var matchJSON = "{ project_id : '1234' }";
var groupJSON = "{ " + 
    "_id : { project_id : '$project_id', date : '$VehicleEntry.@Date' }, " + 
    "passed : { $sum : { $cond : [ { $eq : [ '$VehicleEntry.VehicleStatus', 'PASSED' ] }, 1, 0 ] } }, " + 
    "failed : { $sum : { $cond : [ { $eq : [ '$VehicleEntry.VehicleStatus', 'FAILED' ] }, 1, 0 ] } } }";
var matchBson = MongoDB.Bson.Serialization.BsonSerializer.Deserialize<BsonDocument>(matchJSON);
var groupBson = MongoDB.Bson.Serialization.BsonSerializer.Deserialize<BsonDocument>(groupJSON);
var aggregate = collection.Aggregate()
    .Match(matchBson)
    .Unwind(i => i["VehicleEntry"])
    .Group(groupBson);

Context

StackExchange Code Review Q#115353, answer score: 2

Revisions (0)

No revisions yet.