Count without non correlated subquery in Cosmos Db

Brass Contributor

Hi Team,

 

Is there a way I can get the count of all records returned along with attributes in a cosmos db no sql query? Right now we have to run 2 queries first to return the count and then to return an attribute/s?Since window functions is not supported now and also group by won't help in this requirement can you please advise?The total count can repeat based on the query results.If there are 5 results the total count will remain the same in the 5 results. This is needed to minimize 2 cross partition queries for a request.

 

SELECT count(1) totcount FROM container r where
r.x ='1'
and
r.y = '2'
and
r.z = '3'

 

SELECT r.id, r.pk,r.ts FROM container r where
r.x ='1'
and
r.y = '2'
and
r.z = '3'

 

I am looking to acheive below

SELECT count(*) tot count,r.id, r.pk,r.ts FROM container r where
r.x ='1'
and
r.y = '2'
and
r.z = '3'

 

With Regards,

Nitin Rahim

6 Replies
Hi Azure Cosmos Db Team, Good morning. Was following up on this query? With Regards, Nitin Rahim
You may have better luck posting this in the Partner-Led tech topics discussion board. I'm moving there in hopes someone can chime in. If not we can move back to the Azure Cosmo's board.

Fingers crossed someone has some experience with this. -jill

@nitinrahim you can use simple group by statements to achieve the same. Something like this,

SELECT
COUNT(1) AS employeesWithThisTraining,
e.capabilities.softwareDevelopment AS developmentLang,
e.capabilities.mediaTrained AS mediaReady
FROM
employees e
GROUP BY
e.capabilities.softwareDevelopment,
e.capabilities.mediaTrained

 

refer for more details https://learn.microsoft.com/en-us/azure/cosmos-db/nosql/query/group-by

 

Hope this helps! 

 

Hi Manish,

I need to include all the fields from employees e along with the total count of records in employees, not just by the grouped by fields.

Basically something like

select count(1), e. * from employees e where e.name ='x' and e.age ='y'

In normal sql we can achieve the same using subqueries, was checking for this in nosql cosmos db as non correlated subqueries are not supported. Can use composite index on name and age to improve performance but is there a way to achieve count in the same query?

With Regards,
Nitin Rahim


Hi Manish,

This use case is for returning Customer the number of results first and the actual results with pagination so 2 cross partition individual queries can be eliminated.

With Regards,
Nitin Rahim
In order to get the count and show the results, you need to perform queries executed twice. However, for simple pagination, you can use continuation token for which you don't need to know the total count in advanced, you can perform something like this
using (FeedIterator<Family> query = container.GetItemQueryIterator<Family>(
queryText,
requestOptions: options))
{
while (query.HasMoreResults)
{
foreach (Family family in await query.ReadNextAsync())
{
familiesSerial.Add(family);
}
}
}
This loop will continue till the results are getting populated otherwise it will get terminated, in Java you can keep checking for continuationtoken not being null to check if you still have results flowing in.
Pagination is briefly documented at https://learn.microsoft.com/en-us/azure/cosmos-db/nosql/query/pagination.