Nested queries don't work with cosmosdb-mongodb

Question

My document in cosmosdb looks like this

{
   "todayDate": "2017-12-08",
   "data": [
      {
        "group": {"priority": 1, "total": 10},
        "severity": 1
      },
      {
         "group": {"priority": 2, "total": 13},
         "priority": 2
      }
    ]
}

The following query when issued from either mongoShell for cosmosdb in azure portal or using my spring data mongodb project works fine and returns results in no time:

db.myCollection.find({ "$or" : [ { "data" : { "$elemMatch" : { "priority" : 1}} , "$or" : [ { "data" : { "$elemMatch" : { "group.priority" : 1}}}] }]})

However, the following query on the same lines with more OR conditions which basically is two of the above queries with OR operator, hangs indefinitely:

db.myCollection.find({ "$or": [ { "data" : { "$elemMatch" : { "priority" : 1}} , "$or" : [ { "data" : { "$elemMatch" : { "group.priority" : 1}}}] }, { "data" : { "$elemMatch" : { "severity" : 2}} , "$or" : [ { "data" : { "$elemMatch" : { "group.severity" : 2}}}] } ] })

Is there anything wrong with the last query that makes it hang indefinitely? Even if I replace initial OR with AND, still the same result i.e. hangs indefinitely.

I am testing it now and will share the results soon. Thank you — Hary, Dec 14 '17 at 16:37

Jay Gong · Answer 1 · 2017-12-15T02:53:07.513

I created 3 documents in my cosmos db according to the document template you provided.

[
  {
    "id": "1",
    "todayDate": "2017-12-08",
    "data": [
      {
        "group": {
          "severity": 1,
          "total": 10
        },
        "severity": 1
      },
      {
        "group": {
          "priority": 1,
          "total": 13
        },
        "priority": 1
      }
    ]
  },
  {
    "id": "2",
    "todayDate": "2017-12-09",
    "data": [
      {
        "group": {
          "priority": 3,
          "total": 10
        },
        "severity": 1
      },
      {
        "group": {
          "priority": 3,
          "total": 13
        },
        "priority": 1
      }
    ]
  },
  {
    "id": "3",
    "todayDate": "2017-12-10",
    "data": [
      {
        "group": {
          "priority": 1,
          "total": 10
        },
        "severity": 1
      },
      {
        "group": {
          "priority": 2,
          "total": 13
        },
        "priority": 2
      }
    ]
  }
]

Then I use Robo 3T tool to execute your sql.

db.coll.find({ 
"$or": [ 
    { "data" : { "$elemMatch" : { "priority" : 1}} , 
    "$or" : [ 
        { "data" : { "$elemMatch" : { "group.priority" : 1}}}
    ] }, 
    { "data" : { "$elemMatch" : { "severity" : 2}} , 
    "$or" : [ 
        { "data" : { "$elemMatch" : { "group.severity" : 2}}}
    ] } 
]  
})

result:

The syntax of the $or that I found on the official document is:

{ $or: [ { <expression1> }, { <expression2> }, ... , { <expressionN> } ] }

It seems that your SQL can be executed normally though it is different from the above syntax. Per my experience, $or is generally used to be nested with $and (MongoDB Nested OR/AND Where?) ,so I do not quite understand what is the purpose of your $or nested here.

Surely, an indefinite hang is probably because the data is too large so that SQL runs too long and you need to optimize your SQL.

Hope it helps you.Any concern ,please let me know.

Update Answer:

I have properly modified my 3 sample documents then query 2 eligible documents via the SQL you provided.

SQL：

db.coll.find(
{
    "$and": [
        {
            "$or": [
                {
                    "data": {
                        "$elemMatch": {
                            "priority": 2
                        }
                    }
                },
                {
                    "data": {
                        "$elemMatch": {
                            "group.priority": 2
                        }
                    }
                }
            ]
        },
        {
            "$or": [
                {
                    "data": {
                        "$elemMatch": {
                            "severity": 1
                        }
                    }
                },
                {
                    "data": {
                        "$elemMatch": {
                            "group.severity": 1
                        }
                    }
                }
            ]
        }
    ]
}
)

Results:

So , I think your SQL is correct. Is the data in the database very large? If you've been hanging for a long time, did you have seen timeout error messages? Or you could check RUs setting's issue.

I followed the link that you provided and created a combination or AND/OR as that is what I really need. { "$and" : [ { "$or" : [ { "data" : { "$elemMatch" : { "priority" : 1}}} , { "data" : { "$elemMatch" : { "group.priority" : 1}}}]} , { "$or" : [ { "data" : { "$elemMatch" : { "severity" : 2}}} , { "data" : { "$elemMatch" : { "group.severity" : 2}}}]}]} hangs indefinitely. I have only one document in cosmos db that satisfied this criteria. Individual OR queries work fine. Is there anything wrong I am doing with the combination of AND/OR ? — Hary, Dec 14 '17 at 18:08
Don't have a lot of documents that would match the query criteria. Throughout setting is 1000 RU/S the error that I get after hanging for long around 3 -4 minutes is "server experienced an unexpected an error" — Hary, Dec 15 '17 at 04:32
@Hary The performance of SQL execution depend on the amount of data that needs to be traversed altogether, instead of the amount of data that match the query criteria. Could you try to create a new collection, just put the 3 sample doc above to see if the SQL works? — Jay Gong, Dec 15 '17 at 06:28
Makes sense. But is there a reason as to why the individual OR queries work fine and an AND query on 2 OR queries hang ? Both AND and OR scan the same amount of data anyway. Is there something mysterious about how and queries work in cosmos ? — Hary, Dec 15 '17 at 14:13
I also added a new collection with just 2 documents only. OR query worked and AND hung — Hary, Dec 15 '17 at 14:28
@Hary It's weird.I searched a lot of documents and didn't find a similar issue as yours. I notice this SO thread :https://stackoverflow.com/questions/27083501/documentdb-call-hangs . SDK version may be the reason. — Jay Gong, Dec 20 '17 at 09:23
I think it's something to do with the indexing. The query worked for me when I shrunk the size of data list from 21 to 10 elements in it. As of now, I am relying on default indexing by cosmos. Do you think I should be indexing on "group" under "data" only and somehow remove rest of the indices ? — Hary, Dec 21 '17 at 16:19
@Hary Hi,Hary. I think it is not index issue. I am also the default index, But I did not reproduce your mistake. Only 2 of the documents in your database however it's still hangs. I didn't find the difference between us. I suggest you try using the robo3t tool in my answer to test, or rebuild a cosmos DB account. If it still hangs, please submit your feedback to Azure support : https://feedback.azure.com/forums/34192--general-feedback — Jay Gong, Dec 22 '17 at 02:21

Nested queries don't work with cosmosdb-mongodb

1 Answers1