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
New user
New category old user
Old category old user
Pipeline updates is one of the options. However the provided pipeline us very complex.
Why can’t u use array filters with 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
Thanks Pavel. Your method gave me a good idea. I think I can write the right method now.
Your method surprised me. I never thought I could do it before. Now I have a better understanding of Mongo.
Thanks
Takis.
This topic was automatically closed 5 days after the last reply. New replies are no longer allowed.