I have a document with a nested array which looks like this:

[
  {
    "id": 1,
    data: [
      [
        ISODate("2000-01-01T00:00:00Z"),
        2,
        3
      ],
      [
        ISODate("2000-01-03T00:00:00Z"),
        2,
        3
      ],
      [
        ISODate("2000-01-05T00:00:00Z"),
        2,
        3
      ]
    ]
  },
  {
    "id": 2,
    data: []
  }
]

As you can see, we have an array of arrays. For each element in the data array, the first element is a date.

I wanted to create an aggregation pipeline which filters only the elements of data where the date is larger than a given date.

db.collection.aggregate([
  {
    "$match": {
      "id": 1
    }
  },
  {
    "$project": {
      "data": {
        "$filter": {
          "input": "$data",
          "as": "entry",
          "cond": {
            "$gt": [
              "$$entry.0",
              ISODate("2000-01-04T00:00:00Z")
            ]
          }
        }
      }
    }
  }
])

The problem is that with $gt, this just returns an empty array for data. With $lt this returns all elements. So the filtering clearly does not work.

Expected result:

[
  {
    "id": 1,
    "data": [
      [
        ISODate("2000-01-05T00:00:00Z"),
        2,
        3
      ]
    ]
  }
]

Any ideas?

Playground

CodePudding user response：

I believe the issue is that when you write $$entry.0, MongoDB is trying to evaluate entry.0 as a variable name, when in reality the variable is named entry. You could make use of the $first array operator in order to get the first element like so:

db.collection.aggregate([
  {
    "$match": {
      "id": 1
    }
  },
  {
    "$project": {
      "data": {
        "$filter": {
          "input": "$data",
          "as": "entry",
          "cond": {
            "$gt": [
              {
                $first: "$$entry"
              },
              ISODate("2000-01-04T00:00:00Z")
            ]
          }
        }
      }
    }
  }
])

Mongo playground example

CodePudding user response：

Don't think $$entry.0 work to get the first element of the array. Instead, use $arrayElemAt operator.

db.collection.aggregate([
  {
    "$match": {
      "id": 1
    }
  },
  {
    "$project": {
      "data": {
        "$filter": {
          "input": "$data",
          "as": "entry",
          "cond": {
            "$gt": [
              {
                "$arrayElemAt": [
                  "$$entry",
                  0
                ]
              },
              ISODate("2000-01-04T00:00:00Z")
            ]
          }
        }
      }
    }
  }
])

Sample Mongo Playground

CodePudding user response：

to specify which element in the array you are comparing it is better to use $arrayElemAt instead of $$ARRAY.0. you must pass 2 parameters while using $arrayElemAt, the first one is the array which in your case is $$entry, and the second one is the index which in your case is 0

this is the solution I came up with:

db.collection.aggregate([
  {
    "$match": {
      "id": 1
    }
  },
  {
    "$project": {
      "data": {
        "$filter": {
          "input": "$data",
          "as": "entry",
          "cond": {
            "$gt": [
              {
                "$arrayElemAt": [
                  "$$entry",
                  0
                ]
              },
              ISODate("2000-01-04T00:00:00Z")
            ]
          }
        }
      }
    }
  }
])

playground