Update nested arrays with upsert

I want to update a nested array directly. I want push new object to nested array if Books with field ‘Category’=1 or object of Books not exists. Or if Books with ‘Category’=1 field exists then update BookInfos field in this object.

I cant write right syntax, help plz

{
  "UserId" : 1,
  "Books" : [
    {
      "Category" : 1,
      "BookInfos" : [{Name:xxx,Auth:xxx}]
    }, 
    {
      "Category" : 2,
      "BookInfos" : [{Name:xxx,Auth:xxx}],
    }
  ]
}

Hello

The bellow does
1)Add new book
if no books array,or new category
2)else new member on add bookinfo

*uses pipeline update so need mongodb >=4.2

Change those to the query below,use variables
newBook needs to have this structure for code to work,else change the code also

NewBook

 "newBook": {
                    "Name": "xxx2",
                    "Auth": "xxx2",
                    "Category": 2
                  }

UserID

"q": {
        "UserId": 1
      }

What it does
if books doesnt exists adds books have only the newbooks
else
reduce on the books array,to updated books-array
if category exists
add to bookinfo
else if i found the end and i dindt added yet(new category)
add new-book to books
It does it with one array read

{
  "update": "testcoll",
  "updates": [
    {
      "q": {
        "UserId": 1
      },
      "u": [
        {
          "$addFields": {
            "Books": {
              "$let": {
                "vars": {
                  "newBook": {
                    "Name": "xxx2",
                    "Auth": "xxx2",
                    "Category": 2
                  }
                },
                "in": {
                  "$cond": [
                    {
                      "$not": [
                        {
                          "$ne": [
                            {
                              "$type": "$Books"
                            },
                            "missing"
                          ]
                        }
                      ]
                    },
                    [
                      {
                        "Category": "$$newBook.Category",
                        "BookInfos": [
                          {
                            "Name": "$$newBook.Name",
                            "Auth": "$$newBook.Auth"
                          }
                        ]
                      }
                    ],
                    {
                      "$arrayElemAt": [
                        {
                          "$reduce": {
                            "input": "$Books",
                            "initialValue": [
                              [],
                              0,
                              false
                            ],
                            "in": {
                              "$let": {
                                "vars": {
                                  "booksIndexAdded": "$$value",
                                  "book": "$$this"
                                },
                                "in": {
                                  "$let": {
                                    "vars": {
                                      "books": {
                                        "$arrayElemAt": [
                                          "$$booksIndexAdded",
                                          0
                                        ]
                                      },
                                      "index": {
                                        "$arrayElemAt": [
                                          "$$booksIndexAdded",
                                          1
                                        ]
                                      },
                                      "added": {
                                        "$arrayElemAt": [
                                          "$$booksIndexAdded",
                                          2
                                        ]
                                      }
                                    },
                                    "in": {
                                      "$cond": [
                                        {
                                          "$eq": [
                                            "$$book.Category",
                                            "$$newBook.Category"
                                          ]
                                        },
                                        [
                                          {
                                            "$concatArrays": [
                                              "$$books",
                                              [
                                                {
                                                  "$mergeObjects": [
                                                    "$$book",
                                                    {
                                                      "BookInfos": {
                                                        "$concatArrays": [
                                                          "$$book.BookInfos",
                                                          [
                                                            {
                                                              "Name": "$$newBook.Name",
                                                              "Auth": "$$newBook.Auth"
                                                            }
                                                          ]
                                                        ]
                                                      }
                                                    }
                                                  ]
                                                }
                                              ]
                                            ]
                                          },
                                          {
                                            "$add": [
                                              "$$index",
                                              1
                                            ]
                                          },
                                          true
                                        ],
                                        {
                                          "$cond": [
                                            {
                                              "$and": [
                                                {
                                                  "$eq": [
                                                    "$$index",
                                                    {
                                                      "$subtract": [
                                                        {
                                                          "$size": "$Books"
                                                        },
                                                        1
                                                      ]
                                                    }
                                                  ]
                                                },
                                                {
                                                  "$not": [
                                                    "$$added"
                                                  ]
                                                }
                                              ]
                                            },
                                            [
                                              {
                                                "$concatArrays": [
                                                  "$$books",
                                                  [
                                                    "$$book",
                                                    {
                                                      "Category": "$$newBook.Category",
                                                      "BookInfos": [
                                                        {
                                                          "Name": "$$newBook.Name",
                                                          "Auth": "$$newBook.Auth"
                                                        }
                                                      ]
                                                    }
                                                  ]
                                                ]
                                              },
                                              {
                                                "$add": [
                                                  "$$index",
                                                  1
                                                ]
                                              },
                                              true
                                            ],
                                            [
                                              {
                                                "$concatArrays": [
                                                  "$$books",
                                                  [
                                                    "$$book"
                                                  ]
                                                ]
                                              },
                                              {
                                                "$add": [
                                                  "$$index",
                                                  1
                                                ]
                                              },
                                              "$$added"
                                            ]
                                          ]
                                        }
                                      ]
                                    }
                                  }
                                }
                              }
                            }
                          }
                        },
                        0
                      ]
                    }
                  ]
                }
              }
            }
          }
        }
      ],
      "upsert": true,
      "multi": true
    }
  ]
}

