skip to Main Content

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

3

Answers


  1. 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

    Login or Signup to reply.
  2. 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

    Login or Signup to reply.
  3. 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

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search