Get User Details along with login count as per dates in Cosmos DB

Question

Data store in UserActivity Document,

A)User Data

User along with Date store in document only once per day with Type User:

{
"id":"R6788F9DS8DF099K90K0977K", //Unique Id
"UserId":1, 
"LoginDate":"2022-04-02T12:41:45.567",
"UserName":"Foo",
"Type":"User"
}

B)LoginAttempt

User login Attept data store in Document with Type LoginAttemt as many times user attempt login

{
"id":"CP6788F9DS8DF099K90K056U", //Unique Id
"UserId":1, //user id for logged in attempted by user
"LoginDate":"2022-04-02T12:41:45.567",
"Type":"LoginAttempt"
},
{
"id":"CP6788F9DS8DF099K90K056U", //Unique Id
"UserId":1, //user id for logged in attempted by user
"LoginDate":"2022-04-02T01:50:20.142",
"Type":"LoginAttempt"
}

I want to achieve below query in Azure Cosmos DB

Select c,
UserName, 
LoginDate,
(select count(id) from UserActivity u where u.Type='LoginAttemt' and u.UserId=u1.UserId and u.LoginDate=u1.LoginDate ) LoginCount
from UserActivity u1 where u1.Type='User' and u1.LoginDate='02-04-2022'

but LoginCount is always 0 when I try above query in Cosmos DB.

expected Output will be:

[{
"UserName":"Foo",
"LoginDate":"02-04-2022",
"LoginCount":"2"
}]

above result show user details and logged in count 2 because User Foo Attempted 2 login on date 02-04-2022

and also my Doubt is : In MSSQL we can pass Outer row details to inner query and inner query process based on received inputs from outer query and return data is it possible in cosmos db

Answer 1

The query you're trying to achieve won't work. You are trying to avoid joining different documents with eachother through other means, but that's not something Cosmos supports.

The simple solution is to just cut the query into two queries; one for counting the login attempts and one for retrieving the user information. It's likely also the most efficiënt in terms of RU usage and easiest to interpret for other developers working with your code.

---

Also to give some insights why your query doesn't work. If you want to filter for a specific date you need to check between values c.date >= '2022-01-01' AND c.date < '2022-01-02' since you're working with date AND time (and it's actually a string comparison).

In your outer WHERE clause you filter c.Type = 'User'. That means that other types of documents won't ever appear in your inner statements.

You can't use COUNT in a subquery to count documents as you're within the scope of a single document inside the subquery. A subquery with COUNT can be useful when using self-joins, but not in this context.