Appending item to a list nested within another list

[Francesco_Garrisi]

Hello,

I have a document with the following structure:

{'_id': "670e362adbbe1ff03860a2e1",
'testHistory': [
        {'results': [
                1,
                2
            ]
        },
        {'results': [
                3,
                4
            ]
        },
        {'results': []
        }
    ]
}

I need to push a new value to the “results” array in the last element of “testHistory”.

I am trying to to do it using the update_one method in conjunction with the $[] operator to determine the index of the last element, but I am struggling to configure exactly the update dictionary and the arrayFilters to do what I want.

This is what I am currently using:

updateDictionary = {‘$push’: {‘testHistory.$[elem].results’: 5}}

arrayFilters = [{‘elem’: {‘$eq’: {‘$arrayElemAt’: ‘$testHistory’}}}]

The code runs without errors, but the document is not actually updated.
What am I getting wrong?

Thank you,
Francesco

[Francesco_Garrisi]

Edit: The opration fails also if I use
arrayFilters = [{‘elem’: {‘$eq’: {‘$arrayElemAt’: [‘$testHistory’, -1]}}}]
or
arrayFilters = [{‘elem’: {‘$eq’: {‘$last’: ‘$testHistory’}}}]

[Joel_Lord]

Hi @Francesco_Garrisi, and welcome to the MongoDB forums.

** This solution won’t work as $[] updates all the array elements **

The following should let you update the last results array.

db.arrayPush.updateOne(
  {"_id": "670e362adbbe1ff03860a2e1"}, 
  {$push: {"testHistory.$[].results": 5}}
);

$[] indicates the last array element.

[Joel_Lord]

Correction, $[] specifies all the array elements. The provided solution does not work.

[steevej (Steeve Juneau)]

Take a look at a similar thread:

Is it possible to set a field in the last element in an array?

What I understand is that you want the chat array to become: That is you want the 2nd element, that is chat[ 1 ], to the answer object because the question object is present and the answer is not. If that is the case, look at this playground. It uses the $ operator to update the element specified by $elemMatch to specify an element that has a question but no answer. It will not be necessarily the last, in fact it is the first that matches the $elemMatch.

[Mark_Smith]

Without remodelling your data, as @steevej demonstrates in the linked post, I think this is going to be difficult.

Another approach might be to add a flag to the last item of the array, something like open: true and then use arrayFilters to match on that field. Then, when you add a new item to testHistory (starting a new results array), you unset the existing open flag and attach it to the new last item in the array. But I think that’s equally fiddly.

[Francesco_Garrisi]

Hi Everyone,

thank you all for your answers!

I get it is not straightforward to append the element where I want in a simple way.
I would prefer not having to flag the entries of the list, but it is likely the approach I might adopt in the end, as @Mark_Smith suggested.

Still, I didn’t get why the arrayFilters I am using are not working.

In any case, I was thinking: would it be possible to exploit some reverse operator on “testHistory” so that we can match the first elements of the reversed list?

Thanks!
Francesco

[NestorDaza (Néstor)]

Hello @Francesco_Garrisi welcome to the forum! Take a look at this playground, it piggybacks on the last element being empty (although it might not be the case in a real-life case).

I’d like to understand your use case a bit better, because it seems like a small change to your data model would simplify this problem a lot.
For example, how are results grouped by? You could solve the issue using a key-value pattern, like this

[Francesco_Garrisi]

Dear @NestorDaza,

thanks for the welcome! Also, good to know about the playground tool; it can come in handy.

Indeed, I am already at work to change my data model by de-embedding the testHistory entries in separate documents (turning testHistory into a list of ObjectIds). This solves the issue at hand entirely, because now “results” is an an outer field of the entry documents themselves, and I can $push on it.

Also, right now I actually have an “executionDate” timestamp for each testHistory entry. I guess I could use the arrayFilters to seach for the entry where “executionDate” is max, even though I am struggling to do this as well (see this updated playgound). I don’t understand if $max is allowed and how I should use it.

In general, it would be nice to be able to do the same by looking for the last index in the array rather than the contents of the document. Possibly this is discouraged in MongoDB?

Thank you!
Francesco

[steevej (Steeve Juneau)]

@Francesco_Garrisi

In any case, I was thinking: would it be possible to exploit some reverse operator on “testHistory” so that we can match the first elements of the reversed list?

You can $position with a value of 0 to $push at the front of the array. The update the first element becomes easy with { “$set” : { “array_field.0.attribute” : new_value } }.

An alternative strategy would be to have the incomplete results as a top level object rather than the last element of the array. You then $push it in testHistory only when it is completed.

[Mark_Smith]

Okay, I took this as a bit of a challenge, and I have solved it with an aggregation used in update_one.

    coll.update_one(
        {
            "_id": "MY_DOCUMENT_ID",
        },
        [
            {
                "$set": {
                    "testHistory": {
                        "$concatArrays": [
                            {
                                "$slice": [
                                    "$testHistory",
                                    {"$subtract": [{"$size": "$testHistory"}, 1]},
                                ]
                            },
                            [
                                {
                                    "$mergeObjects": [
                                        {"$last": "$testHistory"},
                                        {
                                            "results": {
                                                "$concatArrays": [
                                                    {
                                                        "$getField": {
                                                            "field": "results",
                                                            "input": {
                                                                "$last": "$testHistory"
                                                            },
                                                        }
                                                    },
                                                    [5],
                                                ]
                                            }
                                        },
                                    ]
                                }
                            ],
                        ]
                    }
                }
            },
        ],
    )

I’m not sure whether to apologise for this? I’m also not sure if it’s actually a good idea. /ht to @steevej for providing the overall technique!

I’m genuinely about to go have a lie down now, and I may come back and document how this works in the morning

[Francesco_Garrisi]

Wow, I did not expect the solution to be that involved, to be honest! Thank you a lot @Mark_Smith and @steevej for the effort!

I’m marking it as the solution since it is the one relevant for the title of the thread, although it’s clear that a better approach would be changing the data model to have something more manageable.

Thank you all!
Francesco