CODEWITHSUNDEEP
mongodbpymongo
Alan
Alan

Reputation: 2498

MongoDB Query Syntax: how to use $isNull inside $in (pymongo api)

I have a document that looks like:

{
"_id": ObjectId(),
"employees": [
   {
        "_id": ObjectId(),
        "sharedBranches": [
            ObjectId(),
            ObjectId()
        ]
   },
{
        "_id": ObjectId()
   }
]
}

I am trying to return the documents that contains my input ObjectId in the sharedBranches field, and also filter the employees array down so it only contains objects whose sharedBranches contains my input ObjectId.

However, not every employee object (i.e. the elem in the employees array) contains the sharedBranches field. My query is returning an error which I am pretty sure is due to the Nulls, but I can't figure out the syntax for $isNull. here is my query. (note the branch_id is the input ObjectId I am searching on.

collection = client["collection"]["documents"]
pipeline = [
        {
            "$match": {
                "employees.sharedBranches": {"$elemMatch": {"$eq": ObjectId(branch_id)}},
            }
        },
        {
            "$project": {
                "employees": {
                    "$filter": {
                        "input": "$employees",
                        "as": "employees",
                        "cond": {"$in": [ObjectId(branch_id), {"$ifNull": ["$$employees.sharedBranches", []]}]}
                    }
                }
            }
        }
    ]

This query returns the error:

OperationFailure: $in requires an array as a second argument, found: object, full error: {'ok': 0.0, 'code': 40081, 'errmsg': '$in requires an array as a second argument, found: object', 'operationTime': Timestamp(1639079887, 1)}

It seems that the $ifNull stuff is not evaluating to an array. If I remove the $ifNull stuff, and just try to use $in on the array directly (so my $cond looks like: "cond": {"$in": [ObjectId(branch_id), "$$employees.sharedBranches"]},

I get this error:

OperationFailure: $in requires an array as a second argument, found: string, full error: {'ok': 0.0, 'code': 40081, 'errmsg': '$in requires an array as a second argument, found: string', 'operationTime': Timestamp(1639080588, 1)}

So I am at a loss of how to resolve this. Is my issue with the $ifNull? Am I mistaken that it's needed at all?

Upvotes: 0

Views: 203

Answers (1)

Buzz Moschetti
Buzz Moschetti

Reputation: 7568

I suspect some of your sharedBranches fields are not arrays but strings with a single ID. Here is a little trick that sniffs for the $type of such things and if the field is not an array (which includes if it is missing which will return missing), it turns it into an array of one:

c = db.foo.aggregate([
    {$project: {
    employees: {$filter: {
            input: "$employees",
            as: "employees",
            cond: {$in: [targetSharedBranchID, {$cond:
                        {if:{$ne:[{$type:'$$employees.sharedBranches'},"array"]},
                         then:  ['$$employees.sharedBranches'], // ah HA!  Create array of one on the fly.
                                                                // OK if missing; will create an empty array.
                         else: '$$employees.sharedBranches'
                        }} ] }
        }}
    }}

    ,{$match: {$expr: {$gt:[{$size:"$employees"},0]} }}

]);

Upvotes: 1

Related Questions