选择一个数组字段中的所有值都存在于另一个数组中的文档 [英] Select documents where all values in an array field exist in another array

问题描述

我不太确定如何命名它！

I wasn't quite sure how to title this!

所以我有一组产品 ID

So I have an array of product IDs

var productIds = [139,72,73,1,6]

还有一堆MongoDB中的客户文档

And a bunch of customer documents in MongoDB

{
    name: 'James',
    products: [ 73, 139 ],
    _id: 5741cff3f08e992598d0a39b
}

{
    name: 'John',
    products: [ 72, 99 ],
    _id: 5741d047f08e992598d0a39e
}

我想在所有产品都出现在产品 ID (productIds) 数组中时找到客户

I would like to find customers when all of their products appear in the array of product IDs (productIds)

我试过了:

'products' : {
    '$in': productIds
}

但这会返回 John，即使产品 ID 列表中不存在 99

But that returns John, even though 99 doesn't exist in the list of product IDs

我也试过:

'products' : {
    '$all': productIds
}

由于没有一个客户拥有所有产品，因此什么都不返回

Which returns nothing because none of the customer have ALL the products

有没有办法在单个查询中实现我需要的东西，还是我必须做一些查询后处理?

Is there a way to achieve what I need in a single query or am I going to have to do some post query processing?

我也试过

'products': {
    '$in': productIds,
    '$not': {
        '$nin': productIds
    }
}

但是当并非所有产品 ID 都匹配时，这似乎也会返回客户

but this also seems to return customers when not all product IDs match

推荐答案

您可以使用 .aggregate() 方法和 $redact 操作符.在您的 $cond 表达式中您需要使用 $setIsSubset 以检查products"数组中的所有元素是否都在productIds"中.这是因为您不能使用 $in 在条件表达式中

You can do this using the .aggregate() method and the $redact operator. In your $cond expressions you need to use the $setIsSubset in order to check if all the elements in the "products" array are in "productIds". This is because you cannot use $in in the conditional expression

var productIds = [139,72,73,1,6];
db.customers.aggregate([ 
    { "$redact": { 
        "$cond": [ 
            { "$setIsSubset": [ "$products", productIds ] },
            "$$KEEP",
            "$$PRUNE" 
        ] 
    }} 
])

这篇关于选择一个数组字段中的所有值都存在于另一个数组中的文档的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！