How do I slice a nested array and how do I update a specific field in it?

Hi, I have the following data:

{
  _id: "some_id",
  array1: [
    {
      _id: "level_1_id",
      array2: [
        {
           _id: "level_2_id_0,
           count: 0
        },
        {
           _id: "level_2_id_1,
           count: 1
        },
      ]
    }
  ]
}
  1. How do I retrieve the count of the element with _id: level_2_id_1? All I know is the _id of the root document, the _id of the element in array1, and the index of array2 I need. I tried the following, with index = 0:
db.findOne(
  {
    _id: "some_id",
    "array1._id": "level_1_id"
  },
  {
    fields: { _id: 0, "array1.$.array2": { $slice: [index, 1] }
  }
})

But this doesn’t give me what I need.

  1. How do I update the count of an element at a certain index in array2?

Appreciate any help, thank you!

Hi @Ajay_Pillay
Well, if I understood your questions, this work for me:
first I created the document
image

and use the next MQL:

db.product.find({ “_id”:“some_id”, “array1._id”:“level_1_id”},{"_id":0, “array1.array2”:{"$slice": 1 } } ).pretty()

Hi @Javier_Ojeda yes this works in the case you described because there’s only 1 element in array1. If you add more elements to array1, it’s going to return the entire array1 within each document and it will slice each array2 within the respective element in array1.

Hi @Ajay_Pillay . The next is the more close I got until now:

I insert more fields:

db.product.find({ “_id”:“some_id”},{ “array1.array2.count”:1} ).pretty()

mongodbComm2

I am testing some MQL expressions using logics, comparatives and projections operators within this case., because I am getting some undesired behavior with some of them.
I continue learning…

Unfortunately I don’t see anything, I don’t think the image upload was successful. Would you be able to paste the code instead?

EDIT: With the updated images/code it doesn’t return the results quite as expected yet.

Hello : )

For the first the path is

[“array1” (variable.array1)
member where member._id= “level_1_id” (filter and get first element)
“array2” (variable.array2)
1 (get variable 1)
“count” (variable.count)
]

We will do it with paths,and $arrayElementAt

{
  "aggregate": "testcoll",
  "pipeline": [
    {
      "$match": {
        "$expr": {
          "$eq": [
            "$_id",
            "some_id"
          ]
        }
      }
    },
    {
      "$project": {
        "_id": 0,
        "countValue": {
          "$let": {
            "vars": {
              "array1": "$$ROOT.array1"
            },
            "in": {
              "$let": {
                "vars": {
                  "array1_member": {
                    "$arrayElemAt": [
                      {
                        "$filter": {
                          "input": "$$array1",
                          "as": "m",
                          "cond": {
                            "$eq": [
                              "$$m._id",
                              "level_1_id"
                            ]
                          }
                        }
                      },
                      0
                    ]
                  }
                },
                "in": {
                  "$let": {
                    "vars": {
                      "array2": "$$array1_member.array2"
                    },
                    "in": {
                      "$let": {
                        "vars": {
                          "array2_member": {
                            "$arrayElemAt": [
                              "$$array2",
                              1
                            ]
                          }
                        },
                        "in": "$$array2_member.count"
                      }
                    }
                  }
                }
              }
            }
          }
        }
      }
    }
  ],
  "cursor": {},
  "maxTimeMS": 1200000
}

Printed

{:countValue 1}

The second is more complicated because we need to get in,and then restore the document structure,and we use $mergeObjects. ($$m variable name is used 2 times,in both maps)

{
  "update": "testcoll",
  "updates": [
    {
      "q": {
        "$expr": {
          "$eq": [
            "$_id",
            "some_id"
          ]
        }
      },
      "u": [
        {
          "$replaceRoot": {
            "newRoot": {
              "$mergeObjects": [
                "$$ROOT",
                {
                  "array1": {
                    "$map": {
                      "input": "$array1",
                      "as": "m",
                      "in": {
                        "$cond": [
                          {
                            "$eq": [
                              "$$m._id",
                              "level_1_id"
                            ]
                          },
                          {
                            "$mergeObjects": [
                              "$$m",
                              {
                                "array2": {
                                  "$map": {
                                    "input": "$$m.array2",
                                    "as": "m",
                                    "in": {
                                      "$cond": [
                                        {
                                          "$eq": [
                                            "$$m._id",
                                            "level_2_id_1"
                                          ]
                                        },
                                        {
                                          "$mergeObjects": [
                                            "$$m",
                                            {
                                              "count": 100
                                            }
                                          ]
                                        },
                                        "$$m"
                                      ]
                                    }
                                  }
                                }
                              }
                            ]
                          },
                          "$$m"
                        ]
                      }
                    }
                  }
                }
              ]
            }
          }
        }
      ],
      "multi": true
    }
  ]
}

