首页 文章

查找数组字段不为空的MongoDB记录

提问于
浏览 次
360

我的所有记录都有一个名为“图片”的字段 . 该字段是一个字符串数组 .

我现在想要这个数组不为空的最新10条记录 .

我已经google了一下，但奇怪的是，我没有找到太多 . 我已经阅读了$ where选项，但我想知道本机函数有多慢，以及是否有更好的解决方案 .

即便如此，这也行不通：

ME.find({$where: 'this.pictures.length > 0'}).sort('-created').limit(10).execFind()

什么都不返回离开没有长度位的 this.pictures 确实有效，但当然它也会返回空记录 .

mongodb mongoose

10 回答

  • 28

    经过一番寻找，特别是在mongodb文件中，以及令人费解的一点，这就是答案：

    ME.find({pictures: {$exists: true, $not: {$size: 0}}})
    回复于
  • 89

    从2.6版本开始，另一种方法是将字段与空数组进行比较：

    ME.find({pictures: {$gt: []}})

    在shell中测试它：

    > db.ME.insert([
{pictures: [1,2,3]},
{pictures: []},
{pictures: ['']},
{pictures: [0]},
{pictures: 1},
{foobar: 1}
])

> db.ME.find({pictures: {$gt: []}})
{ "_id": ObjectId("54d4d9ff96340090b6c1c4a7"), "pictures": [ 1, 2, 3 ] }
{ "_id": ObjectId("54d4d9ff96340090b6c1c4a9"), "pictures": [ "" ] }
{ "_id": ObjectId("54d4d9ff96340090b6c1c4aa"), "pictures": [ 0 ] }

    所以它正确地包含了 pictures 至少有一个数组元素的文档，并且排除了 pictures 是空数组，不是数组或缺少的文档 .

    回复于
  • 26
    ME.find({pictures: {$exists: true}})

    很简单，这对我有用 .

    回复于
  • 0

    检索所有且仅查看“图片”为数组但不为空的文档

    ME.find({pictures: {$type: 'array', $ne: []}})

    如果使用3.2之前的MongoDb版本，请使用 $type: 4 而不是 $type: 'array' . 请注意，此解决方案甚至不使用$size，因此索引没有问题（"Queries cannot use indexes for the $size portion of a query"）

    其他解决方案，包括这些（接受的答案）：

    ME.find（{pictures：{$ exists：true，$ not：{$ size：0}}}）; ME.find（{pictures：{$ exists：true，$ ne：[]}}）

    是 wrong 因为他们返回文件，例如，'pictures'是 null ， undefined ，0等 .

    回复于
  • -7

    这可能也适合你：

    ME.find({'pictures.0': {$exists: true}});
    回复于
  • 4

    在查询时你关心两件事 - 准确性和性能 . 考虑到这一点，我在MongoDB v3.0.14中测试了一些不同的方法 .

    TL; DR db.doc.find({ nums: { $gt: -Infinity }}) 是最快且最可靠的（至少在我测试的MongoDB版本中） .

    编辑：这不再适用于MongoDB v3.6！有关可能的解决方案，请参阅此帖子下的评论 .

    设置

    我插入了1k文档没有列表字段，1k文档带有空列表，5个文档带有非空列表 .

    for (var i = 0; i < 1000; i++) { db.doc.insert({}); }
for (var i = 0; i < 1000; i++) { db.doc.insert({ nums: [] }); }
for (var i = 0; i < 5; i++) { db.doc.insert({ nums: [1, 2, 3] }); }
db.doc.createIndex({ nums: 1 });

    我认识到这不足以像我在下面的测试中那样认真对待性能，但它足以呈现所选查询计划的各种查询和行为的正确性 .

    测试

    db.doc.find({'nums': {'$exists': true}}) 返回错误的结果（我们正在努力实现的目标） .

    MacBook-Pro(mongod-3.0.14) test> db.doc.find({'nums': {'$exists': true}}).count()
1005

    db.doc.find({'nums.0': {'$exists': true}}) 返回正确的结果，但使用完整的集合扫描也很慢（请注意解释中的 COLLSCAN 阶段） .

    MacBook-Pro(mongod-3.0.14) test> db.doc.find({'nums.0': {'$exists': true}}).count()
