MongoDB – 在管道中根据数组的所有元素添加聚合字段

huangapple go评论58阅读模式
英文:

MongoDB - Add aggregate field in pipeline depending on all elements of an array

问题

I've translated the provided content as requested. Here's the translated code and explanation:

给定集合中的以下文档:

```json
[{
  "_id": {
    "$oid": "63f06283b80a395adf27780d"
  },
  "suppliers": [
    {
      "name": "S1",
      "duesPaid": true
    },
    {
      "name": "S2",
      "duesPaid": true
    }
  ]
},{
  "_id": {
    "$oid": "63f06283b80a395adf27780e"
  },
  "suppliers": [
    {
      "name": "S1",
      "duesPaid": true
    },
    {
      "name": "S2",
      "duesPaid": false
    }
  ]
}]

我想在每个文档中创建一个聚合字段,执行以下操作:如果suppliers数组至少有1个元素,并且其中的每个元素都具有duesPaid == true,则向文档添加一个字段suppliersPaid = true。否则,添加suppliersPaid = false。管道生成的文档应如下所示:

[{
  "_id": {
    "$oid": "63f06283b80a395adf27780d"
  },
  "suppliers": [
    {
      "name": "S1",
      "duesPaid": true
    },
    {
      "name": "S2",
      "duesPaid": true
    }
  ],
  "suppliersPaid": true,
},{
  "_id": {
    "$oid": "63f06283b80a395adf27780e"
  },
  "suppliers": [
    {
      "name": "S1",
      "duesPaid": true
    },
    {
      "name": "S2",
      "duesPaid": false
    }
  ],
  "suppliersPaid": false,
}]

我尝试了以下管道:

[{$addFields: {
  suppliersPaid: {
    $and: [
      { $gte: [{ $size: "$suppliers" }, 1] },
      {
        suppliers: {
          $not: {
            $elemMatch: { duesPaid: false },
          },
        },
      },
    ],
  },
}}]

但是我遇到了以下错误:

无效的$addFields :: 由于 :: 无法识别的表达式'$elemMatch'

我尝试消除对$elemMatch的依赖,根据文档 https://www.mongodb.com/docs/manual/reference/operator/query/elemMatch/#single-query-condition,如下所示:

[{$addFields: {
  suppliersPaid: {
    $and: [
      { $gte: [{ $size: "$suppliers" }, 1] },
      {
        suppliers: {
          $not: {
            duesPaid: false
          },
        },
      },
    ],
  },
}}]

但是这会导致将suppliersPaid设置为两个文档中都为true的错误结果,这是不正确的。

注意:我希望在此代码中避免使用任何JavaScript,即不使用$where运算符。

英文:

Given the following documents in a collection:

[{
  "_id": {
    "$oid": "63f06283b80a395adf27780d"
  },
  "suppliers": [
    {
      "name": "S1",
      "duesPaid": true
    },
    {
      "name": "S2",
      "duesPaid": true
    }
  ]
},{
  "_id": {
    "$oid": "63f06283b80a395adf27780e"
  },
  "suppliers": [
    {
      "name": "S1",
      "duesPaid": true
    },
    {
      "name": "S2",
      "duesPaid": false
    }
  ]
}]

I would like to create an aggregate field in each document that does the following: If the suppliers array has at least 1 element and every element in that has the duesPaid == true, then add a field to the document suppliersPaid = true. Otherwise, add suppliersPaid = false. The resulting documents from the pipeline should look like this:

[{
  "_id": {
    "$oid": "63f06283b80a395adf27780d"
  },
  "suppliers": [
    {
      "name": "S1",
      "duesPaid": true
    },
    {
      "name": "S2",
      "duesPaid": true
    }
  ],
  "suppliersPaid": true,
},{
  "_id": {
    "$oid": "63f06283b80a395adf27780e"
  },
  "suppliers": [
    {
      "name": "S1",
      "duesPaid": true
    },
    {
      "name": "S2",
      "duesPaid": false
    }
  ],
  "suppliersPaid": false,
}]

I have tried the following pipeline:

[{$addFields: {
  suppliersPaid: {
    $and: [
      { $gte: [{ $size: "$suppliers" }, 1] },
      {
        suppliers: {
          $not: {
            $elemMatch: { duesPaid: false },
          },
        },
      },
    ],
  },
}}]

and I get the following error:

> Invalid $addFields :: caused by :: Unrecognized expression '$elemMatch'

I've tried to eliminate the reliance on $elemMatch per the docs https://www.mongodb.com/docs/manual/reference/operator/query/elemMatch/#single-query-condition as such:

[{$addFields: {
  suppliersPaid: {
    $and: [
      { $gte: [{ $size: "$suppliers" }, 1] },
      {
        suppliers: {
          $not: {
            duesPaid: false
          },
        },
      },
    ],
  },
}}]

But this yields the incorrect result of setting suppliersPaid to true for both documents, which is incorrect.

Note: I would like to avoid using any sort of JS in this code i.e. no $where operator.

答案1

得分: 1

对于第二个条件:

  1. $eq - 比较 1.1 的结果,返回一个空数组。

    1.1. $filter - 过滤包含 { duesPaid: false }suppliers 文档。

db.collection.aggregate([
  {
    $addFields: {
      suppliersPaid: {
        $and: [
          {
            $gte: [
              {
                $size: "$suppliers"
              },
              1
            ]
          },
          {
            $eq: [
              {
                $filter: {
                  input: "$suppliers",
                  cond: {
                    $eq: [
                      "$$this.duesPaid",
                      false
                    ]
                  }
                }
              },
              []
            ]
          }
        ]
      }
    }
  }
])

在 Mongo Playground 上演示

英文:

For the second condition:

  1. $eq - Compare the result from 1.1 to return an empty array.

    1.1. $filter - Filter the documents from suppliers containing { duesPaid: false }.

db.collection.aggregate([
  {
    $addFields: {
      suppliersPaid: {
        $and: [
          {
            $gte: [
              {
                $size: "$suppliers"
              },
              1
            ]
          },
          {
            $eq: [
              {
                $filter: {
                  input: "$suppliers",
                  cond: {
                    $eq: [
                      "$$this.duesPaid",
                      false
                    ]
                  }
                }
              },
              []
            ]
          }
        ]
      }
    }
  }
])

Demo @ Mongo Playground

huangapple
  • 本文由 发表于 2023年2月18日 13:59:43
  • 转载请务必保留本文链接:https://go.coder-hub.com/75491493.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定