It updated count to 100
Screenshot from 2021-07-07 18-38-54

You can take the filter and the pipeline and use it in driver method.

2 Likes

Thank you so much, these are some very complex aggregations and I appreciate the time taken to come up with them.

However, for the first aggregation, it doesn’t return the count at the specific index of array2 but rather returns an array of count, as such (in this example, my document had 3 elements in array2, with a count of 1,2,3 respectively):

{
  countValue: [1,2,3]
}

But this is always the same regardless of the index specified in $arrayElemAt under the array2_member var. I tried index 0,1,2 but the output is always the same, where I would be expecting countValue: 1, countValue: 2, countValue: 3 respectively as you described.

The second pipeline works well!

However - what if I only know the index of the element in array2 that I want to update? This pipeline requires the _id of the element in array2.

Hello

The first returns the value of the field “count” for index=1 in array2

{
  _id: "some_id",
  array1: [
    {
      _id: "level_1_id",
      array2: [
        {
           _id: "level_2_id_0,
           count: 0
        },
        {
           _id: "level_2_id_1,
           count: "tomatoes"
        },
      ]
    }
  ]
}

Would print

{"countValue" : "tomatoes"}

I thought this is what you want,if its not that,if you can give sample data,and the expected output.

For the second,if you want to update 1 element of an array and you know the index of that element you can do it in many ways,bellow are 3 of possible ways.

Data(collection with 1 document for example)

{myarray [1 2 3 4 5]}

All 3 results in (at index=3 we add 10)

{myarray [1 2 3 14 5]}

The 3 methods are
1)get the member of the index,update it,and concat array_before [updated_member] array_after
you need to be careful if those exists,here it only works for index in the middle
2)map after zipmap, [[1 0][2 1][3 2] [4 3] [5 4]] zipmap array with range(count(array))
the second is the index
3)reduce array to array,and keeping the index value ,update only in that index

First is simpler,but the other 2 can be used in many things.

Slice

[
  {
    "$addFields": {
      "myarray": {
        "$let": {
          "vars": {
            "m": {
              "$arrayElemAt": [
                "$myarray",
                3
              ]
            },
            "array_before": {
              "$slice": [
                "$myarray",
                3
              ]
            },
            "array_after": {
              "$slice": [
                "$myarray",
                4,
                {
                  "$size": "$myarray"
                }
              ]
            }
          },
          "in": {
            "$let": {
              "vars": {
                "new_m": {
                  "$add": [
                    "$$m",
                    10
                  ]
                }
              },
              "in": {
                "$concatArrays": [
                  "$$array_before",
                  [
                    "$$new_m"
                  ],
                  "$$array_after"
                ]
              }
            }
          }
        }
      }
    }
  }
]

Map on zip result with range

[
  {
    "$addFields": {
      "myarray": {
        "$map": {
          "input": {
            "$zip": {
              "inputs": [
                "$myarray",
                {
                  "$range": [
                    0,
                    {
                      "$size": "$myarray"
                    }
                  ]
                }
              ]
            }
          },
          "as": "m_index",
          "in": {
            "$let": {
              "vars": {
                "m": {
                  "$arrayElemAt": [
                    "$$m_index",
                    0
                  ]
                },
                "index": {
                  "$arrayElemAt": [
                    "$$m_index",
                    1
                  ]
                }
              },
              "in": {
                "$cond": [
                  {
                    "$eq": [
                      "$$index",
                      3
                    ]
                  },
                  {
                    "$add": [
                      "$$m",
                      10
                    ]
                  },
                  "$$m"
                ]
              }
            }
          }
        }
      }
    }
  }
]

Reduce keeping the index value

[
  {
    "$addFields": {
      "myarray": {
        "$arrayElemAt": [
          {
            "$reduce": {
              "input": "$myarray",
              "initialValue": [
                [],
                0
              ],
              "in": {
                "$let": {
                  "vars": {
                    "arrray_index": "$$value",
                    "m": "$$this"
                  },
                  "in": {
                    "$let": {
                      "vars": {
                        "array": {
                          "$arrayElemAt": [
                            "$$arrray_index",
                            0
                          ]
                        },
                        "index": {
                          "$arrayElemAt": [
                            "$$arrray_index",
                            1
                          ]
                        }
                      },
                      "in": {
                        "$cond": [
                          {
                            "$eq": [
                              "$$index",
                              3
                            ]
                          },
                          [
                            {
                              "$concatArrays": [
                                "$$array",
                                [
                                  {
                                    "$add": [
                                      "$$m",
                                      10
                                    ]
                                  }
                                ]
                              ]
                            },
                            {
                              "$add": [
                                "$$index",
                                1
                              ]
                            }
                          ],
                          [
                            {
                              "$concatArrays": [
                                "$$array",
                                [
                                  "$$m"
                                ]
                              ]
                            },
                            {
                              "$add": [
                                "$$index",
                                1
                              ]
                            }
                          ]
                        ]
                      }
                    }
                  }
                }
              }
            }
          },
          0
        ]
      }
    }
  }
]

