Home How to apply filter on Array object in MongoDb table using Mongodb.Driver in C#?
Reply: 2

How to apply filter on Array object in MongoDb table using Mongodb.Driver in C#?

prog1011
1#
prog1011 Published in 2018-02-14 07:33:47Z

I am using MongoDB to store my application data. Now I want to fetch data based on filter on Array object in MongoDB table. as you can see below pic

This is my table tblEmployee. I want to find those records whose GroupList field contains value Group5. When I inserted these records as in mongodb table at that time I stored it as Dictionary<string,dynamic> from C# Code as below.

var clientTest = new MongoClient("mongodb://localhost:XXXX");
var dbTest = clientTest.GetDatabase("Test_DB");
var collectionTest = dbTest.GetCollection<Dictionary<string, dynamic>>("tblEmployee"); 
Dictionary<string, dynamic> obj = new Dictionary<string, dynamic>();
string[] items = { "Group1", "Group2", "Group5", "Group6" };
obj.Add("_id", Guid.NewGuid());
obj.Add("FirstName", "Carlton");
...// Other Props and Values
obj.Add("GroupList", items); // Here value type is String Array.
await collectionTest.InsertOneAsync(obj);

Now, I tried below code read data based on filter as I want.

var clientTest = new MongoClient("mongodb://localhost:XXXX");
                var dbTest = clientTest.GetDatabase("Test_DB");
                var collectionTest = dbTest.GetCollection<Dictionary<string, dynamic>>("tblEmployee");
                var builder = Builders<Dictionary<string, dynamic>>.Filter;
                var filter = builder.AnyIn("GroupList", "Group5"); // Used AnyIn to create IN filter for Array object
                var allDataObj = await collectionTest.FindAsync(filter);
                var allDataList = allDataObj.ToList(); 

But, in allDataList I got no records, it should return one record (record number 4) which I have highlighted in above image.

Please give me suggestions that How to build Filters for Array object which contains specific value(in my case Array which contain "Group5" string)

Any Help or suggestions would be highly appreciated.

Thanks.

Evk
2#
Evk Reply to 2018-02-14 08:40:08Z

This filter

builder.AnyIn("GroupList", "Group5");

Treats array as array of characters, not strings, because you did not provide generic type and it was inferred as char (because "Group5" string is IEnumerable<char>). For that reason, produced query is not what you would expect. To treat array as array of strings, you need to build filter like this:

var filter = builder.AnyIn("GroupList", new [] {"Group5"});

However you might notice from your screenshot that actual values are stored not in GroupList property, but deeper, inside GroupList > _v, so correct query should be:

var filter = builder.AnyIn("GroupList._v", new [] {"Group5"});

EDIT: as mentioned in comments - it appears that AnyIn in this specific case (for Dictionary<string, dynamic>) only works in older versions of C# driver, while in newer versions it produces strange exception. If you are using last version of C# driver - use suggestions from another answer:

var filter = builder.Eq( "GroupList._v", "Group5"); // query will be { GroupList._v: "Group5" }
var filter = builder.In("GroupList._v", new [] { "Group5" }); // query will be { GroupList._v: { $in: [ "Group5" ] }
dnickless
3#
dnickless Reply to 2018-02-14 07:58:17Z

I would suggest not to use dynamic types if possible since the MongoDB driver simply has no way of inferring the types he is dealing with.

The following two versions give the right result in your specific case:

var filter = builder.Eq( "GroupList._v", "Group5"); // query will be { GroupList._v: "Group5" }
var filter = builder.In("GroupList._v", new [] { "Group5" }); // query will be { GroupList._v: { $in: [ "Group5" ] }
You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.375233 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO