Filtering on array properties

Paul_Weston · 2024-08-16T10:52:15.377Z

Hi,

I’m new to MongoDB and I’ve been struggling with this for a day now. Any examples I’ve found refuse to work for me.

My documents are ExpandoObject that have a number of array properties. The array properties are BsonDocument arrays, although they could be arrays of known-types - I’ve tried both methods without success. Here’s how they look in the DB at the moment:

Schema

I’ve proved that I can find a document by id and by filtering on another field:

    var filter =
        Builders<ExpandoObject>.Filter.Eq("_id", id) &
        Builders<ExpandoObject>.Filter.Eq("Status", 4);

But when I include an array property in the filter and use the recommended ElemMatch, I cannot fetch the record. I’ve tried a few variations of the following to include the array filter:

    var filter =
        Builders<ExpandoObject>.Filter.Eq("_id", id) &
        Builders<ExpandoObject>.Filter.ElemMatch("CtoRatings",  Builders<BsonDocument>.Filter.Eq("UserId", 175));

I want to be able to update properties on these arrays, but first I want to fetch a record.

    var item = (await collection.FindAsync<ExpandoObject>(filter)).FirstOrDefault();

Can anyone see where I’m going wrong?

Thanks

Ruben_FS · 2024-08-16T11:00:04.258Z

Not sure, but I think you are using in your $elemMatch an object, instead of array. Maybe you forgot to add to CtoRatings the array field, so it would be CtoRatings._v.

Paul_Weston · 2024-08-19T06:27:02.611Z

Thanks for that - I added the ‘_v’ to the ElemMatch and it brought back the record.

However, I was throwing the ExpandoObject at the DB, and I’ve now realised that saving the ExpandoObject as a BsonDocument tidies up the schema/structure and the Objects are now Arrays, without the _t, _v, etc.

I’m now able to update an array element:

  var filter =
      Builders<BsonDocument>.Filter.Eq("_id", id) &
      Builders<BsonDocument>.Filter.ElemMatch("CtoRatings", Builders<BsonDocument>.Filter.Eq("UserId", userId));

  var update = Builders<BsonDocument>.Update.Set("CtoRatings.$.Rating", rating);

  var result = await collection.FindOneAndUpdateAsync(filter, update);

What I’m now trying to do is insert a new element if the element with the given userId is not found. I’ve discovered that I can’t use update-options with IsUpsert = true with an array element, as the filter will not find a document to update.

Do I have to complete this in two stages, i.e. if the update does not affect a record, do an insert, or is there a more efficient method?

I haven’t been able to find any C# driver examples of this - perhaps someone can point me in the right direction?

Thanks