Forum Discussion
Count without non correlated subquery in Cosmos Db
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 ret...
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
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
mannu2050Microsoft
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.