CODEWITHSUNDEEP
mongodbmongodb-query
Hubert
Hubert

Reputation: 42

Querying for arrays in MongoDB

Suppose in collection I have following documents:

[
  {"title": "t1", "fingerprint":[1, 2, 3]},
  {"title": "t2", "fingerprint":[4, 5, 6]}
]

I want to query documents in which at least one element in fingerprint at given position is equal to my querying array. For example: query([1, 7, 9]) should return [{"title": "t1", "fingerprint":[1, 2, 3]}]

query([1, 5, 9]) should return [{"title": "t1", "fingerprint":[1, 2, 3]}, {"title": "t2", "fingerprint":[4, 5, 6]}]

but query([5,1,9]) should return none records, because neither of records have same value at any of the positions in fingerprint array. How to write given query?

Upvotes: 0

Views: 483

Answers (3)

Asya Kamsky
Asya Kamsky

Reputation: 42342

When you are trying to match only documents with arrays where the sequence [ 1 2, 3 ] appears in values field 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 }

What the $addFields stage does is checks if [1,2,3] appears in a list of three element arrays starting at position 0 of value array and moving forward till two positions before the end.

As you can see, it's now trivial to add a $match stage to filter out documents where cmp is not true.

Upvotes: 3

Rubin Porwal
Rubin Porwal

Reputation: 3845

$in operator is used to match a value against list of values.

According to above mentioned description please try executing following query in MongoDB shell

db.collection.find({fingerprint:{$in:[1,7,9]}})

Upvotes: 0

Julien TASSIN
Julien TASSIN

Reputation: 5212

You can use the .$index notation to perform such a search.

Example for your query([1, 7, 9])

db.coll.find({$or: [{"fingerprint.0": 1}, {"fingerprint.1": 7 }, {"fingerprint.2": 9}]})
{ "_id" : ObjectId("59170da907e34e73c0c93a9b"), "title" : "t1", "fingerprint" : [ 1, 2, 3 ] }

And query([1, 5, 9])

db.coll.find({$or: [{"fingerprint.0": 1}, {"fingerprint.1": 5 }, {"fingerprint.2": 9}]})
{ "_id" : ObjectId("59170da907e34e73c0c93a9b"), "title" : "t1", "fingerprint" : [ 1, 2, 3 ] }
{ "_id" : ObjectId("59170da907e34e73c0c93a9c"), "title" : "t2", "fingerprint" : [ 4, 5, 6 ] }

Upvotes: 1

Related Questions