Before update
Screenshot from 2020-12-25 14-51-32

New user
NewUser

New category old user
NewCategoryOlduser

Old category old user
OldCategoryOldUser

Hi @Takis and @grape_Ye,

Pipeline updates is one of the options. However the provided pipeline us very complex.

Why can’t u use array filters with upsert?

https://docs.mongodb.com/manual/reference/operator/update/positional-filtered/#upsert

Best
Pavel

I know,if you can help sent a smaller query,update operators must be enough here.
I am testing pipeline updates and i will try to make a function to generate the MQL code for nested updates,but its not done yet(like simplicity of arrayfilters but in pipeline)

This is the same query but with filters,its less code(~1/2) but reads the array 2 times.
I tested it seems to work also i think

Books are filtered 2 times
book-same-category (filter …)
books-different category (filter …)

If not same category found the new book is added to the books
If same category found then the bookInfos is updated and added to books-different-category

Its also pipeline update

{
  "update": "testcoll",
  "updates": [
    {
      "q": {
        "UserId": 1
      },
      "u": [
        {
          "$addFields": {
            "Books": {
              "$let": {
                "vars": {
                  "newBook": {
                    "Name": "xxx2",
                    "Auth": "xxx2",
                    "Category": 3
                  }
                },
                "in": {
                  "$cond": [
                    {
                      "$not": [
                        {
                          "$ne": [
                            {
                              "$type": "$Books"
                            },
                            "missing"
                          ]
                        }
                      ]
                    },
                    [
                      {
                        "Category": "$$newBook.Category",
                        "BookInfos": [
                          {
                            "Name": "$$newBook.Name",
                            "Auth": "$$newBook.Auth"
                          }
                        ]
                      }
                    ],
                    {
                      "$let": {
                        "vars": {
                          "sameCategoryBook": {
                            "$arrayElemAt": [
                              {
                                "$filter": {
                                  "input": "$Books",
                                  "as": "book",
                                  "cond": {
                                    "$eq": [
                                      "$$newBook.Category",
                                      "$$book.Category"
                                    ]
                                  }
                                }
                              },
                              0
                            ]
                          },
                          "differentCategoryBooks": {
                            "$filter": {
                              "input": "$Books",
                              "as": "book",
                              "cond": {
                                "$ne": [
                                  "$$newBook.Category",
                                  "$$book.Category"
                                ]
                              }
                            }
                          }
                        },
                        "in": {
                          "$cond": [
                            {
                              "$not": [
                                {
                                  "$ne": [
                                    {
                                      "$type": "$$sameCategoryBook"
                                    },
                                    "missing"
                                  ]
                                }
                              ]
                            },
                            {
                              "$concatArrays": [
                                "$Books",
                                [
                                  {
                                    "Category": "$$newBook.Category",
                                    "BookInfos": [
                                      {
                                        "Name": "$$newBook.Name",
                                        "Auth": "$$newBook.Auth"
                                      }
                                    ]
                                  }
                                ]
                              ]
                            },
                            {
                              "$concatArrays": [
                                "$$differentCategoryBooks",
                                [
                                  {
                                    "$mergeObjects": [
                                      "$$sameCategoryBook",
                                      {
                                        "BookInfos": {
                                          "$concatArrays": [
                                            "$$sameCategoryBook.BookInfos",
                                            [
                                              {
                                                "Name": "$$newBook.Name",
                                                "Auth": "$$newBook.Auth"
                                              }
                                            ]
                                          ]
                                        }
                                      }
                                    ]
                                  }
                                ]
                              ]
                            }
                          ]
                        }
                      }
                    }
                  ]
                }
              }
            }
          }
        }
      ],
      "upsert": true,
      "multi": true
    }
  ]
}

Hi guys,

I used to prepare another approach for a different data model, but try to use something of this sort:

db.sample.update({books: {$elemMatch: {Category: "<FILTER_FIELD_KEY>", BookInfos: "<FILTER_FIELD_VALUE>"}}}, 
                   [{$addFields: {input: {$zip: {inputs: ["$books", [{Category: "<TARGET_FIELD_KEY>", BooksInfos: {...} }]]}}}}, 
                    {$set: {books: {"$arrayElemAt": ["$input", 0]}}}, 
                    {$project: {input : 0}}
                   ]
                  );

The Idea is to zip the array with the nee element and get a sort of upsert to an array.

Let me know if you need a more specific examples.

Thanks
Pavel

1 Like

Thanks Pavel. Your method gave me a good idea. I think I can write the right method now.

1 Like

Your method surprised me. I never thought I could do it before. Now I have a better understanding of Mongo.
Thanks
Takis.

1 Like

This topic was automatically closed 5 days after the last reply. New replies are no longer allowed.