5
MacBook-Pro(mongod-3.0.14) test> db.doc.find({'nums.0': {'$exists': true}}).explain()
{
  "queryPlanner": {
    "plannerVersion": 1,
    "namespace": "test.doc",
    "indexFilterSet": false,
    "parsedQuery": {
      "nums.0": {
        "$exists": true
      }
    },
    "winningPlan": {
      "stage": "COLLSCAN",
      "filter": {
        "nums.0": {
          "$exists": true
        }
      },
      "direction": "forward"
    },
    "rejectedPlans": [ ]
  },
  "serverInfo": {
    "host": "MacBook-Pro",
    "port": 27017,
    "version": "3.0.14",
    "gitVersion": "08352afcca24bfc145240a0fac9d28b978ab77f3"
  },
  "ok": 1
}

    db.doc.find({'nums': { $exists: true, $gt: { '$size': 0 }}}) 返回错误的结果 . 这是因为索引扫描无效，无法提升文档 . 如果没有索引，它可能会准确但很慢 .

    MacBook-Pro(mongod-3.0.14) test> db.doc.find({'nums': { $exists: true, $gt: { '$size': 0 }}}).count()
0
MacBook-Pro(mongod-3.0.14) test> db.doc.find({'nums': { $exists: true, $gt: { '$size': 0 }}}).explain('executionStats').executionStats.executionStages
{
  "stage": "KEEP_MUTATIONS",
  "nReturned": 0,
  "executionTimeMillisEstimate": 0,
  "works": 2,
  "advanced": 0,
  "needTime": 0,
  "needFetch": 0,
  "saveState": 0,
  "restoreState": 0,
  "isEOF": 1,
  "invalidates": 0,
  "inputStage": {
    "stage": "FETCH",
    "filter": {
      "$and": [
        {
          "nums": {
            "$gt": {
              "$size": 0
            }
          }
        },
        {
          "nums": {
            "$exists": true
          }
        }
      ]
    },
    "nReturned": 0,
    "executionTimeMillisEstimate": 0,
    "works": 1,
    "advanced": 0,
    "needTime": 0,
    "needFetch": 0,
    "saveState": 0,
    "restoreState": 0,
    "isEOF": 1,
    "invalidates": 0,
    "docsExamined": 0,
    "alreadyHasObj": 0,
    "inputStage": {
      "stage": "IXSCAN",
      "nReturned": 0,
      "executionTimeMillisEstimate": 0,
      "works": 1,
      "advanced": 0,
      "needTime": 0,
      "needFetch": 0,
      "saveState": 0,
      "restoreState": 0,
      "isEOF": 1,
      "invalidates": 0,
      "keyPattern": {
        "nums": 1
      },
      "indexName": "nums_1",
      "isMultiKey": true,
      "direction": "forward",
      "indexBounds": {
        "nums": [
          "({ $size: 0.0 }, [])"
        ]
      },
      "keysExamined": 0,
      "dupsTested": 0,
      "dupsDropped": 0,
      "seenInvalidated": 0,
      "matchTested": 0
    }
  }
}

    db.doc.find({'nums': { $exists: true, $not: { '$size': 0 }}}) 返回正确的结果，但性能不好 . 它在技术上做了索引扫描，但它仍然提前所有的文档，然后必须过滤它们） .

    MacBook-Pro(mongod-3.0.14) test> db.doc.find({'nums': { $exists: true, $not: { '$size': 0 }}}).count()
5
MacBook-Pro(mongod-3.0.14) test> db.doc.find({'nums': { $exists: true, $not: { '$size': 0 }}}).explain('executionStats').executionStats.executionStages
{
  "stage": "KEEP_MUTATIONS",
  "nReturned": 5,
  "executionTimeMillisEstimate": 0,
  "works": 2016,
  "advanced": 5,
  "needTime": 2010,
  "needFetch": 0,
  "saveState": 15,
  "restoreState": 15,
  "isEOF": 1,
  "invalidates": 0,
  "inputStage": {
    "stage": "FETCH",
    "filter": {
      "$and": [
        {
          "nums": {
            "$exists": true
          }
        },
        {
          "$not": {
            "nums": {
              "$size": 0
            }
          }
        }
      ]
    },
    "nReturned": 5,
    "executionTimeMillisEstimate": 0,
    "works": 2016,
    "advanced": 5,
    "needTime": 2010,
    "needFetch": 0,
    "saveState": 15,
    "restoreState": 15,
    "isEOF": 1,
    "invalidates": 0,
    "docsExamined": 2005,
    "alreadyHasObj": 0,
    "inputStage": {
      "stage": "IXSCAN",
      "nReturned": 2005,
      "executionTimeMillisEstimate": 0,
      "works": 2015,
      "advanced": 2005,
      "needTime": 10,
      "needFetch": 0,
      "saveState": 15,
      "restoreState": 15,
      "isEOF": 1,
      "invalidates": 0,
      "keyPattern": {
        "nums": 1
      },
      "indexName": "nums_1",
      "isMultiKey": true,
      "direction": "forward",
      "indexBounds": {
        "nums": [
          "[MinKey, MaxKey]"
        ]
      },
      "keysExamined": 2015,
      "dupsTested": 2015,
      "dupsDropped": 10,
      "seenInvalidated": 0,
      "matchTested": 0
    }
  }
}

    db.doc.find({'nums': { $exists: true, $ne: [] }}) 返回正确的结果并略快，但性能仍然不理想 . 它使用IXSCAN，它只使用现有列表字段推进文档，但随后必须逐个过滤掉空列表 .

    MacBook-Pro(mongod-3.0.14) test> db.doc.find({'nums': { $exists: true, $ne: [] }}).count()
