queries in embedded arrays return wrong results??

47 views

Skip to first unread message

Alex

unread,

Oct 15, 2012, 2:31:19 PM10/15/12

to mongod...@googlegroups.com

Given the following collection and documents:

aggs:PRIMARY> db.version()

2.2.0

1. insert an embedded document first to test the waters...

aggs:PRIMARY> db.funwitharrays.insert({tl: {"47": ISODate("2012-06-02T21:00:00.001Z")}})

2. start pushing into array:

aggs:PRIMARY> db.funwitharrays.update({dummyfield:1}, {"$push": { "tl" : {"47": ISODate("2012-06-03T21:00:00.001Z")}} }, true, false)

3. did we insert correctly?

aggs:PRIMARY> db.funwitharrays.find()

{ "_id" : ObjectId("507c4e3d714107a16777ade4"), "tl" : { "47" : ISODate("2012-06-02T21:00:00.001Z") } }

{ "_id" : ObjectId("507c4e677a01a47885674b74"), "dummyfield" : 1, "tl" : [ { "47" : ISODate("2012-06-03T21:00:00.001Z") } ] }

4. keep on inserting, this time in the same date as the embedded document in step (1)

aggs:PRIMARY> db.funwitharrays.update({dummyfield:2}, {"$push": { "tl" : {"47": ISODate("2012-06-02T21:00:00.001Z")}} }, true, false)

5. great, they are there!

aggs:PRIMARY> db.funwitharrays.find()

{ "_id" : ObjectId("507c4e3d714107a16777ade4"), "tl" : { "47" : ISODate("2012-06-02T21:00:00.001Z") } }

{ "_id" : ObjectId("507c4e677a01a47885674b74"), "dummyfield" : 1, "tl" : [ { "47" : ISODate("2012-06-03T21:00:00.001Z") } ] }

{ "_id" : ObjectId("507c4e847a01a47885674b75"), "dummyfield" : 2, "tl" : [ { "47" : ISODate("2012-06-02T21:00:00.001Z") } ] }

6. keep on inserting...

aggs:PRIMARY> db.funwitharrays.update({dummyfield:3}, {"$push": { "tl" : {"47": ISODate("2012-06-04T21:00:00.001Z")}} }, true, false)

7. check with a find again...

aggs:PRIMARY> db.funwitharrays.find()

{ "_id" : ObjectId("507c4e3d714107a16777ade4"), "tl" : { "47" : ISODate("2012-06-02T21:00:00.001Z") } }

{ "_id" : ObjectId("507c4e677a01a47885674b74"), "dummyfield" : 1, "tl" : [ { "47" : ISODate("2012-06-03T21:00:00.001Z") } ] }

{ "_id" : ObjectId("507c4e847a01a47885674b75"), "dummyfield" : 2, "tl" : [ { "47" : ISODate("2012-06-02T21:00:00.001Z") } ] }

{ "_id" : ObjectId("507c4e927a01a47885674b76"), "dummyfield" : 3, "tl" : [ { "47" : ISODate("2012-06-04T21:00:00.001Z") } ] }

aggs:PRIMARY> db.funwitharrays.update({dummyfield:4}, {"$push": { "tl" : {"48": ISODate("2012-06-05T21:00:00.001Z")}} }, true, false)

8. some find()'s to see the results:

8.a. tl.47 and date between 06/02 - 06/03 CORRECT

aggs:PRIMARY> db.funwitharrays.find( { "tl.47" : { "$gte" : ISODate("2012-06-02T21:00:00.000Z") , "$lt" : ISODate("2012-06-03T20:59:59.000Z") }} )

{ "_id" : ObjectId("507c4e3d714107a16777ade4"), "tl" : { "47" : ISODate("2012-06-02T21:00:00.001Z") } }

{ "_id" : ObjectId("507c4e847a01a47885674b75"), "dummyfield" : 2, "tl" : [ { "47" : ISODate("2012-06-02T21:00:00.001Z") } ] }

8.b. tl.47 and date between 06/03-06/04 CORRECT

aggs:PRIMARY> db.funwitharrays.find( { "tl.47" : { "$gte" : ISODate("2012-06-03T21:00:00.000Z") , "$lt" : ISODate("2012-06-04T20:59:59.000Z") }} )

