Inconsistent behavior between $in with $addFields versus $in with $match stage ?

[Shiv]

Let's say we have two documents in below collection in 3.4.4-7-g70415a0.

db.testcol.insertMany([{ "name" : "first", "value" : [ 1, 2, 3 ] }, { "name" : "second", "value" : [ 4, 5, 6 ] }]);

Using $in with $addFields for input array [1,2,3]

Running below query

db.testcol.aggregate([{

$addFields: {

cmp: { $in: [  {

$zip: {

inputs: [[1, 2, 3], {

$range: [0, {

$size: {$literal:[1, 2, 3]}

}]

}]

}

},  {

$zip: {

inputs: ["$value", {

$range: [0, {

$size: "$value"

}]

}]

}

}] }

}

}])

returns

{ "_id" : ObjectId("591724e9f3b93cb449118cd3"), "name" : "first", "value" : [ 1, 2, 3 ], "cmp" : false }

{ "_id" : ObjectId("591724e9f3b93cb449118cd4"), "name" : "second", "value" : [ 4, 5, 6 ], "cmp" : false }

Using $match with input array and its index  [1, 0],  [2, 1],  [3, 2]

Running below query

db.testcol.aggregate([{

    $addFields: {

        valueWithIx: {

            $zip: {

                inputs: ["$value", {

                    $range: [0, {

                        $size: "$value"

                    }]

                }]

            }

        },

    }

}, {

    $match: {

        valueWithIx: {

            $in: [

                [1, 0],

                [2, 1],

                [3, 2]

            ]

        }

    }

}])

returns

{ "_id" : ObjectId("591724e9f3b93cb449118cd3"), "name" : "first", "value" : [ 1, 2, 3 ], "valueWithIx" : [ [ 1, 0 ], [ 2, 1 ], [ 3, 2 ] ] }

Can someone help understand this behavior ?  I'm expecting the first query to return value "cmp" : true for document with "name":first

Appreciate any help.

[Asya Kamsky]

In both cases you are generating an array that looks like original value with index:

[ [ 1, 0 ], [ 2, 1 ], [ 3, 2 ] ]

In the $match case you are asking:  match the document if Value with index is one of these value: [1,0], [2,1], [3,2] - since $match reaches "inside" arrays, you actually get a match if *any* of those three are true.   So if you add a few more documents with arrays like [3, 2, 1] and [1, 12, 13] you'll see that the second pipeline matches them even though I suspect what you want is only to match *exact* order, right?

In the first case, the $in expression expects a single value as first argument and a set/array as a second argument and tells you whether the first argument is found as an element in the second argument.  That's *not* the case here.

So, the correct solution for your problem depends on *what* you are trying to do.   Are you trying to match only arrays where the sequence [ 1 2, 3 ] appears in values array and only in that exact order?

You can do it this way:

db.testcol.find()

{ "_id" : "first", "value" : [ 1, 2, 3 ] }

{ "_id" : "second", "value" : [ 4, 5, 6 ] }

{ "_id" : "third", "value" : [ 1, 12, 13 ] }

{ "_id" : "fourth", "value" : [ 3, 2, 1 ] }

{ "_id" : "fifth", "value" : [ 1, 12, 13, 2, 3 ] }

{ "_id" : "sixth", "value" : [ 3, 2, 1, 2, 3 ] }

> db.testcol.aggregate([{$addFields:{ 

   cmp: {$in:[ 

      {$literal:[1,2,3]}, 

      {$map: {

         input:{$range:[0, {$subtract:[{$size:"$value"},2]}]}, 

         as:"l", 

         in: {$slice: [ "$value", "$$l", 3] } 

      }}

   ]}

}}])

{ "_id" : "first", "value" : [ 1, 2, 3 ], "cmp" : true }

{ "_id" : "second", "value" : [ 4, 5, 6 ], "cmp" : false }

{ "_id" : "third", "value" : [ 1, 12, 13 ], "cmp" : false }

{ "_id" : "fourth", "value" : [ 3, 2, 1 ], "cmp" : false }

{ "_id" : "fifth", "value" : [ 1, 12, 13, 2, 3 ], "cmp" : false }

{ "_id" : "sixth", "value" : [ 3, 2, 1, 2, 3 ], "cmp" : true }

As you can see, I test membership via $in but I'm testing presence of [1,2,3] among array of 3 element arrays generated from the value array by going through it and taking 3-element slices starting at the beginning.  So my test finds all the arrays that have 1, 2, 3 somewhere in them in order, but not when 1, 2, 3 are present but out of order or not adjacent.

Asya

--
You received this message because you are subscribed to the Google Groups "mongodb-user"
group.
 
For other MongoDB technical support options, see: https://docs.mongodb.com/manual/support/
---
You received this message because you are subscribed to the Google Groups "mongodb-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to mongodb-user+unsubscribe@googlegroups.com.
To post to this group, send email to mongod...@googlegroups.com.
Visit this group at https://groups.google.com/group/mongodb-user.
To view this discussion on the web visit https://groups.google.com/d/msgid/mongodb-user/43773d00-63c6-44d4-be47-e0d4f942a04b%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

--

Asya Kamsky
Lead Product Manager
MongoDB
Download MongoDB - mongodb.org/downloads
We're Hiring! - https://www.mongodb.com/careers

[Shiv]

'm trying to do exactly what you think I'm trying.

Thank you for the explanation.

Your solution is superior which also covers the variable size arrays, and compares the input sequence anywhere in the array while also generating the index in the same pipeline.

I've already removed my answer which was second query. Please answer the stackoverflow (https://stackoverflow.com/questions/43951978/querying-for-arrays-in-mongodb/43954562) which actually led to this post here.

Thanks again !

[Asya Kamsky]

I added my answer to the SO question.  Thanks for bringing it here, I think this is a great example of how to deal with arrays in aggregation.

--
You received this message because you are subscribed to the Google Groups "mongodb-user"
group.
 
For other MongoDB technical support options, see: https://docs.mongodb.com/manual/support/
---
You received this message because you are subscribed to the Google Groups "mongodb-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to mongodb-user+unsubscribe@googlegroups.com.
To post to this group, send email to mongod...@googlegroups.com.
Visit this group at https://groups.google.com/group/mongodb-user.

To view this discussion on the web visit https://groups.google.com/d/msgid/mongodb-user/2c852511-4ce7-42fa-b7a8-64fb6683063f%40googlegroups.com.

For more options, visit https://groups.google.com/d/optout.