MongoDB：JS聚合查询转C#时处理缺失数组字段的问题

需求 #

基于长整型userid，统计MongoDB中siren集合里用户的三类数据：

• 拥有的警报数量
• 订阅的警报数量
• 负责的警报数量

Siren文档结构（仅展示相关字段） #

{
  "_id": ObjectId( "65fc94593273fc2ab6ff8960"),
  "ownerid": NumberLong( "99999999"), 
  "listener": [
    NumberLong( "11111111"),
    NumberLong( "00000000")
  ],
  "responsible": [
    NumberLong( "11111111")
  ]
}

可用的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 ] }}   
    }
  }
]);

问题 #

listener和responsible字段可能缺失，JS中可以用$isArray处理这种情况，但C#无法直接实现该逻辑，原代码执行时会报错。

原C#实现代码 #

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 => (_sirena.Responsible != null && _sirena.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;}
}

异常信息 #

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.'

修复后的C#方法 #

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;
}

补充说明 #

MongoDB.Driver 2.24的some_array is Array转换功能存在bug，在MongoDB v7.0.6中无法正常工作，需要修改表达式才能运行。

内容的提问来源于stack exchange，提问作者HedgehogNSK