I have a collection which stores information about sirens – notifications. I need to get summarized user statistic by (long) userid:
- A count of owned sirens;
- A count of subscriptions;
- A count of sirens that the user is responsible for
Siren document structure (necessary fields for question only):
{
"_id": ObjectId( "65fc94593273fc2ab6ff8960"),
"ownerid": NumberLong( "99999999"),
"listener": [
NumberLong( "11111111"),
NumberLong( "00000000")
],
"responsible": [
NumberLong( "11111111")
]
}
After 2 days of struggling I’ve made a working request via JS
db.sirens.aggregate([
{
"$match": {
"$or": [
{ "ownerid": userId },
{ "listener": userId },
{ "responsible": userId }
]}},
{
"$group": {
"_id": null,
"owner": { "$sum": { "$cond": [{ "$eq": ["$ownerid", userId ] }, 1, 0] }},
"responsible": { "$sum": { "$cond": [{ "$and": [
{ "$ne": ["$responsible", null] },
{ "$isArray": "$responsible" },
{ "$in": [userId, "$responsible"] }
] }, 1,0 ] }}
,"listener": { "$sum": { "$cond": [{ "$and": [
{ "$ne": ["$listener", null] },
{ "$isArray": "$listener" },
{ "$in": [userId, "$listener"] }
] }, 1,0 ] }}
}
}
]);
But here is a concern that I can’t solve. fields "listener" and "responsible" may be missing. And in JS this condition:{ "$isArray": "$responsible" },
handles the situation. But I can’t do the same in C#.
Here is the code I’ve got so far:
var query = sirens.AsQueryable<SirenRepresentation>()
.Where(_sirena => _sirena.OwnerId == userId
|| (_sirena.Listener != null && _sirena.Listener.Any(x => x == userId))
|| (_sirena.Responsible != null && _sirena.Responsible.Any(x => x == userId)))
.GroupBy(s => true)
.Select(g => new UserStatistics
{
SirenasCount = g.Sum(_siren => _siren.OwnerId == userId ? 1 : 0),
Subscriptions = g.Sum(_siren => (_siren.Listener != null && _siren.Listener.Contains(userId)) ? 1 : 0),
Responsible = g.Sum(_siren => (_siren.Responsible != null && _siren.Responsible.Contains(userId)) ? 1 : 0)
});
public class SirenRepresentation
{
[BsonId]
public ObjectId Id { get; set; }
[BsonElement("ownerid"), BsonRepresentation(BsonType.Int64)]
public long OwnerId { get; set; }
[BsonRepresentation(BsonType.Int64)]
[BsonElement("listener")]
public long[] Listener { get; set; } = [];
[BsonRepresentation(BsonType.Int64)]
[BsonElement("responsible")]
public long[] Responsible { get; set; } = [];
[BsonElement("requests")]
//...
}
public class UserStatistics{
public int SirenasCount{get;set;}
public int Subscriptions{get;set;}
public int Responsible{get;set;}
}
When one of the fields is missing an exception is caught:
Exception has occurred: CLR/MongoDB.Driver.MongoCommandException An
exception of type ‘MongoDB.Driver.MongoCommandException’ occurred in
System.Private.CoreLib.dll but was not handled in user code: ‘Command
aggregate failed: PlanExecutor error during aggregation :: caused by
:: $in requires an array as a second argument, found: missing.’
Update. Fixed method:
public async Task<UserStatistics> Get(long userId)
{
var query = sirens.AsQueryable()
.Where(_sirena => _sirena.OwnerId == userId
|| _sirena.Listener.Any(x => x == userId)
|| _sirena.Responsible.Any(x => x == userId))
.GroupBy(keySelector: x => true,
resultSelector: (_, _sirens) => new UserStatistics
{
SirenasCount = _sirens.Sum(x => x.OwnerId == userId ? 1 : 0),
Subscriptions = _sirens.Sum(_sirena => (_sirena.Listener ?? new long[] { }).Contains(userId) ? 1 : 0),
Responsible = _sirens.Sum(_sirena => (_sirena.Responsible ?? new long[] { }).Contains(userId) ? 1 : 0)
})
.FirstOrDefaultAsync();
return await query;
}
Big thanks to Yong Shun <3
P.S. MongoDB.Driver 2.24 also provides behaviour for translation some_array is Array
, but unfortunately it is bugged. It translates is Array
to expression:
"$cond": {
"if": {
"$and": [{
"$or": [{
"$eq": ["$some_array._t", "Array"]
}, {
"$and": [{
"$isArray": "$some_array._t"
}, {
"$in": ["Array", "$some_array._t"]
}
...
And in my case for MongoDB v.7.0.6 it doesn’t work properly. But if we change "$eq": ["$some_array._t", "Array"]
-> {"$isArray": "$some_array"}
then it will work
2
Answers
I've sent a bugreport to the MongoDB team. And a member of their team helped me a lot with understanding of converting C# language constructs to a json query string. Special thanks to Boris Dogadov.
Long story short:
As a result we can check the missing field like this:
or
Be aware that Mql methods are applicable only for LINQ queries. Also there is a detailed answer to another user about subtles of LINQ processing
As your
Listener
andResponsible
fields are possibly missing, you can provide a default value to the field with:Thus, when transforming to a MongoDB query, it is equivalent to:
Complete query: