[DOCS-5995] $in operation on an indexed array field along with $ projection is failing in mongodb 2.6 Created: 07/Aug/15  Updated: 07/Apr/23  Resolved: 11/Aug/15

Status: Closed
Project: Documentation
Component/s: drivers
Affects Version/s: mongodb-2.6
Fix Version/s: 01112017-cleanup

Type: Bug Priority: Major - P3
Reporter: Sulbigar Shanawaz Assignee: Kay Kim (Inactive)
Resolution: Duplicate Votes: 0
Labels: $, $in, array, fields
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Attachments: File sample_document.query     File sample_document_2.query    
Participants:
Days since reply: 8 years, 27 weeks, 1 day ago

 Description   

I have the following data structure which has an indexed field as an array of strings.

<QUERY IN ATTACHMENT>

The below query was working fine in mongodb 2.4.

{ "docKey" : { "$in" : ["M06001XXXX45378CED", "M06001ZZZ45378CED"] }, "serviceGroupCode.estimateCategories.code" : "SEC02" }, { "serviceGroupCode.estimateCategories.$" : 1 }

But after upgrading to mongo db 2.6, this query is failing.
The above query works fine only if the $in operation consists of the first array element. Otherwise, it results is projection failure.

I initially thought that the indexing would have failed for other array elements but they work fine when queried alone without the $ projection.

I could not find any documentation that touches impact to these operation. Hence it would be of great help if this issue can be looked upon and a resolution can be provided at the earliest.

Thank You,
Sulbigar S



 Comments   
Comment by Andre Spiegel [ 11/Aug/15 ]

Duplicate of SERVER-18500

Comment by Andre Spiegel [ 11/Aug/15 ]

Thanks kay.kim@10gen.com, but please stick to the truth

We have this covered here now and the issue is being tracked under SERVER-18500.

I'm closing this ticket so we can focus on the SERVER issue.

Comment by Kay Kim (Inactive) [ 11/Aug/15 ]

Hi Sulbigar – I haven't had much time to look into this since yesterday. I believe andre.spiegel@10gen.com is on-site to discuss and develop possible solutions, and I would defer to Andre. He's fantastic and is much more knowledgeable and helpful than I would be.

Regards,

Kay Kim

Comment by Sulbigar Shanawaz [ 11/Aug/15 ]

Hi Kay,

Hope you are having a great day.
Did you get a chance of validating in 2.4.8 version?
What are your thoughts?

Thank You,
Sulbigar S

Comment by Sulbigar Shanawaz [ 10/Aug/15 ]

On a side note, what other idea would you suggest/recommend to fetch the document as per the needs I had explained before. I cannot fetch the entire set of array (as every array element is pretty huge [I have provided just a sample] and I would not like to have them in app server memory) and filter them in the application layer. Aggregation will not happen either for the same reason of size restriction.

Comment by Sulbigar Shanawaz [ 10/Aug/15 ]

The version that I'm using is 2.4.8

Comment by Kay Kim (Inactive) [ 10/Aug/15 ]

And which version of 2.4 are you using? I was using 2.4.12

Comment by Sulbigar Shanawaz [ 10/Aug/15 ]

The array element on what I had queried for.

{
  "_id" : "1502134734",
  "serviceGroupCode" : {
    "estimateCategories" : [{
        "code" : "SEC03",
        "description" : "Market Estimates - 3",
        "networkCategories" : [{
            "code" : "INS",
            "likelyAmounts" : {
              "lengthOfstay" : null,
              "totalEstimate" : 1540
            },
            "minAmounts" : {
              "lengthOfstay" : null,
              "totalEstimate" : 1170
            },
            "maxAmounts" : {
              "lengthOfstay" : null,
              "totalEstimate" : 2886
            }
          }, {
            "code" : "INC",
            "likelyAmounts" : {
              "lengthOfstay" : null,
              "totalEstimate" : 1540
            },
            "minAmounts" : {
              "lengthOfstay" : null,
              "totalEstimate" : 1170
            },
            "maxAmounts" : {
              "lengthOfstay" : null,
              "totalEstimate" : 2886
            }
          }, {
            "code" : "INR",
            "likelyAmounts" : {
              "lengthOfstay" : null,
              "totalEstimate" : 1540
            },
            "minAmounts" : {
              "lengthOfstay" : null,
              "totalEstimate" : 1170
            },
            "maxAmounts" : {
              "lengthOfstay" : null,
              "totalEstimate" : 2886
            }
          }],
        "benefitTypeCode" : "BT04",
        "estimateConfidenceCode" : "SECF03"
      }]
  }
}

