C# mongodb - how to update nested array elements

Question

I have the following JSON structure that represents an item

{
    Id: "a",
    Array1: [{
        Id: "b",
        Array2: [{
            Id: "c",
            Array3: [
                {...}
            ]
        }]
    }]
}

I need to be able to either replace the array element in Array2 with a new item or to replace just Array3 with a new array.

Here is my code to replace the array item in Array2:

await Collection.UpdateOneAsync(
    item => item.Id.Equals("a") &&
    item.Array1.Any(a => a.Id.Equals("b")) &&
    item.Array1[-1].Array2.Any(b => b.Id.Equals("c")),
    Builders<Item>.Update.Set(s => s.Array1[-1].Array2[-1], newArray2Item)
);

When executing this code I'm getting this error:

"A write operation resulted in an error.
 Too many positional (i.e. '$') elements found in path 'Array1.$.Array2.$'"

Here is my code to replace Array3 within Array2:

await Collection.UpdateOneAsync(
        item => item.Id.Equals("a") &&
        item.Array1.Any(a => a.Id.Equals("b")) &&
        item.Array1[-1].Array2.Any(b => b.Id.Equals("c")),
        Builders<Item>.Update.Set(s => s.Array1[-1].Array2[-1].Array3, newArray3)
    );

And this is the error:

"A write operation resulted in an error.
 Too many positional (i.e. '$') elements found in path 'Array1.$.Array2.$.Array3'"

I'm using C# MongoDB driver version 2.5.0 and MongoDB version 3.6.1

I found this Jira ticket Positional Operator Matching Nested Arrays that says the problem was fixed and they suggested this syntax for the update

Update all matching documents in nested array:

db.coll.update({}, {$set: {“a.$[i].c.$[j].d”: 2}}, {arrayFilters: [{“i.b”: 0}, {“j.d”: 0}]})
Input: {a: [{b: 0, c: [{d: 0}, {d: 1}]}, {b: 1, c: [{d: 0}, {d: 1}]}]}
Output: {a: [{b: 0, c: [{d: 2}, {d: 1}]}, {b: 1, c: [{d: 0}, {d: 1}]}]}

So I converted it to my elements:

db.getCollection('Items').update(
{"Id": "a"},
{$set: {"Array1.$[i].Array2.$[j].Array3": [newArray3]}}, 
{arrayFilters: 
    [
        {"i.Id": "b"}, 
        {"j.Id": "c"}
    ]}
)

And got this error:

cannot use the part (Array1 of Array.$[i].Array2.$[j].Array3) to traverse the element

Any ideas on how to solve this error?

It's not pretty but as a shortcut you can always just write your updates using strings like you'd write them in the shell and then pass them to the `Update()` method. — dnickless, Feb 19 '18 at 14:38
This is why I gave the sample from Jira. It still doesn't work,,, — Liran Friedman, Feb 19 '18 at 14:41
You will need an updated client for this to work (try the "mongo.exe" shell that came with your distribution): https://stackoverflow.com/questions/47225822/mongodb-3-6-0-rc3-array-filters-not-working — dnickless, Feb 19 '18 at 15:00
I can't use `mongo.exe` in C#... the link you sent me is talking about robomongo. — Liran Friedman, Feb 19 '18 at 15:06
I know. ;) The last query you gave in your example is a string based one that you should be able to run inside mongo.exe without getting an error. That's all I'm saying. Once you've got that query working you can use the filter and update parts of it to create a C# version. — dnickless, Feb 19 '18 at 15:12
Take a look [here](https://docs.mongodb.com/manual/reference/command/setFeatureCompatibilityVersion/#view-fcv) and [here](https://docs.mongodb.com/manual/reference/command/setFeatureCompatibilityVersion/#dbcmd.setFeatureCompatibilityVersion) to run it I only needed to run this command `db.adminCommand( { setFeatureCompatibilityVersion: "3.6" } )` — Liran Friedman, Feb 19 '18 at 15:17

score 8 · Accepted Answer · answered Feb 19 '18 at 15:23

8

Here's the C# version of what you need:

var filter = Builders<Item>.Filter.Eq("Id", "a");
var update = Builders<Item>.Update.Set("Array1.$[i].Array2.$[j].Array3", new[] { new Item { Id = "d" } });
var arrayFilters = new List<ArrayFilterDefinition> { new JsonArrayFilterDefinition<Item>("{'i.Id': 'b'}"), new JsonArrayFilterDefinition<Item>("{'j.Id': 'c'}") };
var updateOptions = new UpdateOptions { ArrayFilters = arrayFilters };
collection.UpdateOne(filter, update, updateOptions);

answered Feb 19 '18 at 15:23

dnickless

10,733
1
19
34

1

It doesn't work from C# but does work from `Studio 3T` any idea why? It doesn't give an error and looks like the command went through, but the update does not apply... – Liran Friedman Feb 19 '18 at 15:50
1

The only difference I see is that I use the filters for the `id` like this: `"_id", "ObjectId(ITEM_ID)"` and like this: "{\"i._id\": ObjectId(ARRAY_ITEM_ID)}" because the id's are generated by mongo and represented by `ObjectId`. Any suggenstions? – Liran Friedman Feb 19 '18 at 15:59
1

I would suggest you log your queries (`db.setProfilingLevel(2)`) and check the MongoDB log file for what the resulting query looks like when you run your C# code. That alone is likely to reveal the problem, I guess. Otherwise you could run that resulting query in Studio3T and see if it produces the right result there (it won't) and why it does not. – dnickless Feb 19 '18 at 16:04
1

https://www.mkyong.com/mongodb/mongodb-where-is-the-log-file/ description for Linux but also pretty much applicable for Windows. – dnickless Feb 20 '18 at 07:15
1

I found some log file under `C:\data\log\mongod.log`, but it doesn't write anything to it... I couldn't find anything that looks like `/var/log/mongodb/mongodb.log` – Liran Friedman Feb 20 '18 at 07:17
1

Fount it, it was under `C:\data\log\mongod.log` but I also needed to `db.setLogLevel(5)` [SetLogLevel](https://docs.mongodb.com/manual/reference/method/db.setLogLevel/) – Liran Friedman Feb 20 '18 at 07:45
1

Now it works, turns out I needed to set the id like this `var filter = Builders.Filter.Eq("_id", new ObjectId(configurationDevice.SetupId));` – Liran Friedman Feb 20 '18 at 07:48
1

Thank you for the help. Much appreciated :-) – Liran Friedman Feb 20 '18 at 07:49
1

You're welcome and, yes, that sounds very correct. My example was based on your sample JSON above. But when dealing with string based queries, the C# driver won't translate any property names. – dnickless Feb 20 '18 at 07:51
1

Can you please try and help me out with another question on how to remove items? posted [here](https://stackoverflow.com/questions/48887667/c-sharp-mongodb-how-to-remove-an-item-from-multiple-nested-arrays-by-element-v) – Liran Friedman Feb 20 '18 at 14:37

C# mongodb - how to update nested array elements

1 Answers1

Linked

Related