5
MacBook-Pro(mongod-3.0.14) test> db.doc.find({'nums': { $exists: true, $ne: [] }}).explain('executionStats').executionStats.executionStages
{
  "stage": "KEEP_MUTATIONS",
  "nReturned": 5,
  "executionTimeMillisEstimate": 0,
  "works": 1018,
  "advanced": 5,
  "needTime": 1011,
  "needFetch": 0,
  "saveState": 15,
  "restoreState": 15,
  "isEOF": 1,
  "invalidates": 0,
  "inputStage": {
    "stage": "FETCH",
    "filter": {
      "$and": [
        {
          "$not": {
            "nums": {
              "$eq": [ ]
            }
          }
        },
        {
          "nums": {
            "$exists": true
          }
        }
      ]
    },
    "nReturned": 5,
    "executionTimeMillisEstimate": 0,
    "works": 1017,
    "advanced": 5,
    "needTime": 1011,
    "needFetch": 0,
    "saveState": 15,
    "restoreState": 15,
    "isEOF": 1,
    "invalidates": 0,
    "docsExamined": 1005,
    "alreadyHasObj": 0,
    "inputStage": {
      "stage": "IXSCAN",
      "nReturned": 1005,
      "executionTimeMillisEstimate": 0,
      "works": 1016,
      "advanced": 1005,
      "needTime": 11,
      "needFetch": 0,
      "saveState": 15,
      "restoreState": 15,
      "isEOF": 1,
      "invalidates": 0,
      "keyPattern": {
        "nums": 1
      },
      "indexName": "nums_1",
      "isMultiKey": true,
      "direction": "forward",
      "indexBounds": {
        "nums": [
          "[MinKey, undefined)",
          "(undefined, [])",
          "([], MaxKey]"
        ]
      },
      "keysExamined": 1016,
      "dupsTested": 1015,
      "dupsDropped": 10,
      "seenInvalidated": 0,
      "matchTested": 0
    }
  }
}

    db.doc.find({'nums': { $gt: [] }}) 由于使用的索引可能会带来意想不到的结果，因此是危险的 . 这是因为索引扫描无效，无法提供任何文档 .

    MacBook-Pro(mongod-3.0.14) test> db.doc.find({'nums': { $gt: [] }}).count()
0
MacBook-Pro(mongod-3.0.14) test> db.doc.find({'nums': { $gt: [] }}).hint({ nums: 1 }).count()
0
MacBook-Pro(mongod-3.0.14) test> db.doc.find({'nums': { $gt: [] }}).hint({ _id: 1 }).count()
5

