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 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
mannu2050Microsoft
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.mediaTrainedrefer 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
- Hi Azure Cosmos Db Team, Good morning. Was following up on this query? With Regards, Nitin Rahim
- JillArmourMicrosoft
Community Manager
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
