找到未包含在数组中的 MongoDB 文档。

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

Find MongoDB documents that are not contained across arrays

问题

MongoDB集合A包含具有数组的文档,其中包含集合B的一些文档ID:

集合A:

{
    some_ids_of_b: ["id1", ...]
}

集合B:

{
    _id: "id1"
},
{
    _id: "id2"
},
...

如何查询集合B中所有不包含在A文档的some_ids_of_b数组中的_id的文档?

英文:

MongoDB Collection A contains documents with an array with some document ids of collection B:

Collection A:

{
    some_ids_of_b: ["id1", ...]
}

Collection B:

{
    _id: "id1"
},
{
    _id: "id2"
},
...

How do I query all documents from B whose _ids are NOT in contained in the some_ids_of_b arrays of documents of A?

答案1

得分: 1

这是一种选项:

db.collectionB.aggregate([
  {$lookup: {
      from: "collectionA",
      let: {my_id: "$_id"},
      pipeline: [
        {$match: {$and: [
              {_id: collADocId},
              {$expr: {$in: "$$my_id", "$some_ids_of_b"}}
        ]}},
        {$project: {_id: 1}}
      ],
      as: "some_ids_of_b"
  }},
  {$match: {"some_ids_of_b.0": {$exists: false}}},
  {$unset: "some_ids_of_b"}
])

playground示例上查看它的工作方式。

英文:

One option is:

db.collectionB.aggregate([
  {$lookup: {
      from: "collectionA",
      let: {my_id: "$_id"},
      pipeline: [
        {$match: {$and: [
              {_id: collADocId},
              {$expr: {$in: ["$$my_id", "$some_ids_of_b"]}}
        ]}},
        {$project: {_id: 1}}
      ],
      as: "some_ids_of_b"
  }},
  {$match: {"some_ids_of_b.0": {$exists: false}}},
  {$unset: "some_ids_of_b"}
])

See how it works on the playground example

答案2

得分: 1

从集合B到集合A的简单查找并筛选,只保留那些在其中没有找到匹配项的文档。

db.collb.aggregate([
  {
    "$lookup": {
      "from": "colla",
      "localField": "_id",
      "foreignField": "someIdsOfB",
      "as": "a"
    }
  },
  {
    $match: {
      $expr: {
        $eq: [{$size: "$a"}, 0]
      }
    }
  }
])

演示

英文:

Simple lookup from collection B to A and filter to keep only those documents where you don't find any matches.

db.collb.aggregate([
  {
    "$lookup": {
      "from": "colla",
      "localField": "_id",
      "foreignField": "someIdsOfB",
      "as": "a"
    }
  },
  {
    $match: {
      $expr: {
        $eq: [{$size: "$a"}, 0]
      }
    }
  }
])

Demo

答案3

得分: 1

你可以使用聚合框架来完成:

  • $group$addToSet - 从A集合中的所有文档中获取所有$some_ids_of_b的值。
  • 使用 $set$reduce - 创建一个包含B集合中所有唯一ID值的数组。
  • $lookup - 从B集合中获取那些在$b_ids数组中不存在的文档,其中文档的_id不在$b_ids数组中。
  • $project - 以期望的输出格式投影数据。
db.A.aggregate([
  {
    "$group": {
      "_id": null,
      "b_ids": {
        "$addToSet": "$some_ids_of_b"
      }
    }
  },
  {
    "$set": {
      b_ids: {
        $reduce: {
          input: "$b_ids",
          initialValue: [],
          in: {
            $setUnion: [
              "$$value",
              "$$this"
            ]
          }
        }
      }
    }
  },
  {
    "$lookup": {
      from: "B",
      let: {
        b_ids: "$b_ids"
      },
      pipeline: [
        {
          "$match": {
            "$expr": {
              $ne: [
                {
                  "$in": [
                    "$_id",
                    "$$b_ids"
                  ]
                },
                true
              ]
            }
          }
        }
      ],
      as: "data"
    }
  },
  {
    "$project": {
      data: 1,
      _id: 0
    }
  }
])

工作示例

英文:

You can do it with Aggregation Framework:

  • $group and $addToSet - To get all $some_ids_of_b from all the documents in A collection.
  • $set with $reduce - To create an array with all unique values of the IDs from the B collection.
  • $lookup - To fetch the documents from the B collection, where the _id of the document is not present in the $b_ids array.
  • $project - To project data as expected output.
db.A.aggregate([
  {
    "$group": {
      "_id": null,
      "b_ids": {
        "$addToSet": "$some_ids_of_b"
      }
    }
  },
  {
    "$set": {
      b_ids: {
        $reduce: {
          input: "$b_ids",
          initialValue: [],
          in: {
            $setUnion: [
              "$$value",
              "$$this"
            ]
          }
        }
      }
    }
  },
  {
    "$lookup": {
      from: "B",
      let: {
        b_ids: "$b_ids"
      },
      pipeline: [
        {
          "$match": {
            "$expr": {
              $ne: [
                {
                  "$in": [
                    "$_id",
                    "$$b_ids"
                  ]
                },
                true
              ]
            }
          }
        }
      ],
      as: "data"
    }
  },
  {
    "$project": {
      data: 1,
      _id: 0
    }
  }
])

Working Example

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

发表评论

匿名网友

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

确定