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