How to find the array in mongodb?

user_15264143 0
Viewed 29

I tried mongodb,

db.getCollection('productList').find({"season": {$elemMatch: {'$gte':2018,'$lte':2020}}, team:"Arsenal"})

for above query i got the following result,

{
    "_id": ObjectId("46ab3e933434234f"),
    "team": "Arsenal",
    "season": [2018, 2020]
},{
    "_id": ObjectId("46ab3e933434234f"),
    "team": "Arsenal",
    "season": [2017, 2018] //I don`t want because of 2017
},{
    "_id": ObjectId("46ab3e933434234f"),
    "team": "Arsenal",
    "season": [2020, 2021] //I don`t want because of 2021
},{
    "_id": ObjectId("46ab3e933434234f"),
    "team": "Arsenal",
    "season": [2018, 2019]
},{
    "_id": ObjectId("46ab3e933434234f"),
    "team": "Arsenal",
    "season": [2019, 2020]
},

I want to get the following result,

{
    "_id": ObjectId("46ab3e933434234f"),
    "team": "Arsenal",
    "season": [2018, 2020]
},{
    "_id": ObjectId("46ab3e933434234f"),
    "team": "Arsenal",
    "season": [2018, 2019]
},{
    "_id": ObjectId("46ab3e933434234f"),
    "team": "Arsenal",
    "season": [2019, 2020]
},

I want to get the season between "2018 ~ 2020" only.

mongodb
1 Answers

Try opposite condition with $not operator,

  • use $lt with $not for "2018" season
  • use $gt with $not for "2020" season
db.getCollection('productList').find({
  $and: [
    { season: { $not: { $lt: 2018 } } },
    { season: { $not: { $gt: 2020 } } },
    { team: "Arsenal" }
  ],
})

Playground

The second approach using expression aggregation operators,

  • $filter to iterate loop of season and check both conditions it will result in filtered season
  • $size to get total returned elements from filter
  • $eq to match both elements size should be the same
db.getCollection('productList').find({
  team: "Arsenal",
  $expr: {
    $eq: [
      {
        $size: {
          $filter: {
            input: "$season",
            cond: {
              $and: [
                { $gte: ["$$this", 2018] },
                { $lte: ["$$this", 2020] }
              ]
            }
          }
        }
      },
      { $size: "$season" }
    ]
  }
})

Playground

user_89871280
Related