As per my last question (Query document count by multiple ranges returning range start/end with matching element count), I built a query to check for count of documents in multiple, potentially overlapping date ranges.

The query works on MongoDB 4.4 but I need to run it on 4.2 as well. On MongoDB 4.2, I get the following error:

Mongo Server error (MongoCommandException): Command failed with error 168 (InvalidPipelineOperator): 'Unrecognized expression '$first'' on server localhost:27017. 

The full response is:
{ 
    "ok" : 0.0, 
    "errmsg" : "Unrecognized expression '$first'", 
    "code" : 168.0, 
    "codeName" : "InvalidPipelineOperator"
}

How would you write the aggregation projection to achieve the same result structure. Here is the complete code with data setup

db.createCollection("object_location_tracking");
db.getCollection("object_location_tracking").insertMany([
    {
        _id: "1",
        locationId: "locationA",
        objectId: "objectA",
        timestamp: ISODate("2020-01-01T00:00:00Z")
    },
    {
        _id: "2",
        locationId: "locationB",
        objectId: "objectA",
        timestamp: ISODate("2020-01-01T00:00:00Z")
    },
    {
        _id: "3",
        locationId: "locationA",
        objectId: "objectB",
        timestamp: ISODate("2019-01-01T00:00:00Z")
    },
    {
        _id: "4",
        locationId: "locationB",
        objectId: "objectB",
        timestamp: ISODate("2020-01-01T00:00:00Z")
    }
]);



db.getCollection("object_location_tracking").aggregate([
    {$facet: {
        "first_bucket_id": [
            {$match: {"objectId":"objectA",
                      "locationId":"locationA",
                      "timestamp": {$gte: new ISODate('2020-01-01'),
                                    $lt: new ISODate('2020-12-31')}
                     }},
            {$count: "N"}
        ],

        "second_bucket_id": [
            {$match: {"objectId":"objectA",
                      "locationId":"locationA",
                      "timestamp": {$gte: new ISODate('2020-01-01'),
                                    $lt: new ISODate('2022-12-31')}
                     }},
            {$count: "N"}
        ],

        "third_bucket_id": [
            {$match: {"objectId":"objectA",
                      "locationId":"locationB",
                      "timestamp": {$gte: new ISODate('2022-01-01'),
                                    $lt: new ISODate('2022-12-31')}
                     }},
            {$count: "N"}
        ]
    }},
    {
        $set: {
            first_bucket_id: { $first: "$first_bucket_id.N"},
            second_bucket_id: { $first: "$second_bucket_id.N"},
            third_bucket_id: { $first: "$third_bucket_id.N"}
        }
    }
    , {
        $project: {
            first_bucket_id: 1,
            second_bucket_id: 1,
            third_bucket_id: 1
            
        }
    }
]);

CodePudding user response：

You are using the first array element, which as you can tell is a new operator added for version 4.4

Luckily for you this is quite easy to overcome, by just using $arrayElemAt, like so:

{
    $set: {
        first_bucket_id: {$arrayElemAt: ["$first_bucket_id.N", 0]},
        second_bucket_id: {$arrayElemAt: ["$second_bucket_id.N", 0]},
        third_bucket_id: {$arrayElemAt: ["$third_bucket_id.N", 0]}
    }
}