3

I am trying to write a simple stored procedure which runs a SQL query and I want to restrict the results by using MaxItemCount property. Query produces 3 documents but I want to return only 1 document. I am forcing this restriction using MaxItemCount property but that doesn't seem to be working.

function uspGetDetailsByEmailId(EmailId) {
  var collection = getContext().getCollection();
  var collectionLink = collection.getSelfLink();
  var response = getContext().getResponse();
  var recordcount = 0;
  if (!EmailId) throw new Error("Please provide the EmailId for which     Details need to be retrieved");     

  retrieveDetails();     

  function retrieveDetails() {
    var requestOptions = {MaxItemCount: "1"};
    var dbquery = {query: "SELECT * FROM c"};
    var isAccepted = collection.queryDocuments(collectionLink, dbquery, requestOptions,function (err, results) {
      console.log(results.length)  
      response.setBody(results)
      if (err) throw err;                   
    });

    if (!isAccepted) {
      response.setBody(null);
    }
  }
}
Yaobin Then
  • 2,662
  • 1
  • 34
  • 54
Learner
  • 43
  • 5

1 Answers1

4

MaxItemCount is not a restriction on the amount of documents that the whole query will return.

Cosmos DB always returns paged data. This means that if you get back 100 documents then there is a good change that these documents came back from many requests that had the same or almost the same amount of documents each. Then they are added together and returned.

MaxItemCount is the maximum account of documents, a single trip to Cosmos DB will contain, but if you query data using the queryDocuments method then you will simply get the same amount of data but you will limit each trip to Cosmos DB to carrying a maximum of 1 document.

What you would do in this case is use a query iterator and escape the iterator's loop once you have the first document.

Looking more into this (mainly using the only documentation I can find https://azure.github.io/azure-cosmosdb-js-server) it looks like the js-server that the stored procedures are using is a different SDK all together.

After digging into this even more, I found out that there is not MaxItemCount property in the RequestOptions object (and even if there was it would be a integer). However there is a property called pageSize as you can see in line 554 of https://azure.github.io/azure-cosmosdb-js-server/DocDbWrapperScript.js.html.

After testing it, it looks like setting the pageSize to 1 will successfully limit your results to the desired amount.

Here is the updated code:

function uspGetDetailsByEmailId(EmailId) {
  var collection = getContext().getCollection();
  var collectionLink = collection.getSelfLink();
  var response = getContext().getResponse();
  var recordcount = 0;
  if (!EmailId) throw new Error("Please provide the EmailId for which     Details need to be retrieved");     

  retrieveDetails();     

  function retrieveDetails() {
    var requestOptions = { pageSize: 1};
    var dbquery = {query: "SELECT * FROM c"};
    var isAccepted = collection.queryDocuments(collectionLink, dbquery, requestOptions,function (err, results) {
      console.log(results.length)  
      response.setBody(results)
      if (err) throw err;                   
    });

    if (!isAccepted) {
      response.setBody(null);
    }
  }
}
Nick Chapsas
  • 6,872
  • 1
  • 20
  • 29
  • Yes Chapsas, you are absolutely correct. MaxItemCount doesn't do this job instead pageSize does. Sadly, documentation doesn't mention this directly but GitHub does. Thanks for you detailed response. – Learner Jan 13 '19 at 11:38
  • Hi Nick, going by the above explanation, I limited the MaxItemCount to 1 and then controlled the number of iterations in the loop for ReadNextAsync before exiting the loop. But if we say wanted to return 100 items, would this mean we're calling the api 100 times? I'm trying to figure out an efficient solution using SDK V3 which doesn't have a parameter available to set for pageSize. Any ideas? – OJB1 Aug 04 '21 at 21:13