Select documents where all values in an array field exist in another array

Question

I wasn't quite sure how to title this!

So I have an array of product IDs

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

And a bunch of customer documents in MongoDB

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

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

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

I tried:

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

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

I also tried:

'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?

I also tried

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

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

Sede · Accepted Answer · 2016-05-22 16:30:58Z

3

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" 
        ] 
    }} 
])

answered May 22, 2016 at 16:30

Sede

61.5k20 gold badges158 silver badges162 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Select documents where all values in an array field exist in another array

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related