Query with filter builder on nested array using MongoDB C# driver with a given array of string

Question

Consider the following object structure stored as documents:

public class Foo
{
    public string Id { get; set; }
    public List<FooBar> Bars { get; set; }
    
    // ...
}
    
public class FooBar
{
    public string uid { get; set; }
    
    // ...
}

I have an array of strings :

var ArrayOfUid = ["STK-00112","STK-00117","STK-00113","STK-00114"]

I want to get all the products where FooBar.uid in ArrayOfUid

So in the end I get the list of products like this without duplicates

on MongoDb Side query :

db.collection.find({
    "foobar.uid": {
    $in: [
        "STK-00113",
        "STK-00117",
        "STK-00113",
        "STK-00114"
        ]
    }
})

Is there a way to do it with the array of strings? At the moment I can achieve it with only one given string like this :

var findFluent = collection.Find(Builders<Foo>.Filter.ElemMatch(
    foo => foo.Bars, 
    foobar => foobar.uid == "STK-00113"));

This will return the object that has the

foobar.uid = "STK-00113"

Is there a way to go through the array of strings and return the list of all objects?

Edit

public List<Produit> Get() =>
    _produits.Find(produit => true).ToList();

will return this list of products :

[
{
    "_id": {
        "$oid": "6152da109e0ced07f16a3fom"
    },
    "uid": "20210915-67102",
    "type": "N1",
    "foobar": [
        {
            "uid": "STK-00117",
            "nom": "JOHN DOE T1"
        },
        {
            "uid": "STK-00114",
            "nom": "JOHN DOE T5"
        }
    ]
},
{
    "_id": {
        "$oid": "6152da109e0ced07f16a3fxs"
    },
    "uid": "20210915-67108",
    "type": "N5",
    "foobar": [
        {
            "uid": "STK-00117",
            "nom": "JOHN DOE T3"
        },
    ]
},
{
    "_id": {
        "$oid": "6152da109e5ced07f16a3fdc"
    },
    "uid": "20210915-67108",
    "type": "N12",
    "foobar": [
        {
            "uid": "STK-00115",
            "nom": "JOHN DOE T4"
        },
        {
            "uid": "STK-00117",
            "nom": "JOHN DOE T10"
        },
        {
            "uid": "STK-00113",
            "nom": "JOHN DOE T18"
        },
    ]
},


{
    "_id": {
        "$oid": "6152da609e0ced07f16a3fdc"
    },
    "uid": "20210915-67108",
    "type": "N17",
    "foobar": [
        {
            "uid": "STK-00113",
            "nom": "JOHN DOE T15"
        },
        {
            "uid": "STK-00112",
            "nom": "JOHN DOE T16"
        },
        {
            "uid": "STK-00111",
            "nom": "JOHN DOE T17"
        },
    ]
},
{
    "_id": {
        "$oid": "6152da109e0ced07f16a3f5e"
    },
    "uid": "20210915-67108",
    "type": "N16",
    "foobar": [
        {
            "uid": "STK-00999",
            "nom": "JOHN DOE T99"
        },
    ]
}]

And this is my service function :

public List<Produit> GetFromSitesAsync(List<string> productSites)
{



    // WORKING BUT EMPTY 
    FilterDefinition<Produit> filter = new BsonDocument(
        "foobar.uid",
        new BsonDocument(
            "$in",
            BsonArray.Create(productSites)
        )
    );
    return _produits.Find(filter).ToList();


}

NB :

productSites = ["STK-00112","STK-00117","STK-00113","STK-00114"]

dododo · Accepted Answer · 2021-10-16 12:53:52Z

        var range = new[]
        {
            "STK-00113",
            "STK-00117",
            "STK-00113",
            "STK-00114"
        };

        var client = new MongoClient();
        var db = client.GetDatabase("d");
        var coll = db.GetCollection<Foo>("c");
        var filter = Builders<Foo>.Filter.Where(foo => foo.Bars.Any(b => range.Contains(b.uid)));
        var result = coll.Find(filter).ToList();

the generated query will be:

{
    "find": "c",
    "filter": {
        "Bars": {
            "$elemMatch": {
                "uid": {
                    "$in": ["STK-00113", "STK-00117", "STK-00113", "STK-00114"]
                }
            }
        }
    }
}

UPDATE: I used the shell to insert the data you mentioned in the original question and it works:

