CODEWITHSUNDEEP
mongodbamazon-web-servicesmongodb-queryaws-documentdb
Ritu Raut
Ritu Raut

Reputation: 73

Mongo $in query works in standalone mongo instance but not in AWS DocumentDB instance

db.getCollection('docs').aggregate([
    {
      $project: {
        "has books" : 
          { $in: ["A", {$cond: {if: {$gt: ["$books", null]}, then: "$books", else: []}}] }
      }
    }
])

The above query works when executed on a standalone MongoDB and gives the following result:

/* 1 */
{
    "_id" : ObjectId("5ca6023ccb9228c0ab417ad5"),
    "has books" : false
}

/* 2 */
{
    "_id" : ObjectId("5ca631b8cb9228c0ab419174"),
    "has books" : false
}

/* 3 */
{
    "_id" : ObjectId("5ca64006cb9228c0ab419a54"),
    "has books" : false
}

/* 4 */
{
    "_id" : ObjectId("5ca6e093cb9228c0ab41cf7c"),
    "has books" : true
}

/* 5 */
{
    "_id" : ObjectId("5ca6eee9cb9228c0ab41d594"),
    "has books" : false
}

However, on executing the same query on AWS DocumentDB instance the following error is given:

Failed to execute script.

Error: command failed: {
    "ok" : 0,
    "errmsg" : "$in requires an array as a second argument, found: object",
    "code" : 40081
} : aggregate failed 
Details:
_getErrorWithCode@src/mongo/shell/utils.js:25:13
doassert@src/mongo/shell/assert.js:18:14
_assertCommandWorked@src/mongo/shell/assert.js:534:17
assert.commandWorked@src/mongo/shell/assert.js:618:16
DB.prototype._runAggregate@src/mongo/shell/db.js:260:9
DBCollection.prototype.aggregate@src/mongo/shell/collection.js:1062:12
DBCollection.prototype.aggregate@:1:355
@(shell):1:1

The aim of the query is to check if a particular value is present in the array or not, with the output expected as boolean. Thedocs collection has an array field = books which can be null, so to overcome the null issue, $cond is used to replace books field with an empty array if the field does not exist or is null. $ifNull is not supported by AWS DocumentDB so it cannot be used.

Suggestions to correct the above query or use an alternative query which will be supported by AWS DocumentDB to get the expected result will be of immense help.

Upvotes: 2

Views: 309

Answers (1)

Tom Slabbaert
Tom Slabbaert

Reputation: 22296

So the problem is you have books field that's neither null nor an array.

You can work around this by using $ifNull like so:


db.getCollection('docs').aggregate([
    {
        $project: {
            "has books" :
                { $in: ["A", {$cond: {if: {$ne: [{$ifNull: ["$books", null]}, null]}, then: "$books", else: []}}] }
        }
    }
])

Or using $isArray:

db.getCollection('docs').aggregate([
    {
        $project: {
            "has books" :
                { $in: ["A", {$cond: {if: {$isArray: "$books"}, then: "$books", else: []}}] }
        }
    }
])

Or without using any of these operators because they're not supported then you can add a type field and then continue:

db.getCollection('docs').aggregate([
    {
        $addFields: {
            booksType: {$type: "$books"}
        }
    },
    {
        $project: {
            "has books" :
                { $in: ["A", {$cond: {if: {$eq: ["$booksType", 4]}, then: "$books", else: []}}] }
        }
    }
])

Upvotes: 1

Related Questions