MongoDB, ObjectId's Array not recognized as array when using $nin

Question

I'm trying to match documents based on the fact that their _id is not present in ObjectId's Array of their own.

Mongodb version: 3.2

Here's my query :

db.subscriptions.aggregate(
[
    { $match : { "device": { $exists: true } } },
    { 
      $lookup : { 
            from: "devices",
            localField: "device",
            foreignField: "_id",
            as: "device"
        } 
    },
    { $match : { "device.0" : { $exists: true } } },
    { $unwind: "$device" },
    { $project : {"_id": 1, "subs": "$device.subscriptions" } },
    { $match: { "_id": { $in: this.subs } } }
]
)

I have two collection subscriptions and devices. The document subscription have a property device which is an ObjectId refering to a device. The device document have an array of objectId refering to the subscriptions. It's a one-to-many relationship.

Step 1: Matching subscription who actualy have the device property (it's not a clean clean db)

Step 2: I'm performing a lookup to get my device document inside the subscription

Step 3: Once again matching the fact that the lookup did give result

Step 4: Unwind the device cause the lookup thing give me an array (i dont quite understand why tho')

Step 5: Projecting the subscriptions array and the subscription id to work with simpler document:

{ 
    "_id" : ObjectId("587e265cb9b235243c7f9960"), 
    "subs" : [
        ObjectId("5899a894b1e11521a44a96f7"), 
        ObjectId("5a8c494db0bed60b389cb1da")
    ]
}

Step 6: This is where the error appear, mongo's teling me this.subs is not an array so i cant use $nin => "errmsg" : "$in needs an array"

I've tried multiple format: "this.subs", "$subs", none of that works.

Can somebody helps me ?

Théo

what is the result when you remove the last query in pipeline and add the result in your question? because I am unable to make sense without data. — Muhammad Faizan
– Muhammad Faizan, Commented Mar 8, 2018 at 18:35
You can see step 5 my result. I get these kind of documents when i'm removing the last $match. @Veeram answer was the one. Thanks nonetheless for you reply ! — Theocat
– Theocat, Commented Mar 9, 2018 at 10:29

s7vr · Accepted Answer · 2018-03-08 19:37:54Z

1

Try below aggregation in 3.2. You can't compare the document field in $match stage. So you have to create a intermediate field (cmp) to hold the result from such comparison and $match stage to compare the result. Also there is no $in ( aggregation operator ). You have to use $setIsSubset to mimic such comparisons.

db.subscriptions.aggregate(
  [
    previous stages stages until $unwind
    {$project:{
      add all the fields you want in response,
      "cmp":{$not:{$setIsSubset: [["$_id"], "$device.subscriptions"]}}
    }},
    {$match: {"cmp": true}}
  ]
)

Upgrade to 3.4 you can use $in(aggregation) operator and below pipeline.

db.subscriptions.aggregate(
  [
    previous stages stages until $unwind
    {$project:{
      add all the fields you want in response,
      "cmp":{$not:{$in: ["$_id", "$device.subscriptions"]}}
    }},
    {$match: {"cmp": true}}
  ]
)

Upgrade to 3.6 you can use $in(aggregation) operator inside the $match with $expr which lets you use aggregation operators.

db.subscriptions.aggregate(
  [
    previous stages stages until $unwind
    {$match: {"$expr": {$not:{$in: ["$_id", "$device.subscriptions"]}}}}
  ]
)

answered Mar 8, 2018 at 19:37

s7vr

76.2k12 gold badges118 silver badges135 bronze badges

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

1 Comment

Theocat Over a year ago

Thanks a lot ! Your answer was perfect. I've only tested the 3.2 version so far but i'm glad you added all of them. I might upgrade my db version in the near futur.

Collectives™ on Stack Overflow

MongoDB, ObjectId's Array not recognized as array when using $nin

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related