MongoDB Enterprise replset:PRIMARY> db.c.runCommand({
...     "find": "c",
...     "filter": {
...         "foobar": {
...             "$elemMatch": {
...                 "uid": {
...                     "$in": ["STK-00113", "STK-00117", "STK-00113", "STK-00114"]
...                 }
...             }
...         }
...     }
... })
{
        "cursor" : {
                "firstBatch" : [
                        {
                                "_id" : ObjectId("616aca60e565e50be00b8a4a"),
                                "uid" : "20210915-67102",
                                "type" : "N1",
                                "foobar" : [
                                        {
                                                "uid" : "STK-00117",
                                                "nom" : "JOHN DOE T1"
                                        },
                                        {
                                                "uid" : "STK-00114",
                                                "nom" : "JOHN DOE T5"
                                        }
                                ]
                        },
                        {
                                "_id" : ObjectId("616aca60e565e50be00b8a4b"),
                                "uid" : "20210915-67108",
                                "type" : "N5",
                                "foobar" : [
                                        {
                                                "uid" : "STK-00117",
                                                "nom" : "JOHN DOE T3"
                                        }
                                ]
                        },
                        {
                                "_id" : ObjectId("616aca60e565e50be00b8a4c"),
                                "uid" : "20210915-67108",
                                "type" : "N12",
                                "foobar" : [
                                        {
                                                "uid" : "STK-00115",
                                                "nom" : "JOHN DOE T4"
                                        },
                                        {
                                                "uid" : "STK-00117",
                                                "nom" : "JOHN DOE T10"
                                        },
                                        {
                                                "uid" : "STK-00113",
                                                "nom" : "JOHN DOE T18"
                                        }
                                ]
                        },
                        {
                                "_id" : ObjectId("616aca60e565e50be00b8a4d"),
                                "uid" : "20210915-67108",
                                "type" : "N17",
                                "foobar" : [
                                        {
                                                "uid" : "STK-00113",
                                                "nom" : "JOHN DOE T15"
                                        },
                                        {
                                                "uid" : "STK-00112",
                                                "nom" : "JOHN DOE T16"
                                        },
                                        {
                                                "uid" : "STK-00111",
                                                "nom" : "JOHN DOE T17"
                                        }
                                ]
                        }
                ],
                "id" : NumberLong(0),
                "ns" : "so1.c"
        },
        "ok" : 1,
        "$clusterTime" : {
                "clusterTime" : Timestamp(1634388656, 1),
                "signature" : {
                        "hash" : BinData(0,"AAAAAAAAAAAAAAAAAAAAAAAAAAA="),
                        "keyId" : NumberLong(0)
                }
        },
        "operationTime" : Timestamp(1634388656, 1)
}

Thank you so much for your response, can't understand why It's returning an empty array.
make sure that you use right field/collection naming, see update

Yong Shun · Accepted Answer · 2021-10-16 06:25:13Z

1

You can set filter with BsonDocument object as below:

string[] uids = new string[] { "STK-00113", "STK-00117", "STK-00113", "STK-00114" };
FilterDefinition<Foo> filter = new BsonDocument(
    "foobar.uid", 
    new BsonDocument(
        "$in", 
        BsonArray.Create(uids)
    )
);
var findFluent = collection.Find(filter);

OR

Write MongoDB query and deserialize to BsonDocument.

var query = @"{
    'foobar.uid': {
      $in: [
        'STK-00113',
        'STK-00117',
        'STK-00113',
        'STK-00114'
      ]
    }
}";
var filter = MongoDB.Bson.Serialization.BsonSerializer.Deserialize<BsonDocument>(query);
var findFluent = collection.Find(filter);

FYI, you can use MongoDB Compass to export Query to C# Language.

edited Oct 16, 2021 at 6:25

answered Oct 15, 2021 at 11:13

Yong Shun

54.5k6 gold badges38 silver badges66 bronze badges

7 Comments

Taieb Over a year ago

Thank you so much for your response, can't understand why It's returning an empty array, no error just an empty arrya.

Yong Shun Over a year ago

Hi, can you show the sample Mongo Document data in the question? Thanks.

Taieb Over a year ago

Hi, Sure I updated the question with more details, thank you again.

Yong Shun Over a year ago

The data looks fine for me, perhaps you try to use MongoDB Compass to query your data, and export the query to C# to compare with my query as the link I shared in the answer. Fingers crossed.

Yong Shun Over a year ago

And also check that you are accessing the correct collection, with the correct property field.

|

Collectives™ on Stack Overflow

Query with filter builder on nested array using MongoDB C# driver with a given array of string

2 Answers 2

2 Comments

7 Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

7 Comments

Related