COUNT unique values with DISTINCT Subquery Expected Result in Data Explorer but not when using Rest API

Question

Hello,

I have a question regarding varying results from running a query via REST API vs Data Explorer on the container directly.

Lets say I have a Cosmos DB container with the following items.

[
  {
    id: '1',
    position: 'Table flipper'
    city: 'Vancouver'
    type: 'Job'
  },
  {
    id: '2',
    position: 'Table painter'
    city: 'Seattle'
    type: 'Job'
  },  
  {
    id: '3',
    position: 'Table flipper'
    city: 'Calgary'
    type: 'NotJob'
  },
  {
    id: '4',
    position: 'Table maker'
    city: 'Toronto'
    type: 'Job'
  },
  {
    id: '5',
    position: 'Table maker'
    city: 'Vancouver'
    type: 'Job'
  },
  {
    id: '6',
    position: 'Table flipper'
    city: 'Vancouver'
    type: 'Job'
  },
]

What I want is the a count of the unique cities given a type.

The query I am running and tried is the following

SELECT VALUE COUNT(subCollection.city) FROM (
  SELECT DISTINCT (c.city) FROM c
  WHERE c.type = 'Job'
) as subCollection

I expect the result to equal the unique number of times a specific city shows up. So in this example the resource from Cosmos should be [3].

Running this query in Azure Portal's Data Explorer the result is [3] as expected. When I run the same query using the REST API, the result is [0]. The actual resource returned from the API is [0, 3].

How I'm using the API:

  let result = await cosmosDb.container(containerName)
    .items.query({
      query: query,
    })
    .fetchAll()
  return result.resources[0] // returns 0 and the result is [0, 3]

I've tried a few combinations of COUNT / DISTINCT but I get the same discrepancy between API and Data Explorer.

Any ideas what I am doing wrong and why results are different from the REST API vs Data Explorer?

Thanks in advance.

Answer 1

It can happen as a result of pagination in REST API. You need to handle pagination correctly to retrieve all the results. Here's an example of how you can modify your code to handle pagination:

let result = await cosmosDb.container(containerName)
  .items.query({
    query: query,
  })
  .fetchAll();

let totalCount = 0;
for (const page of result.byPage()) {
  const response = await page.fetchNext();
  const resources = response.resources;
  totalCount += resources.length;
}

return totalCount;