Mongodb从输入数组中查找数组字段中的子集

Question

假设我有下面的db，

const data = [
  {
    "chapter": 1,
    "targets": [
      {
        type: 'user',
        recipientId: 1
      }
    ],
    "challenge": [
      {
        "activate": true,
        "challengeId": "ch-1"
      },
      {
        "activate": true,
        "challengeId": "ch-2"
      },
      {
        "activate": true,
        "challengeId": "ch-3"
      },
    ]
  },
  {
    "chapter": 1,
    "targets": [
      {
        type: 'user',
        recipientId: 2
      }
    ],
    "challenge": [
      {
        "activate": true,
        "challengeId": "ch-2"
      },
      {
        "activate": true,
        "challengeId": "ch-3"
      },
      {
        "activate": true,
        "challengeId": "ch-4"
      },
    ]
  },
  {
    "chapter": 1,
    "targets": [
      {
        type: 'user',
        recipientId: 3
      }
    ],
    "challenge": [
      {
        "activate": true,
        "challengeId": "ch-4"
      },
      {
        "activate": true,
        "challengeId": "ch-5"
      },
    ]
  },
  {
    "chapter": 1,
    "targets": [
      {
        type: 'user',
        recipientId: 4
      }
    ],
    "challenge": [
      {
        "activate": true,
        "challengeId": "ch-2"
      },
      {
        "activate": false,
        "challengeId": "ch-3"
      },
      {
        "activate": true,
        "challengeId": "ch-4"
      },
    ]
  },
],

并且我想使用一个recipientId数组进行查询，如果我输入[1,2]，它应该打开challengeId的交叉口，预期的输出是

[
{"challengeId": "ch-2"},
{"challengeId": "ch-3"}
]

如果输入是[1,2,3]，它应该返回[]，因为没有与此输入的交集。

如果输入是[1,2,4]，则返回

[
{"challengeId": "ch-2"},
]

因为challengeId ch-3 of recipientId 4还没有激活。

我一直在读$setIntersection，但它似乎不能解决我的问题。

有什么建议吗？谢谢。

Answer 1

我认为使用此查询可以非常接近您的结果。你可能只是想把结果清理一下。

const input = [1,2,4]
db.test.aggregate([
  //filter by input array
  { $match: { "targets.recipientId": { $in: input } } },
  // Unwind the challenge array
  { $unwind: "$challenge" },
  //filter out the non-active ones
  { $match: { "challenge.activate": true } },
  //group by challengeId and keep track of the count
  { $group: { _id: "$challenge.challengeId", count: { $sum: 1 } } },
  //filter out the challengeId with count < input.length
  { $match: { count: { $gte: input.length } } },
]);

result: {"_id":"ch-2","count":3}

Answer 2

您需要实现一些自定义逻辑，因为$setIntersecion不能接受动态数组字段，

• $match到recipientId字段
• $project过滤挑战是activate: ture
• $group by null以计数总文档
• $unwind挑战数组2次，因为为了计数目的，我们将其分组在上面
• $group by challengeId和获取挑战计数
• 检查文档总数和质询计数的$match是相同的
• 显示所需字段的$project

db.collection.aggregate([
  { $match: { "targets.recipientId": { $in: [1, 2, 4] } } },
  {
    $project: {
      challenge: {
        $filter: {
          input: "$challenge",
          cond: { $eq: ["$$this.activate", true] }
        }
      }
    }
  },
  {
    $group: {
      _id: null,
      challenge: { $push: "$challenge" },
      count: { $sum: 1 }
    }
  },
  { $unwind: "$challenge" },
  { $unwind: "$challenge" },
  {
    $group: {
      _id: "$challenge.challengeId",
      cCount: { $sum: 1 },
      count: { $first: "$count" }
    }
  },
  { $match: { $expr: { $eq: ["$count", "$cCount"] } } },
  {
    $project: {
      _id: 0,
      challengeId: "$_id"
    }
  }
])

游乐场