{ "_id" : ObjectId("507c4e677a01a47885674b74"), "dummyfield" : 1, "tl" : [ { "47" : ISODate("2012-06-03T21:00:00.001Z") } ] }

8.c. tl.47 and date between 06/04-06/05 CORRECT

aggs:PRIMARY> db.funwitharrays.find( { "tl.47" : { "$gte" : ISODate("2012-06-04T21:00:00.000Z") , "$lt" : ISODate("2012-06-05T20:59:59.000Z") }} )

{ "_id" : ObjectId("507c4e927a01a47885674b76"), "dummyfield" : 3, "tl" : [ { "47" : ISODate("2012-06-04T21:00:00.001Z") } ] }

8.d. tl.47 and date between 06/05-06/06 CORRECT, it's tl.48 for this date.

aggs:PRIMARY> db.funwitharrays.find( { "tl.47" : { "$gte" : ISODate("2012-06-05T21:00:00.000Z") , "$lt" : ISODate("2012-06-06T20:59:59.000Z") }} )

8.e. let's try with tl.48 CORRECT, returns the document

aggs:PRIMARY> db.funwitharrays.find( { "tl.48" : { "$gte" : ISODate("2012-06-05T21:00:00.000Z") , "$lt" : ISODate("2012-06-06T20:59:59.000Z") }} )

{ "_id" : ObjectId("507c53da7a01a47885674b78"), "dummyfield" : 4, "tl" : [ { "48" : ISODate("2012-06-05T21:00:00.001Z") } ] }

8.f. let's try with tl.48 and out of range date CORRECT doesn't return anything.

aggs:PRIMARY> db.funwitharrays.find( { "tl.48" : { "$gte" : ISODate("2012-06-06T21:00:00.000Z") , "$lt" : ISODate("2012-06-07T20:59:59.000Z") }} )

after one more update operation:

db.funwitharrays.update({dummyfield:4}, {"$push": { "tl" : {"48": ISODate("2012-06-12T21:00:00.001Z")}} }, true, false)

The final state is:

aggs:PRIMARY> db.funwitharrays.find()

{ "_id" : ObjectId("507c4e3d714107a16777ade4"), "tl" : { "47" : ISODate("2012-06-02T21:00:00.001Z") } }

{ "_id" : ObjectId("507c4e677a01a47885674b74"), "dummyfield" : 1, "tl" : [ { "47" : ISODate("2012-06-03T21:00:00.001Z") } ] }

{ "_id" : ObjectId("507c4e847a01a47885674b75"), "dummyfield" : 2, "tl" : [ { "47" : ISODate("2012-06-02T21:00:00.001Z") } ] }

{ "_id" : ObjectId("507c4e927a01a47885674b76"), "dummyfield" : 3, "tl" : [ { "47" : ISODate("2012-06-04T21:00:00.001Z") } ] }

{ "_id" : ObjectId("507c4ea67a01a47885674b77"), "dummyfield" : 4, "tl" : [ { "48" : ISODate("2012-06-05T21:00:00.001Z") }, { "48" : ISODate("2012-06-12T21:00:00.001Z") } ] }

and now I get:

9. query for tl.48 with INCORRECT date and I still get back the result WRONG

db.funwitharrays.find( { "tl.48" : { "$gte" : ISODate("2012-06-06T21:00:00.000Z") , "$lt" : ISODate("2012-06-07T20:59:59.000Z") }} )

{ "_id" : ObjectId("507c4ea67a01a47885674b77"), "dummyfield" : 4, "tl" : [ { "48" : ISODate("2012-06-05T21:00:00.001Z") }, { "48" : ISODate("2012-06-12T21:00:00.001Z") } ] }

tl;dr so essentially, I get different behavior with an array that has a single element vs. an array that has multiple documents and incorrect results in my query because of that.

p.s. I figured out that the correct way to get the correct result set is using $elemMatch but the question is why? What's wrong with this query that shows up in arrays with multiple documents and not with a single one?...

This collection is fresh, no indexes in there and just FYI the .validate({full:true}) result is:

aggs:PRIMARY> db.funwitharrays.validate({full:true})