MacBook-Pro(mongod-3.0.14) test> db.doc.find({'nums': { $gt: [] }}).explain('executionStats').executionStats.executionStages
{
  "stage": "KEEP_MUTATIONS",
  "nReturned": 0,
  "executionTimeMillisEstimate": 0,
  "works": 1,
  "advanced": 0,
  "needTime": 0,
  "needFetch": 0,
  "saveState": 0,
  "restoreState": 0,
  "isEOF": 1,
  "invalidates": 0,
  "inputStage": {
    "stage": "FETCH",
    "filter": {
      "nums": {
        "$gt": [ ]
      }
    },
    "nReturned": 0,
    "executionTimeMillisEstimate": 0,
    "works": 1,
    "advanced": 0,
    "needTime": 0,
    "needFetch": 0,
    "saveState": 0,
    "restoreState": 0,
    "isEOF": 1,
    "invalidates": 0,
    "docsExamined": 0,
    "alreadyHasObj": 0,
    "inputStage": {
      "stage": "IXSCAN",
      "nReturned": 0,
      "executionTimeMillisEstimate": 0,
      "works": 1,
      "advanced": 0,
      "needTime": 0,
      "needFetch": 0,
      "saveState": 0,
      "restoreState": 0,
      "isEOF": 1,
      "invalidates": 0,
      "keyPattern": {
        "nums": 1
      },
      "indexName": "nums_1",
      "isMultiKey": true,
      "direction": "forward",
      "indexBounds": {
        "nums": [
          "([], BinData(0, ))"
        ]
      },
      "keysExamined": 0,
      "dupsTested": 0,
      "dupsDropped": 0,
      "seenInvalidated": 0,
      "matchTested": 0
    }
  }
}

    db.doc.find({'nums.0’: { $gt: -Infinity }}) 返回正确的结果，但性能不佳（使用完整的集合扫描） .

    MacBook-Pro(mongod-3.0.14) test> db.doc.find({'nums.0': { $gt: -Infinity }}).count()
5
MacBook-Pro(mongod-3.0.14) test> db.doc.find({'nums.0': { $gt: -Infinity }}).explain('executionStats').executionStats.executionStages
{
  "stage": "COLLSCAN",
  "filter": {
    "nums.0": {
      "$gt": -Infinity
    }
  },
  "nReturned": 5,
  "executionTimeMillisEstimate": 0,
  "works": 2007,
  "advanced": 5,
  "needTime": 2001,
  "needFetch": 0,
  "saveState": 15,
  "restoreState": 15,
  "isEOF": 1,
  "invalidates": 0,
  "direction": "forward",
  "docsExamined": 2005
}

    db.doc.find({'nums': { $gt: -Infinity }}) 令人惊讶的是，这非常有效！它提供了正确的结果并且速度很快，从索引扫描阶段推进了5个文档 .

    MacBook-Pro(mongod-3.0.14) test> db.doc.find({'nums': { $gt: -Infinity }}).explain('executionStats').executionStats.executionStages
{
  "stage": "FETCH",
  "nReturned": 5,
  "executionTimeMillisEstimate": 0,
  "works": 16,
  "advanced": 5,
  "needTime": 10,
  "needFetch": 0,
  "saveState": 0,
  "restoreState": 0,
  "isEOF": 1,
  "invalidates": 0,
  "docsExamined": 5,
  "alreadyHasObj": 0,
  "inputStage": {
    "stage": "IXSCAN",
    "nReturned": 5,
    "executionTimeMillisEstimate": 0,
    "works": 15,
    "advanced": 5,
    "needTime": 10,
    "needFetch": 0,
    "saveState": 0,
    "restoreState": 0,
    "isEOF": 1,
    "invalidates": 0,
    "keyPattern": {
      "nums": 1
    },
    "indexName": "nums_1",
    "isMultiKey": true,
    "direction": "forward",
    "indexBounds": {
      "nums": [
        "(-inf.0, inf.0]"
      ]
    },
    "keysExamined": 15,
    "dupsTested": 15,
    "dupsDropped": 10,
    "seenInvalidated": 0,
    "matchTested": 0
  }
}
    回复于
  • 0

    您还可以在Mongo运算符$ exists上使用辅助方法Exists

    ME.find()
    .exists('pictures')
    .where('pictures').ne([])
    .sort('-created')
    .limit(10)
    .exec(function(err, results){
        ...
    });
    回复于
  • 0

    您可以使用以下任何方法来实现此目的 .
    两者都负责不为没有请求密钥的对象返回结果：

    db.video.find({pictures: {$exists: true, $gt: {$size: 0}}})
db.video.find({comments: {$exists: true, $not: {$size: 0}}})
    回复于
  • 160
    { $where: "this.pictures.length > 1" }

    使用$ where并传递this.field_name.length，它返回数组字段的大小，并通过与数字进行比较来检查它 . 如果任何数组有任何值，则数组大小必须至少为1.所以所有数组字段的长度都不止一个，这意味着它在该数组中有一些数据

    回复于
  • 579

    如果您还有没有密钥的文档，您可以使用：

    ME.find({ pictures: { $exists: true, $not: {$size: 0} } })

    如果涉及$ size，MongoDB不使用索引，所以这是一个更好的解决方案：

    ME.find({ pictures: { $exists: true, $ne: [] } })

    自MongoDB 2.6发布以来，您可以与运营商 $gt 进行比较，但可能会导致意外结果（您可以找到详细解释in this answer）：

    ME.find({ pictures: { $gt: [] } })
    回复于

相关问题