In MongoDB, the $in aggregation pipeline operator returns a boolean indicating whether a specified value is in the array.

The $in aggregation pipeline operator is not to be confused with the $in query operator, which selects the documents where the value of a field equals any value in the specified array.

Example

Suppose we have a collection called products with the following documents:

{ "_id" : 1, "prod" : "Bat", "sizes" : [ "S", "M", "XL", "XXL" ] }
{ "_id" : 2, "prod" : "Hat", "sizes" : [ "S", "L", "XL" ] }
{ "_id" : 3, "prod" : "Cap", "sizes" : [ "XS", "S", "M", "L", "XL" ] }

We can use the $in operator to find out whether or not the sizes field contains the value L.

Example:

db.products.aggregate(
   [
     {
       $project:
          {
            hasLarge: { $in: [ "L", "$sizes" ] }
          }
     }
   ]
)

Result:

{ "_id" : 1, "hasLarge" : false }
{ "_id" : 2, "hasLarge" : true }
{ "_id" : 3, "hasLarge" : true }

Second Argument Must Resolve to an Array

The second argument of the $in operator must resolve to an array. If it doesn’t, an error is returned.

Suppose we add the following document to the collection:

{ "_id" : 4, "prod" : "Shirt", "sizes" : "L" }

Note that the sizes field is not an array – it’s a string.

Let’s apply $in to that document:

db.products.aggregate(
   [
     { $match: { _id: { $in: [ 4 ] } } },
     {
       $project:
          {
            hasLarge: { $in: [ "L", "$sizes" ] }
          }
     }
   ]
)

Result:

Error: command failed: {
	"ok" : 0,
	"errmsg" : "$in requires an array as a second argument, found: string",
	"code" : 40081,
	"codeName" : "Location40081"
} : aggregate failed :
_getErrorWithCode@src/mongo/shell/utils.js:25:13
doassert@src/mongo/shell/assert.js:18:14
_assertCommandWorked@src/mongo/shell/assert.js:618:17
assert.commandWorked@src/mongo/shell/assert.js:708:16
DB.prototype._runAggregate@src/mongo/shell/db.js:266:5
DBCollection.prototype.aggregate@src/mongo/shell/collection.js:1046:12
@(shell):1:1

In this case, the sizes field contains a string., and therefore an error is returned.

In this example, I also use $in with the $match operator in order to filter the documents to just those that I’m interested in. Note that this uses the $in query operator syntax, which accepts a single argument, and is not to be confused with the two argument syntax.

Null Values

The same thing will happen if the second argument is null.

Let’s add the following document to the collection:

{ "_id" : 5, "prod" : "Jeans", "sizes" : null }

Here’s what happens if we try to use $in with that document:

db.products.aggregate(
   [
     { $match: { _id: { $in: [ 5 ] } } },
     {
       $project:
          {
            hasLarge: { $in: [ "L", "$sizes" ] }
          }
     }
   ]
)

Result:

Error: command failed: {
	"ok" : 0,
	"errmsg" : "$in requires an array as a second argument, found: null",
	"code" : 40081,
	"codeName" : "Location40081"
} : aggregate failed :
_getErrorWithCode@src/mongo/shell/utils.js:25:13
doassert@src/mongo/shell/assert.js:18:14
_assertCommandWorked@src/mongo/shell/assert.js:618:17
assert.commandWorked@src/mongo/shell/assert.js:708:16
DB.prototype._runAggregate@src/mongo/shell/db.js:266:5
DBCollection.prototype.aggregate@src/mongo/shell/collection.js:1046:12
@(shell):1:1

Same error.

Missing Fields

It’s the same deal with missing fields.

Suppose we add the following document to our collection:

{ "_id" : 6, "prod" : "Shorts" }

And now we try to apply $in to the (non-existent) sizes field:

db.products.aggregate(
   [
     { $match: { _id: { $in: [ 6 ] } } },
     {
       $project:
          {
            hasLarge: { $in: [ "L", "$sizes" ] }
          }
     }
   ]
)

Result:

Error: command failed: {
	"ok" : 0,
	"errmsg" : "$in requires an array as a second argument, found: missing",
	"code" : 40081,
	"codeName" : "Location40081"
} : aggregate failed :
_getErrorWithCode@src/mongo/shell/utils.js:25:13
doassert@src/mongo/shell/assert.js:18:14
_assertCommandWorked@src/mongo/shell/assert.js:618:17
assert.commandWorked@src/mongo/shell/assert.js:708:16
DB.prototype._runAggregate@src/mongo/shell/db.js:266:5
DBCollection.prototype.aggregate@src/mongo/shell/collection.js:1046:12
@(shell):1:1

Regular Expressions

Unlike the $in query operator, the $in aggregation pipeline operator does not support the use of regular expressions.

Therefore, the following code does not result in a match:

db.products.aggregate(
   [
     { $match: { _id: { $in: [ 1, 2, 3 ] } } },
     {
       $project:
          {
            hasLarge: { $in: [ /^X/, "$sizes" ] }
          }
     }
   ]
)

Result:

{ "_id" : 1, "hasLarge" : false }
{ "_id" : 2, "hasLarge" : false }
{ "_id" : 3, "hasLarge" : false }

The result for all documents is false, even though the sizes field in all three documents does contain array elements that start with the uppercase X.