skip to Main Content

I have a collection which stores information about sirens – notifications. I need to get summarized user statistic by (long) userid:

  1. A count of owned sirens;
  2. A count of subscriptions;
  3. 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


  1. Chosen as BEST ANSWER

    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:

    1. Null check is not the same as Missing field check.
    2. MongoDB.Driver.Mql - static class that provides tools for checking missing fields

    As a result we can check the missing field like this:

    Responsible = _sirens.Sum(_sirena => (Mql.Exists(_sirena.Responsible) 
                       && _sirena.Responsible.Contains(userId)) ? 1 : 0)
    

    or

    sirens.AsQueryable().Where(x=> Mql.Exists(x.Responsible));
    

    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


  2. As your Listener and Responsible fields are possibly missing, you can provide a default value to the field with:

    (_siren.Listener ?? new long[] { })
    

    Thus, when transforming to a MongoDB query, it is equivalent to:

    { "$ifNull" : ["$responsible", []] }
    

    Complete query:

    var query = sirens.AsQueryable<SirenRepresentation>()
        .Where(_siren => _siren.OwnerId == userId
            || (_siren.Listener != null 
                && (_siren.Listener ?? new long[] { }).Any(x => x == userId))
            || (_siren.Responsible != null 
                && (_siren.Responsible ?? new long[] { }).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 ?? new long[] { }).Contains(userId)) ? 1 : 0),
            Responsible = g.Sum(_siren => (_siren.Responsible != null 
                && (_siren.Responsible ?? new long[] { }).Contains(userId)) ? 1 : 0)
        });
    
    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search