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

Question

Lets say I have a CosmosDB 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]. 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

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

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

Answer 1

• I have used runQuery function which encapsulates the logic for executing the query against COSMOS DB.

• And a new instance of CosmosClient is created. Then, a reference to the desired container within the specified database is obtained using the database() and container() methods.

• Using the query() method, the container is queried, and the fetchAll() function is used to retrieve every result.

• By using the runQuery() function, then() block outcome is result. Any errors are captured in the catch() block.

Here is the code to get expected result as in Data Explorer using Rest API:

const { CosmosClient } = require("@azure/cosmos");
async  function  runQuery() {

// Replace with your Cosmos DB endpoint url
const  endpoint = "https://**********";

// Replace with your Cosmos DB primary key
const  key= "************";

const  containerName = "Items";

const  client = new  CosmosClient({ endpoint, key });

const  container = client.database("NewDb").container(containerName); //Replace with your DB name

const  query = `
SELECT VALUE COUNT(subCollection.city) FROM (
SELECT DISTINCT c.city FROM c WHERE c.type = 'Job'
) AS subCollection`;

const { resources } = await  container.items.query(query).fetchAll();
return  resources;
}

runQuery()

.then((result) => {
console.log(result);
})
.catch((error) => {
console.error("Error running the query:", error);
});

Items: Output: