MongoDB嵌套对象查询问题：查找expired为false的订阅周期

你遇到的问题确实是因为subscription.time_periods是键值对形式的嵌套文档，而非数组，而$elemMatch是专门用于查询数组元素的操作符，所以你的查询语句无法匹配到数据。

你的文档结构示例： #

{"_id" : ObjectId("605c8351e874ab0012081b7d"),"fw8_pk" : NumberLong(604063),"customer_name" : "Testing_9.6","customer_type" : "NP","customer_status" : "A","created_by" : NumberLong(0),"creation_date" : NumberLong(1616675665396),"last_updated_by" : NumberLong(0),"last_updated_date" : NumberLong(1681210340164),"last_sync_ts" : NumberLong(0),"subscription" : {"secondTrainingStatus" : {},"lastPeriodCurrent" : false,"modify_date" : NumberLong(1681236913526),"solution_list" : {"5eb6b989625d12ea8edac74b" : [ "57aa92751f610a8995d796d5", "5ec29b7e93a3c75ad8e04ad2", "5f73094dfde8bd2402889783", "545f95bee4b0ea26a1882362", "5ec29a2f93a3c75ad8e04ad0", "5ec29b3a93a3c75ad8e04ad1", "545f95bee4b0ea26a1882459", "5acf8f129fe8d2a3e0657496", "5ec29bd793a3c75ad8e04ad3"]},"time_periods" : {"1" : {"start" : NumberLong(1616673600000),"end" : NumberLong(1704110400000),"desc" : "TERM 1","expired" : false,"inGrace" : false,"extended" : true,"original_end" : NumberLong(0)},"2" : {"start" : NumberLong(1704196800000),"end" : NumberLong(1791633600000),"desc" : "TERM 2","expired" : false,"inGrace" : false,"extended" : false,"original_end" : NumberLong(0)}}}}

你执行的无效查询语句： #

db.collection.find({"subscription.time_periods": {$elemMatch: {expired: false}}})

解决方法 #

方法1：针对现有嵌套文档结构的查询

使用$expr配合$objectToArray将嵌套文档转换为数组，再筛选符合条件的元素：

db.collection.find({
  $expr: {
    $anyElementTrue: {
      $map: {
        input: {$objectToArray: "$subscription.time_periods"},
        in: {$eq: ["$$this.v.expired", false]}
      }
    }
  }
})

或者用$filter结合$size判断是否存在符合条件的元素：

db.collection.find({
  $expr: {
    $gt: [
      {$size: {
        $filter: {
          input: {$objectToArray: "$subscription.time_periods"},
          cond: {$eq: ["$$this.v.expired", false]}
        }
      }},
      0
    ]
  }
})

方法2：优化数据结构（推荐）

如果可以修改数据结构，将time_periods改为数组形式，比如：

"time_periods": [
  {
    "term": "1",
    "start": NumberLong(1616673600000),
    "end": NumberLong(1704110400000),
    "desc": "TERM 1",
    "expired": false,
    "inGrace": false,
    "extended": true,
    "original_end": NumberLong(0)
  },
  {
    "term": "2",
    "start": NumberLong(1704196800000),
    "end": NumberLong(1791633600000),
    "desc": "TERM 2",
    "expired": false,
    "inGrace": false,
    "extended": false,
    "original_end": NumberLong(0)
  }
]

修改后，查询会变得更简单直观：

db.collection.find({"subscription.time_periods.expired": false})
// 或者需要精确匹配单个元素时用$elemMatch
db.collection.find({"subscription.time_periods": {$elemMatch: {expired: false}}})

数组结构更适合这类多元素的查询场景，后续维护和扩展也更方便。

内容的提问来源于stack exchange，提问作者Dev