在以给定顺序显示的文档数组中查找两个元素 [英] Finding two elements in an array of documents that appear in a given order

问题描述

假设我在数据库中具有以下文档结构:

Let's assume I have the following document structure in database:

{ name: String,
  subDocs: [{
    index: Number,
    value: Number
  }]
}

这样一个典型的文档看起来像:

So that a typical document looks like:

{ name: 'Document 1',
  subDocs: [ 
    { index: 0, value: 30 },
    { index: 1, value: 40 },
    { index: 2, value: 10 },
    { index: 3, value: 20 },
    { index: 4, value: 700 },
    { index: 5, value: 40 }
  ]
}

现在，我想查找所有包含subDocs且其值A = 10和B = 40的所有文档，但是数组中各项的出现必须满足以下要求. B.索引.因此，基本上，具有A值的项目必须早于B出现在集合中.因此，上面的对象满足了要求，而没有满足要求，因为这些值没有按以下顺序出现:

Now, I would like to find all documents which contain subDocs with values A = 10 and B = 40 BUT the occurrence of the items in the array must meet the following requirement A.index < B.index. So basically the item with the value of A must appear earlier in the collection than B. So the object above meets the requirement while this one not, because the values don't appear in the order:

{ name: 'Document 2',
  subDocs: [ 
    { index: 0, value: 40 },
    { index: 1, value: 70 },
    { index: 2, value: 10 },
    { index: 3, value: 20 },
    { index: 4, value: 700 }
  ]
}

我们可以用Mongoose实现它，但又不牺牲这种查询的性能吗?

Can we achieve it with Mongoose yet not sacrificing performance of such a query?

推荐答案

如果您要在查询中使用这种约束，那么根据您的MongoDB版本所支持的内容，您基本上有两个选择:

If you want that sort of constraint in the query, then you basically have two options depending on what your MongoDB version supports:

您最好使用 $expr 另外" 到任何常规查询条件中，以实际选择有效的文档:

You would preferably use $expr "in addition" to any normal query conditions to actually select valid documents:

var A = 10, B = 40;

Model.find({
  "subDocs.value": { "$all": [A, B] },
  "$expr": {
    "$lt": [
      { "$arrayElemAt": [
        "$subDocs.index",
        { "$indexOfArray": [ "$subDocs.value", A ]}
      ]},
      { "$arrayElemAt": [
        "$subDocs.index",
        { "$indexOfArray": [ "$subDocs.value", B ]}  
      ]}
    ]
  }
})

或匹配最后一个" 事件:

Model.find({
  "subDocs.value": { "$all": [A, B] },
  "$expr": {
    "$lt": [
      { "$arrayElemAt": [
        "$subDocs.index",
        { "$subtract": [
          { "$subtract": [{ "$size": "$subDocs.value" }, 1 ] },
          { "$indexOfArray": [ { "$reverseArray": "$subDocs.value" }, A ] }
        ]}
      ]},
      { "$arrayElemAt": [
        "$subDocs.index",
        { "$subtract": [
          { "$subtract": [{ "$size": "$subDocs.value" }, 1 ] },
          { "$indexOfArray": [ { "$reverseArray": "$subDocs.value" }, B ] }
        ]}
      ]}
    ]
  }
})

早期版本

相同，但没有本机运算符，则需要使用 $where :

var A = 10, B = 40;

Model.find({
  "subDocs.value": { "$all": [A, B] },
  "$where": `this.subDocs.find( e => e.value === ${A}).index
      < this.subDocs.find( e => e.value === ${B}).index`
})

或匹配最后一个" 事件:

Model.find({
  "subDocs.value": { "$all": [10,40] },
  "$where": `let arr = this.subDocs.reverse();
      return arr.find( e => e.value === ${A}).index
        > arr.find( e => e.value === ${B}).index`
})

如果需要在聚合管道中使用，则可以使用 $redact 和与第一个示例类似的逻辑:

If you needed that in an aggregation pipeline, then you would use $redact and similar logic to the first example instead:

var A = 10, B = 40;

Model.aggregate([
  { "$match": { "subDocs.value": { "$all": [A, B] } } },
  { "$redact": {
    "$cond": {
      "if": {
        "$lt": [
          { "$arrayElemAt": [
            "$subDocs.index",
            { "$indexOfArray": [ "$subDocs.value", A ]}
          ]},
          { "$arrayElemAt": [
            "$subDocs.index",
            { "$indexOfArray": [ "$subDocs.value", B ]}  
          ]}
        ]
      },
      "then": "$$KEEP",
      "else": "$$PRUNE"
    }
  }}
])

可以说比较逻辑"实际上并不是查询运算符"本身的本机，因此可以将优化" 应用于索引的唯一部分是使用 $all 查询运算符.剩下的基本逻辑实际上适用于在主表达式被求值之后"和除"之外，以便不返回除通过

Suffice to say that the "comparison logic" is not actually native to "query operator expressions" themselves, so the only part that "optimally" can be applied to an index is using the $all query operator in all cases. The essential remaining logic actually applies "after" that main expression is evaluated and "in addition to" in order that no results are returned other that those meeting the expression with either $expr or $where.

每种方法的基本逻辑实质上是从实际上与"value"属性中的各个值匹配的第一"数组成员中提取"index"属性的值.如果小于"，则条件为true，这满足了要返回的文档.

The basic logic of each is essentially to extract the value of the "index" property from the "first" array member that actually matches the respective value in the "value" property. Where this is "less than", then the condition is true and this satisfies the document being returned.

因此请注意，要么计算的评估"与查询运算符的效率相匹配，并且不与其他能够访问索引"的查询运算符条件组合"使用，则将进行全集合扫描"启动.

So note that either "calculated evaluation" matches the efficiency of query operators, and without being used "in combination" of other query operator conditions which are able to access an "index", then a "full collection scan" will be initiated.

但是总的结果肯定比将所有匹配项返回到第一个查询条件然后在从数据库返回的之后"游标上拒绝它们更为有效.

But the overall result is certainly more efficient than returning all matching items to the first query condition and then rejecting them on the cursor "after" returning from the database.

另请参阅 $arrayElemAt ， $indexOfArray ，

See also documentation for $arrayElemAt, $indexOfArray, $lt and Array.find() for JavaScript

这篇关于在以给定顺序显示的文档数组中查找两个元素的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！