Using extended filters (by field inside field) in MongoDB

64 Views Asked by At

There's a list List<BsonDocument> with values like that:

{{ "_id" : 123, "IDD" : 123, "Sc" : { "Field1" : null, "Field2" : "some text|some text|"} }}
{{ "_id" : 124, "IDD" : 124, "Sc" : { "Field1" : { "fl" : "something" }, "Field2" : ""} }}
{{ "_id" : 125, "IDD" : 125, "Sc" : { "Field1" : { }, "Field2" : null} }}

It's was taken with simple filter like that:

var builder = Builders<BsonDocument>.Filter;
var filter = builder.Gt("IDD", 122);
var collection = database.GetCollection<BsonDocument>("coll").Find(filter).ToList();

I have 2 quite similar questions:

  1. How to get the element with this condition without the creation a class (i.e. just using BsonDocument):

    SELECT * FROM coll WHERE Sc.Field2 != "" && Sc.Field2 != null

Expected result:

{{ "_id" : 123, "IDD" : 123, "Sc" : { "Field1" : null, "Field2" : "some text|some text|"} }}
  1. How to get the element with this condition without the creation a class (i.e. just using BsonDocument):

    SELECT * FROM coll WHERE Sc.Field1 != {} && Sc.Field2 != null

Expected result:

{{ "_id" : 124, "IDD" : 124, "Sc" : { "Field1" : { "fl" : "something" }, "Field2" : ""} }}

.

.

. I tried construction like that and it doesn't work:

filter &= !builder.ElemMatch<BsonValue>("Sc", new BsonDocument { { "Field2", BsonNull.Value } })
1

There are 1 best solutions below

0
On

Found a solution. Somehow I've missed that syntax:

filter &= !builder.Eq($"Sc.Field1", ""); 
filter &= !builder.Eq($"Sc.Field1", BsonNull.Value); 
filter &= !builder.Eq($"Sc.Field1", new BsonDocument()); 

And it works for both cases. I hope it will be useful to everyone who encounters the same case.