{

"ns" : "VZW_STG_UCS_RUNTIME.funwitharrays",

"firstExtent" : "2:fe33000 ns:VZW_STG_UCS_RUNTIME.funwitharrays",

"lastExtent" : "2:fe33000 ns:VZW_STG_UCS_RUNTIME.funwitharrays",

"extentCount" : 1,

"extents" : [

{

"loc" : "2:fe33000",

"xnext" : "null",

"xprev" : "null",

"nsdiag" : "VZW_STG_UCS_RUNTIME.funwitharrays",

"size" : 4096,

"firstRecord" : "2:fe330b0",

"lastRecord" : "2:fe332c0"

}

"datasize" : 364,

"nrecords" : 5,

"lastExtentSize" : 4096,

"padding" : 1.004,

"firstExtentDetails" : {

"loc" : "2:fe33000",

"xnext" : "null",

"xprev" : "null",

"nsdiag" : "VZW_STG_UCS_RUNTIME.funwitharrays",

"size" : 4096,

"firstRecord" : "2:fe330b0",

"lastRecord" : "2:fe332c0"

"objectsFound" : 5,

"invalidObjects" : 0,

"bytesWithHeaders" : 444,

"bytesWithoutHeaders" : 364,

"deletedCount" : 4,

"deletedSize" : 3476,

"nIndexes" : 1,

"keysPerIndex" : {

"VZW_STG_UCS_RUNTIME.funwitharrays.$_id_" : 5

"valid" : true,

"errors" : [ ],

"ok" : 1

}

Also an issue in 2.1.0 .

Gianfranco

unread,

Oct 22, 2012, 6:30:53 AM10/22/12

to mongod...@googlegroups.com

As from discussed in Jira I'm posting this on Google Groups for anyone who might need further explanation.

Let's say you want to find documents which have an array element containing an object key matching a certain date range.

1

2

3

4

5

6

> db.funwitharrays.find()

{ "_id": ObjectId("507d3af5e7f94285f2725484"), "tl": { "47": ISODate("20120602T21:00:00.001Z") } }

{ "_id": ObjectId("507d3b01d7e5a5dfc7c58521"), "dummyfield": 1, "tl": [ { "47": ISODate("20120603T21:00:00.001Z") } ] }

{ "_id": ObjectId("507d3b06d7e5a5dfc7c58522"), "dummyfield": 2, "tl": [ { "47": ISODate("20120602T21:00:00.001Z") } ] }

{ "_id": ObjectId("507d3b0bd7e5a5dfc7c58523"), "dummyfield": 3, "tl": [ { "47": ISODate("20120604T21:00:00.001Z") } ] }

{ "_id": ObjectId("507d3b11d7e5a5dfc7c58524"), "dummyfield": 4, "tl": [ { "48": ISODate("20120605T21:00:00.001Z") }, { "48": ISODate("20120612T21:00:00.001Z") } ] }

1

2

> db.funwitharrays.find( { "tl.48" : { "$gte" : ISODate("2012-06-06T21:00:00.000Z") , "$lt" : ISODate("2012-06-07T20:59:59.000Z") }} )

 "_id": ObjectId("507d3b11d7e5a5dfc7c58524"), "dummyfield": 4, "tl": [   {   "48": ISODate("20120605T21:00:00.001Z") },   {   "48": ISODate("20120612T21:00:00.001Z") } ] }

The above correctly doesn't not match the document structure.

This will only match single values, probably something you don't expect for arrays – this is why $elemMatch is needed for range queries on an array.

1

2

3

4

> db.funwitharrays.find( { "tl" : { $elemMatch: { '48': { "$gte" : ISODate("2012-06-06T21:00:00.000Z") , "$lt" : ISODate("2012-06-07T20:59:59.000Z") }}}} )

No records

> db.funwitharrays.find( { "tl" : { $elemMatch: { '48': { "$gte" : ISODate("2012-06-06T21:00:00.000Z") , "$lt" : ISODate("2012-06-13T20:59:59.000Z") }}}} )

{ "_id": ObjectId("507d3b11d7e5a5dfc7c58524"), "dummyfield": 4, "tl": [   {   "48": ISODate("20120605T21:00:00.001Z") },   {   "48": ISODate("20120612T21:00:00.001Z") } ] }