So for the first part, here’s my sample data (I changed count to comments, because that’s the real field):

{
    "_id" : "ROOT_DOCUMENT_ID",
    "array1" : [ 
        {
            "_id" : "ARRAY1_A",
            "array2" : [ 
                {
                    "_id" : "ARRAY2_A",
                    "comments" : 0
                }
            ]
        }, 
        {
            "_id" : "ARRAY1_B",
            "array2" : [ 
                {
                    "_id" : "ARRAY2_A",
                    "comments" : 10
                }, 
                {
                    "_id" : "ARRAY2_B",
                    "comments" : 20
                }, 
                {
                    "_id" : "ARRAY2_C",
                    "comments" : 30
                }
            ]
        }
    ]
}

When I run the following find:

db.aggregate([
    { $match: { "$expr": { "$eq": [ "$_id", "ROOT_DOCUMENT_ID" ] } } },
    {
        $project: {
            "_id": 0,
            "comments": {
                $let: {
                    "vars": {"array1": "$$ROOT.array1" },
                    "in": {
                    $let: {
                        "vars": {
                        "array1_element": {
                            $arrayElemAt: [
                            {
                                $filter: {
                                "input": "$$array1",
                                "as": "m",
                                "cond": {
                                    $eq: [
                                    "$$m._id",
                                    "ARRAY1_B"
                                    ]
                                }
                                }
                            },
                            0
                            ]
                        }
                        },
                        "in": {
                        $let: {
                            "vars": {
                            "array2": "$$array1.array2"
                            },
                            "in": {
                            $let: {
                                "vars": {
                                "array2": {
                                    $arrayElemAt: [
                                    "$$array2",
                                    0 // This is the index I want
                                    ]
                                }
                                },
                                "in": "$$array2.comments"
                            }
                            }
                        }
                        }
                    }
                    }
                }
            }
        }
    }
])

It returns:

{
  comments: [10, 20, 30]
}

It returns the same thing even when I change the index to 1 or 2. What I’m expecting is:

{
  comments: 10
}

And for the 3 methods - is method #3 the best, since method 1 doesn’t allow me to update at the first and last index?

Hello

[
  {
    "$match": {
      "$expr": {
        "$eq": [
          "$_id",
          "ROOT_DOCUMENT_ID"
        ]
      }
    }
  },
  {
    "$project": {
      "_id": 0,
      "comments": {
        "$let": {
          "vars": {
            "array1": "$$ROOT.array1"
          },
          "in": {
            "$let": {
              "vars": {
                "array1_member": {
                  "$arrayElemAt": [
                    {
                      "$filter": {
                        "input": "$$array1",
                        "as": "m",
                        "cond": {
                          "$eq": [
                            "$$m._id",
                            "ARRAY1_B"
                          ]
                        }
                      }
                    },
                    0
                  ]
                }
              },
              "in": {
                "$let": {
                  "vars": {
                    "array2": "$$array1_member.array2"
                  },
                  "in": {
                    "$let": {
                      "vars": {
                        "array2_member": {
                          "$arrayElemAt": [
                            "$$array2",
                            0
                          ]
                        }
                      },
                      "in": "$$array2_member.comments"
                    }
                  }
                }
              }
            }
          }
        }
      }
    }
  }
]

Printed

{comments 10}

You changed the $let ,while trying it i guess and you did it

"vars": {
            "array2": "$$array1.array2"
            },

Where it was (previous query worked also, i think this edit above made it to now work)

"vars": {
                    "array2": "$$array1_member.array2"
                  }

Previous query i think worked also for the first data if you run it unchanged.

I sended you the same query for the new data,i didnt really changed anything just the names
of the new fields.

For the second thing,about updating one array,first method is fine,but you need to do some extra checks that i didnt do,i dindnt checked if the member was the last in the array

This works in all cases i think,edit the “index”: 0 , myarray with your field name,
and here i just add 10 to the member to update it, change the new_m bellow

"new_m": {
                      "$add": [
                        "$$m",
                        10
                      ]
                    }
                  }

Query

