MongoDB - Search on a field of type BsonDocument by their values

593 views Asked by At

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();
1

There are 1 answers

0
Yong Shun On BEST ANSWER
  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
        ]
      }
    }
  }
])

Demo @ MongoPlayground


MongoDB .NET Driver syntax

BsonDocument greaterThanCriteria = 
    new BsonDocument() {
        { "$gt", new BsonArray() {
            new BsonDocument() {
                { "$size", new BsonDocument() {
                    { "$filter", new BsonDocument() {
                        { "input", new BsonDocument()
                            {
                                { "$objectToArray", "$UserThemes"  }
                            }
                        },
                        { "cond", new BsonDocument()
                            {
                                { "$gt", new BsonArray() {
                                    "$$this.v", 100
                                    }
                                }
                            }
                        }
                      }
                    }
                  }
                }
              },
            0
          }
        }
    };

var docs = await _context.UserInfos.Aggregate()
    .Match(
        new BsonDocument() {
           { "$expr", new BsonDocument() {
                { "$and", new BsonArray() {
                      greaterThanCriteria
                   }
                }
             }
           }
        })
  .As<UserInfo>()
  .ToListAsync();

Demo

enter image description here