Querying sub array with $where

Question

I have a collection with following document:

{
"_id" : ObjectId("51f1fd2b8188d3117c6da352"),
"cust_id" : "abc1234",
"ord_date" : ISODate("2012-10-03T18:30:00Z"),
"status" : "A",
"price" : 27,
"items" : [{
  "sku" : "mmm",
  "qty" : 5,
  "price" : 2.5
}, {
  "sku" : "nnn",
  "qty" : 5,
  "price" : 2.5
}]
}

I want to use "$where" in the fields of "items", so something like this:

{$where:"this.items.sku==mmm"}

How can I do it? It works when the field is not of array type.

score 2 · Answer 1 · answered Jul 27 '13 at 04:20

2

You don't need a $where operator to do this; just use a query object of:

{ "items.sku": mmm }

As for why your $where isn't working, the value of that operator is executed as JavaScript, so that's not going to check each element of the items array, it's just going to treat items as a normal object and compare its sku property (which is undefined) to mmm.

answered Jul 27 '13 at 04:20

JohnnyHK

305,182
66
621
471

I have a situation where I have to use both, and my service is just a interface between user and mongodb, totally unaware what the field client want's to store, so if the client passes a key like "items.sku==mmm", it should work, I know I can use simple queries, but is their a workaround with $where – Phalguni Jul 27 '13 at 04:26
So in that case can a "for loop" be added with $where clause? – Phalguni Jul 27 '13 at 04:34

Philipp · Accepted Answer · 2013-07-27T10:53:04.077

You are comparing this.items.sku to a variable mmm, which isn't initialized and thus has the value unefined. What you want to do, is iterate the array and compare each entry to the string 'mmm'. This example does this by using the array method some which returns true, when the passed function returns true for at least one of the entries:

{$where:"return this.items.some(function(entry){return entry.sku =='mmm'})"}

But really, don't do this. In a comment to the answer by JohnnyHK you said "my service is just a interface between user and mongodb, totally unaware what the field client want's to store". You aren't really explaining your use-case, but I am sure you can solve this better.

The $where operator invokes the Javascript engine even though this trivial expression could be done with a normal query. This means unnecessary performance overhead.
Every single document in the collection is passed to the function, so when you have an index, it can not be used.
When the javascript function is generated from something provided by the client, you must be careful to sanetize and escape it properly, or your application gets vulnerable to code injection.

@Phalguni An earlier version of this answer contained an incorrect javascript function. It is now fixed. — Philipp, Jul 27 '13 at 05:47
I executed the following query:db.runCommand({ mapreduce: "orders", map : function Map() { emit(this._id,this); }, reduce : function Reduce(key, values) { return values; }, finalize : function Finalize(key, reduced) { return reduced; }, query : { "$where" : "return this.items.some(function(entry){entry.sku =='mmm'})" }, out : { merge: "map_reduce" } });, but it doesn't work — Phalguni, Jul 27 '13 at 09:36
I got the problem: the query should be as follows{$where:"return this.items.some(function(entry){return entry.sku =='mmm'})"} ,inner function was missing return statement — Phalguni, Jul 27 '13 at 10:34

score 0 · Answer 3 · answered Jul 27 '13 at 19:48

I've been reading through your comments in addition to the question. It sounds like your users can generically add some attributes, which you are storing in an array within a document. Your client needs to be able to query an arbitrary pair from the document in a generic manner. The pattern to achieve this is typically as follows:

 {
    .
    .
    attributes:[
     {k:"some user defined key",
      v:"the value"},
     {k: ,v:}
      .
      .
    ]
 }

Note that in your case, items is attributes. Now to get the document, your query will be something like:

eg)
db.collection.find({attributes:{$elemMatch:{k:"sku",v:"mmm"}}});
(index attributes.k, attributes.v)

This allows your service to provide a way to query the data, and letting the client specify what the k,v pairs are. The one caveat with this design is always be aware that documents have a 16MB limit (unless you have a use case that makes GridFS appropriate). There are functions like $slice which may help with controlling this.

Dyalan you guessed right, but in case the user wants to query for something like this as well "items.qty>5", this won't work in that case I think its better to go with "$where" as @philip told. — Phalguni, Jul 28 '13 at 04:27
You can do this query. An example is: db.col.find({attributes:{$elemMatch:{k0:"sku",v0:"mmm",k1:"qty",v1:{$gt:5}}}}). The index may look like db.col.ensureIndex({"attributes.k0":1,"attributes.v0":1,"attributes.k1":1,"attributes.v1":1,"attributes.k2":1,"attributes.v2":1}); — Dylan Tong, Jul 28 '13 at 06:27

Querying sub array with $where

3 Answers3