Using Mongo query to find an in array element

Question

Have records in my db with such structure:

{ 
  "_id" : "YA14163134", 
  "discount" : "", 
  "retail" : "115.0000", 
  "cost" : "", 
  "description" : "Caterpillar Mens Big Twist Analog Watch", 
  "stock_update" : "05", 
  "brand" : "Kronos", 
  "img_url" : "image2342000.jpg", 
  "UPC" : "4895053708012", 
  "stock" : [ [ "1611292138", "5" ], [ "1612032232", "4" ], [ "1612050918", "0" ] ] 
}

and looking for query to get all records that have in "stock" "1612050918" value. That is update id.

Trying something like:

db.vlc.find({stock: {$elemMatch:{$all:["1612050918"]}}})

or

db.vlc.find({stock: { $in : ['1611292138']}})

or

db.vlc.find({stock: { $all : [[1611292138]]}})

with no result. It works only if I include in request second array element like here

db.vlc.find({stock: { $all : [['1611292138', '7']]}})

but that limit my request to all items from update with qnty 7 when I need with any qnty. Thank you in advance!

code_monk · Accepted Answer · 2016-12-05 22:01:55Z

1

use this query:

{
    "stock" : {
        "$elemMatch" : {
            "$elemMatch" : {
                "$eq" : "1611292138"
            }
        }
    }
}

Explanation:

The first $elemMatch allows you to scan all three arrays under stock
The nex $elemMatch allows you to scan the two elements in the sub-arrays
since $elemMatch requires a query object, the $eq notation is used for a literal match.

If you know that "1611292138" will always be the first element of the sub-array, your query becomes simpler:

{ "stock" : { "$elemMatch" : { "0" : "1611292138" } } }

Explanation:

Scan all arrays under stock
Look for "1611292138" in the first slot of each sub-array

edited Dec 5, 2016 at 22:01

answered Dec 5, 2016 at 21:56

code_monk

10.2k3 gold badges46 silver badges45 bronze badges

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

1 Comment

Vic Over a year ago

Thank you. Great explanation! Position is fixed so scanning 0 element is to my mind the best approach.Appreciate your help. Cheers.

Neo-coder · Accepted Answer · 2016-12-05 20:25:27Z

0

Use nested $elemMatch as below :

db.vlc.find({stock: { "$elemMatch":{"$elemMatch":{"$all":["1612050918"]}}}})

Or

 db.vlc.find({stock: {"$elemMatch":{ "$elemMatch":{"$in" : ["1612050918"]}}}})

answered Dec 5, 2016 at 20:25

Neo-coder

7,8585 gold badges36 silver badges54 bronze badges

1 Comment

Neo-coder Over a year ago

What should I do when someone answers my question?

Collectives™ on Stack Overflow

Using Mongo query to find an in array element

2 Answers 2

1 Comment

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Related