patterncsharpMinor
Nesting BsonDocuments in MongoDB queries
Viewed 0 times
bsondocumentsqueriesnestingmongodb
Problem
I have a MongoDB collection setup where each document's JSON looks like this:
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
{
"_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:
Then I converted it to BsonDocuments:
and then ran my aggregate query just like it was previously:
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.