CODEWITHSUNDEEP
azureazure-cosmosdb
Scott Lin
Scott Lin

Reputation: 1562

Azure DocumentDB - Query returning no results

Is the below query supported in Azure DocumentDB? It returns no documents.

Variables values at runtime:
  1. collectionLink = "<link for my collection>"
  2. feedOptions = new FeedOptions { MaxItemCount = 2 }
  3. name = "chris"

client.CreateDocumentQuery<T>(collectionLink, feedOptions).Where(m => (m.Status == "Foo" && (m.Black.Name == null || m.Black.Name != name) && (m.White.Name == null || m.White.Name != name)));

I have tested with simpler queries, such as the below, which both return results I expect.

client.CreateDocumentQuery<T>(collectionLink, feedOptions).Where(m => m.Status == "Foo");

client.CreateDocumentQuery<T>(collectionLink, feedOptions).Where(m => m.Status == "Foo").Where(m => m.Size == 19);

Lastly, I've ensured there are documents which meet the problematic query's filter criteria:

{
  "id": "1992db52-c9c6-4496-aaaa-f8cb83a8c6b0",
  "status": "Foo",
  "size": 19,
  "black": {
    "name": "charlie"
  },
  "white": {},
}

Thanks.

Upvotes: 2

Views: 3117

Answers (2)

Aravind Krishna R.
Aravind Krishna R.

Reputation: 8003

The query can be written to handle missing properties using DocumentDB UDFs as follows. DocumentDB uses JavaScript's semantics, and an explicit null is different from a missing property ("undefined") in JavaScript. To check for explicit null is simple (== null like your query), but to query for a field that may or may not exist in DocumentDB, you must first create a UDF for ISDEFINED:

function ISDEFINED(doc, prop) {
    return doc[prop] !== undefined;   
}

And then use it in a DocumentDB query like:

client.CreateDocumentQuery<T>(
    collectionLink, 
   "SELECT * FROM docs m WHERE m.Status == "Foo" AND (ISDEFINED(m.white, "name") OR m.white.name != name)");

Hope this helps. Note that since != and UDFs both require scans, it's a good idea for performance/scale to always use them only within queries that have other filters.

Upvotes: 1

Scott Lin
Scott Lin

Reputation: 1562

Turns out the "m.White.Name == null || m.White.Name != name" check is problematic because the Name field does not exist on the document in the DB.

When the document is edited to the following, the query returns it. Notice the explicit null value for Name field.

{
  "id": "1992db52-c9c6-4496-aaaa-f8cb83a8c6b0",
  "status": "Foo",
  "size": 19,
  "black": {
    "name": "charlie"
  },
  "white": {
    "name": null
  },
}

Upvotes: 1

Related Questions