I have a UserInfo collection and in that there is a field called UserThemes. I want to get the users if there are themes with values greater than 100.

This is the UserInfo collection:

public record UserInfo:BaseDocument
{
    public string UserName { get; set; }
    public BsonDocument UserThemes { get; init; } = new BsonDocument();
}

This is a sample of stored data:

{
    "_id" : ObjectId("62f8fe33127584b3e027060e"),
    "UserId" : "7BCBCC2DA9624BCB8191AC2DBDC5CA71",
    "UserName" : "kaveh",
    "UserThemes" : {
        "football" : 10,
        "basketball" : 110,
        "volleyball" : 90,
        "tennis" : 50,
        "golf" : 25
    }
}

So far I have tried this but it didn't work (I'm using MongoDB C#/.NET Driver):

var docs = await _context.UserInfos.Aggregate()
          .Match(
                new BsonDocument() {
                   { "$expr", new BsonDocument() {
                      { "$and", new BsonArray() {
                         new BsonDocument(){{ "$gt", new BsonArray() { "$UserThemes.value", BsonValue.Create(100) } } },
                        }
                      }
                    }
                  }
                })
          .As<UserInfo>()
          .ToListAsync();

CodePudding user response：

1. $match

1.1. $gt - Filter the document with array (from the result 1.1.1) size ($size) is greater than 0.

1.1.1. $filter - Filter the document from the input (converting the UserThemes field from key-value pair to the document array via $objectToArray) with v is greater than 100.

MongoDB query

db.collection.aggregate([
  {
    $match: {
      $expr: {
        $gt: [
          {
            $size: {
              $filter: {
                input: {
                  $objectToArray: "$UserThemes"
                },
                cond: {
                  $gt: [
                    "$$this.v",
                    100
                  ]
                }
              }
            }
          },
          0
        ]
      }
    }
  }
])