Comment by Kay Kim (Inactive) [ 10/Aug/15 ]

Hi Sulbigar –
could you let me know what is returned from your find operation?

Comment by Sulbigar Shanawaz [ 10/Aug/15 ]

Thank You Kay for your response.

However, I do not exactly see the same behavior as what you have mentioned with respect to mongo 2.4.

I have attached another document with two array elements. One with code SEC02 and another with SEC03. With 2.4, we get the appropriate array field projected as long as the $ operation is applied on the same array field on which the query is done (irrespective of the presence of query on another array field).

This is the query I tried

db.foo.find({ "docKey" : { "$in" : ["M06001AAAA45378CED", "M06001CCC45378CED"] }, "serviceGroupCode.estimateCategories.code" : "SEC03" }, { "serviceGroupCode.estimateCategories.$" : 1 })

and the result is not equivalent to

db.foo.find( { "docKey" : { "$in" : ["M06001AAAA45378CED", "M06001CCC45378CED"] }, "serviceGroupCode.estimateCategories.code" : "SEC02" }, { "serviceGroupCode.estimateCategories" : 1 })

But with 2.6, it does not work the same way.

Can you please check and let me know your thoughts? Something seems to have been modified with respect to this operation which has broken this logic.

Thank You,
Sulbigar S

Comment by Kay Kim (Inactive) [ 10/Aug/15 ]

Hi Sulbigar –
So, in both 2.4, 2.6, etc, to use the projection "$" positional operator, the query cannot include multiple arrays in the conditions
http://docs.mongodb.org/v2.4/reference/operator/projection/positional/#array-field-limitations

Your query condition includes conditions on 2 array fields: docKey field and serviceGroupCode fields. In 2.4, with the incorrect usage, the query + projection seems to return the serviceGroupCode array in its entirety instead of the element that just has code: "SEC02" (i.e. seems to ignore the "$" in the project):

Is this what you generally see?

db.foo.find( { "docKey" : { "$in" : ["M06001XXXX45378CED", "M06001ZZZ45378CED"] }, "serviceGroupCode.estimateCategories.code" : "SEC02" }, { "serviceGroupCode.estimateCategories.$" : 1 })
{ "_id" : "1502134733", "serviceGroupCode" : { "estimateCategories" : [ 	{ 	"code" : "SEC02", 	"description" : "Market Estimates", 	"networkCategories" : [ 	{ 	"code" : "INS", 	"likelyAmounts" : { 	"lengthOfstay" : null, 	"totalEstimate" : 1540 }, 	"minAmounts" : { 	"lengthOfstay" : null, 	"totalEstimate" : 1170 }, 	"maxAmounts" : { 	"lengthOfstay" : null, 	"totalEstimate" : 2886 } }, 	{ 	"code" : "INC", 	"likelyAmounts" : { 	"lengthOfstay" : null, 	"totalEstimate" : 1540 }, 	"minAmounts" : { 	"lengthOfstay" : null, 	"totalEstimate" : 1170 }, 	"maxAmounts" : { 	"lengthOfstay" : null, 	"totalEstimate" : 2886 } }, 	{ 	"code" : "INR", 	"likelyAmounts" : { 	"lengthOfstay" : null, 	"totalEstimate" : 1540 }, 	"minAmounts" : { 	"lengthOfstay" : null, 	"totalEstimate" : 1170 }, 	"maxAmounts" : { 	"lengthOfstay" : null, 	"totalEstimate" : 2886 } } ], 	"benefitTypeCode" : "BT04", 	"estimateConfidenceCode" : "SECF03" } ] } }

This is equivalent to:

db.foo.find( { "docKey" : { "$in" : ["M06001XXXX45378CED", "M06001ZZZ45378CED"] }, "serviceGroupCode.estimateCategories.code" : "SEC02" }, { "serviceGroupCode.estimateCategories" : 1 })

In 2.6, if you use "$" projection operator but specify query conditions on 2 array fields, the result does not assume you meant "serviceGroupCode.estimateCategories" : 1. As such, you'll need to either fix the query condition such that you no longer specify conditions on multiple arrays or update your projection document.

Hope this helps.

Kay Kim

Comment by Sulbigar Shanawaz [ 10/Aug/15 ]

Hi,

Greetings!!!
It would be highly appreciated if someone can confirm whether this was a miss with the upgrade?
So far, I'm unable to find another query that would be able to achieve the same behavior as the one that I had reported.

Thank You,
Sulbigar S

Generated at Thu Feb 08 07:51:25 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.