[
  {
    "$addFields": {
      "myarray": {
        "$let": {
          "vars": {
            "index": 0
          },
          "in": {
            "$let": {
              "vars": {
                "m": {
                  "$arrayElemAt": [
                    "$myarray",
                    "$$index"
                  ]
                },
                "array_before": {
                  "$slice": [
                    "$myarray",
                    "$$index"
                  ]
                },
                "array_after": {
                  "$cond": [
                    {
                      "$lt": [
                        "$$index",
                        {
                          "$subtract": [
                            {
                              "$size": "$myarray"
                            },
                            1
                          ]
                        }
                      ]
                    },
                    {
                      "$slice": [
                        "$myarray",
                        {
                          "$add": [
                            "$$index",
                            1
                          ]
                        },
                        {
                          "$size": "$myarray"
                        }
                      ]
                    },
                    []
                  ]
                }
              },
              "in": {
                "$let": {
                  "vars": {
                    "new_m": {
                      "$add": [
                        "$$m",
                        10
                      ]
                    }
                  },
                  "in": {
                    "$concatArrays": [
                      "$$array_before",
                      [
                        "$$new_m"
                      ],
                      "$$array_after"
                    ]
                  }
                }
              }
            }
          }
        }
      }
    }
  }
]
1 Like

For the first one - good catch! That was actually just a typo in my post here. But I found out what the real problem was. The last condition.

"in": "$$array2.comments"

Should be:

"in": "$$array2_member.comments"

Final working query is here:

db.aggregate([
    { $match: { "$expr": { "$eq": [ "$_id", "ROOT_DOCUMENT_ID" ] } } },
    {
        $project: {
            "_id": 0,
            "comments": {
                $let: {
                    "vars": {"array1": "$$ROOT.array1" },
                    "in": {
                    $let: {
                        "vars": {
                        "array1_member": {
                            $arrayElemAt: [
                            {
                                $filter: {
                                "input": "$$array1",
                                "as": "m",
                                "cond": {
                                    $eq: [
                                    "$$m._id",
                                    "ARRAY1_B"
                                    ]
                                }
                                }
                            },
                            0
                            ]
                        }
                        },
                        "in": {
                        $let: {
                            "vars": {
                            "array2_member": "$$array1_member.array2"
                            },
                            "in": {
                            $let: {
                                "vars": {
                                "array2": {
                                    $arrayElemAt: [
                                    "$$array2",
                                    0 // This is the index I want
                                    ]
                                }
                                },</pre>
                                "in": "$$array2_member.comments"
                            }
                            }
                        }
                        }
                    }
                    }
                }
            }
        }
    }
])

This gives the desired output.

For the second query, I was trying to work with that but I don’t see array1 or array2 being referenced anywhere or matched. I don’t really see how to fit that into my case.

With the following data:

{
    "_id" : "ROOT_DOCUMENT_ID",
    "array1" : [ 
        {
            "_id" : "ARRAY1_A",
            "array2" : [ 
                {
                    "_id" : "ARRAY2_A",
                    "comments" : 0
                }
            ]
        }, 
        {
            "_id" : "ARRAY1_B",
            "array2" : [ 
                {
                    "_id" : "ARRAY2_A",
                    "comments" : 10
                }, 
                {
                    "_id" : "ARRAY2_B",
                    "comments" : 20
                }, 
                {
                    "_id" : "ARRAY2_C",
                    "comments" : 30
                }
            ]
        }
    ]
}

The only information I have is: _id (root document ID), array1._id, and the index of array 2.

Appreciate the help again!

yes i know , i just wrote one that updates 1 member of an array,not for your data specific.
that queries are complex because nested data , but they can be much easier if you generate MQL,for some common used things,so dont get discouraged from them,they can be much shorter.MQL is so powerfull and it can become smaller in size.

1 Like

Understood, yes dealing with nested data is pretty difficult. I might go with your initial approach of just using the _id. I don’t think it’ll be good having too complicated of a query too. Might even restructure some of the data.

Thanks once again, I really appreciate the time taken to come up with those, I learned a lot from them! :slight_smile:

1 Like

This is my first week learning mongodb and I appreciate this kind of cases because the knowledge you can get with them. Thanks both of you. :+1:

1 Like

The complex queries using aggregation are a lot to learn. I’ve found personally that remodelling your data to avoid nesting as much as possible is ideal because then you avoid having to deal with the kind of complexity you see here.

You can learn a lot from nested data as we did here but it takes time to figure out how to perform CRUD operations effectively.

1 Like

That’s correct.
As Power BI developer I know the importance of the data model. And this case is the kind of case with I usually used to understand the filter context when I was learning DAX.

1 Like