How to insert data on last item of an inner array in MongoDB

Question

I am trying to insert data in the last element of an array in MongoDB. insert in the first element works using the 0 operator, but I'm confused about how to insert in the last element. To explain it, consider the following data:

[
    {
        "_id": "1",
        "company": "turivius",
        "forms": [
            {
                "current_version": 1,
                "history": [
                    {
                        "content": [],
                        "layout": [],
                        "responses": [
                            {
                                "client_id": 100,
                                "response_date": datetime(2022, 5, 17, 3, 0),
                                "values": [
                                    {
                                        "field_id": 0,
                                        "primeiro user": "primeiro form, resposta 1",
                                    },
                                    {
                                        "field_id": 1,
                                        "value": "aaa",
                                    },
                                ],
                            },
                            {
                                "client_id": 100,
                                "response_date": datetime(2022, 5, 17, 3, 0),
                                "values": [
                                    {
                                        "field_id": 0,
                                        "primeiro user": "primeiro form, resposta 2",
                                    },
                                    {
                                        "field_id": 1,
                                        "value": "bbb",
                                    },
                                ],
                            },
                        ],
                        "version": 0,
                    },
                    {
                        "content": [],
                        "layout": [],
                        "responses": [
                            {
                                "client_id": 100,
                                "response_date": datetime(2022, 5, 17, 3, 0),
                                "values": [
                                    {
                                        "field_id": 0,
                                        "primeiro user": "primeiro form, resposta 1 v1",
                                    },
                                    {
                                        "field_id": 1,
                                        "value": "aaa",
                                    },
                                ],
                            }
                        ],
                        "version": 1,
                    },
                ],
                "id": "33b66684-24a9-4a45-a12f-27a330152ac8",
                "is_active": True,
            },
            {
                "current_version": 0,
                "history": [],
                "id": "fa2eb9a1-c7c4-4b64-b682-7f51658bc4ab",
                "is_active": False,
            },
        ],
        "user_id": "1",
    },
    {
        "_id": "2",
        "company": "turivius",
        "forms": [
            {
                "current_version": 0,
                "history": [],
                "id": "565656",
                "is_active": True,
            },
        ],
        "user_id": "9999",
    },
]

I want to insert data in the document with user_id = 1, form.id = 33b66684-24a9-4a45-a12f-27a330152ac8 and in the history array where the version = 1 I want to insert a new response:

{
    "client_id": 100,
    "response_date": datetime(2022, 5, 17, 3, 0),
    "values": [
        {
            "field_id": 0,
            "test": "test",
        },
        {
            "field_id": 1,
            "test2": "test3",
        },
    ],
}

The history with version 1 is the last element, if I wanted insert in the first element the following code would works:

find_one_and_update(
            {"$and": [{"user_id": "1"}, {"forms.id": '33b66684-24a9-4a45-a12f-27a330152ac8'}]},
            {
                "$push": {"forms.$.history.0.responses": data_here},
            },
            return_document=ReturnDocument.AFTER,
)

Is there anyway to do it that way or I need a more complex query to do it?

Answer 1

You want to add a new document to the responses array when those conditions are true:

{
  user_id: '1',
  'forms.id': '33b66684-24a9-4a45-a12f-27a330152ac8',
  'forms.history.version': 1
}

forms is an array, so you want to update the array element that matches the condition, so you can use the $[identifier] operator. Inside the array of forms, you only want to update the history with a specific version, so you need to use the $[identifier] operator multiple times, the field you want to update would be like this:

forms.$[f].history.$[h].responses 

f is the identifier of each elements in forms that matches the condition and h the elements of the history array that match the other condition. An arrayFilters object is used to define the conditions.

The complete query could be like this:

db.collection.update({
  user_id: "1",
  'forms.id': '33b66684-24a9-4a45-a12f-27a330152ac8',
  'forms.history.version': 1
},
{
  $push: {
    'forms.$[f].history.$[h].responses': {
      "client_id": 100,
      "response_date": "datetime(2022, 5, 17, 3, 0)",
      "values": [
        {
          "field_id": 0,
          "test": "test",              
        },
        {
          "field_id": 1,
          "test2": "test3",              
        },            
      ],          
    }
  }
},
{
  arrayFilters: [
    {
      'h.version': 1
    },
    {
      'f.id': '33b66684-24a9-4a45-a12f-27a330152ac8'
    }
  ]
})

Working example at MongoDB playground