3
\$\begingroup\$

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 BsonDocument
                                {
                                    {
                                        "$eq", new BsonArray
                                        {
                                            "$VehicleEntry.VehicleStatus",
                                            "FAILED"
                                        }
                                    }
                                },
                                1,
                                0
                            }
                        }
                    }
                }
            }
        }
    });

While it works as expected, this seems very difficult to maintain and has even become unreadable. How can I improve the way this is written to be more maintainable?

If it's relevant to a better solution, I already have the Newtonsoft library in my project as I use it to convert Json to Bson.

EDIT I have been told I could serialize Bson to do this, but I am not familiar with the concept. What are some best practices for that and how can I convert this query to use serialized bson?

\$\endgroup\$
1
\$\begingroup\$

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<BsonDocument>(matchJSON);
var groupBson = MongoDB.Bson.Serialization.BsonSerializer.Deserialize<BsonDocument>(groupJSON);

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

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.