I have the following structure:
each document has a level_1 array of documents with a nested level_2 array
I would like to batch update each of the level_2 documents (for one or multiple top level documents) with a new_field that references the value field in the same document (e.g. new_field = value * 2 ).
I tried to update with arrayFilters, which does not seem to work in the nested scenario. I tried $map, without success.
BTW, I am very new to this
It would be helpful if you could provide more sample documents, examples of the batched updates you want to perform and the expected results.
Most likely the solution involves update with aggregation that uses a $map for each level of arrays.
1 Like
Steeve,
thanks for the reply. What I am trying to achieve is a bit complex:
I have a collection of races, which came from another database.
I also have a collection of riders:
You can see that there is a nested array of (rider) entries, which lives inside an array of (race) sessions, both inside the overall race document.
I would like to automate a process, which can replace the rd_id field inside the nested array with the _id of the corresponding rider from the rider collection (for all entries in all sessions for every race).
I can create a Node script that can loop through races, sessions, and entries that can achieve this, but I was wondering whether I am missing a clever way to go about it.
Thank you
Panos
To be able to experiment with your use-case we need documents that we can cut-n-paste directly into our own collections. We cannot work with a screenshot of documents.
The reason you cannot use arrayFilters isn’t because of multiple levels of nesting (it works with that) but because you want to set the value to something that references another field in the document. That can only be done with aggregation expressions so you need pipeline syntax for this.
Given a document:
{ l1: [
{ l2: [ { value: 1}, {value: 2} ] }
] }
You can do this update to double every value
db.coll.update({}, [ {$map:{
input: "$l1", as: "l1", in: {$map:{
input: "$$l1.l2", as: "l2", in: {$mergeObjects:[
"$$l2",
{value: {$multiply:[ 2, "$$l2.value"] }}
]}
}}
}} ], {multi:true})
If you don’t want to update every element, you would add a conditional $cond expression and then either pass on $$l1 (or $$l2) or the replacement object.
HTH,
Asya
1 Like
Valid point, clearly new to this.
I am attaching a trimmed down version of the races and riders collections.
Thanks
Panos
races:
[{
"_id": {
"$oid": "654833cc0f0fd00429be14d1"
},
"gp_id": 114,
"date": {
"$date": "2020-07-19T04:00:00.000Z"
},
"sessions": [
{
"date": {
"$date": "2020-07-17T04:00:00.000Z"
},
"entries": [
{
"rd_id": 9
},
{
"rd_id": 7
},
{
"rd_id": 2
},
{
"rd_id": 22
},
{
"rd_id": 17
},
{
"rd_id": 21
},
{
"rd_id": 3
},
{
"rd_id": 10
},
{
"rd_id": 18
},
{
"rd_id": 14
},
{
"rd_id": 30
},
{
"rd_id": 6
},
{
"rd_id": 12
},
{
"rd_id": 15
},
{
"rd_id": 25
},
{
"rd_id": 32
},
{
"rd_id": 23
},
{
"rd_id": 4
},
{
"rd_id": 31
},
{
"rd_id": 26
},
{
"rd_id": 13
},
{
"rd_id": 16
}
]
},
{
"date": {
"$date": "2020-07-17T04:00:00.000Z"
},
"entries": [
{
"rd_id": 9
},
{
"rd_id": 7
},
{
"rd_id": 2
},
{
"rd_id": 22
},
{
"rd_id": 17
},
{
"rd_id": 21
},
{
"rd_id": 3
},
{
"rd_id": 10
},
{
"rd_id": 18
},
{
"rd_id": 14
},
{
"rd_id": 30
},
{
"rd_id": 6
},
{
"rd_id": 12
},
{
"rd_id": 15
},
{
"rd_id": 25
},
{
"rd_id": 32
},
{
"rd_id": 23
},
{
"rd_id": 4
},
{
"rd_id": 31
},
{
"rd_id": 26
},
{
"rd_id": 13
},
{
"rd_id": 16
}
]
},
{
"date": {
"$date": "2020-07-18T04:00:00.000Z"
},
"entries": [
{
"rd_id": 9
},
{
"rd_id": 7
},
{
"rd_id": 2
},
{
"rd_id": 22
},
{
"rd_id": 17
},
{
"rd_id": 21
},
{
"rd_id": 3
},
{
"rd_id": 10
},
{
"rd_id": 18
},
{
"rd_id": 14
},
{
"rd_id": 30
},
{
"rd_id": 6
},
{
"rd_id": 12
},
{
"rd_id": 15
},
{
"rd_id": 25
},
{
"rd_id": 32
},
{
"rd_id": 23
},
{
"rd_id": 4
},
{
"rd_id": 31
},
{
"rd_id": 26
},
{
"rd_id": 13
},
{
"rd_id": 16
}
]
},
{
"date": {
"$date": "2020-07-18T04:00:00.000Z"
},
"entries": [
{
"rd_id": 9
},
{
"rd_id": 7
},
{
"rd_id": 2
},
{
"rd_id": 22
},
{
"rd_id": 17
},
{
"rd_id": 21
},
{
"rd_id": 3
},
{
"rd_id": 10
},
{
"rd_id": 18
},
{
"rd_id": 14
},
{
"rd_id": 30
},
{
"rd_id": 6
},
{
"rd_id": 12
},
{
"rd_id": 15
},
{
"rd_id": 25
},
{
"rd_id": 32
},
{
"rd_id": 23
},
{
"rd_id": 4
},
{
"rd_id": 31
},
{
"rd_id": 26
},
{
"rd_id": 13
},
{
"rd_id": 16
}
]
},
{
"date": {
"$date": "2020-07-18T04:00:00.000Z"
},
"entries": [
{
"rd_id": 21
},
{
"rd_id": 30
},
{
"rd_id": 6
},
{
"rd_id": 12
},
{
"rd_id": 15
},
{
"rd_id": 25
},
{
"rd_id": 32
},
{
"rd_id": 23
},
{
"rd_id": 4
},
{
"rd_id": 31
},
{
"rd_id": 26
},
{
"rd_id": 16
}
]
},
{
"date": {
"$date": "2020-07-18T04:00:00.000Z"
},
"entries": [
{
"rd_id": 9
},
{
"rd_id": 7
},
{
"rd_id": 2
},
{
"rd_id": 22
},
{
"rd_id": 17
},
{
"rd_id": 21
},
{
"rd_id": 3
},
{
"rd_id": 10
},
{
"rd_id": 18
},
{
"rd_id": 14
},
{
"rd_id": 13
},
{
"rd_id": 16
}
]
},
{
"date": {
"$date": "2020-07-19T04:00:00.000Z"
},
"entries": [
{
"rd_id": 9
},
{
"rd_id": 7
},
{
"rd_id": 2
},
{
"rd_id": 22
},
{
"rd_id": 17
},
{
"rd_id": 21
},
{
"rd_id": 3
},
{
"rd_id": 10
},
{
"rd_id": 18
},
{
"rd_id": 14
},
{
"rd_id": 30
},
{
"rd_id": 6
},
{
"rd_id": 12
},
{
"rd_id": 15
},
{
"rd_id": 25
},
{
"rd_id": 32
},
{
"rd_id": 23
},
{
"rd_id": 4
},
{
"rd_id": 31
},
{
"rd_id": 26
},
{
"rd_id": 13
}
]
},
{
"date": {
"$date": "2020-07-19T04:00:00.000Z"
},
"entries": [
{
"rd_id": 9
},
{
"rd_id": 7
},
{
"rd_id": 2
},
{
"rd_id": 22
},
{
"rd_id": 17
},
{
"rd_id": 21
},
{
"rd_id": 3
},
{
"rd_id": 10
},
{
"rd_id": 18
},
{
"rd_id": 14
},
{
"rd_id": 30
},
{
"rd_id": 6
},
{
"rd_id": 12
},
{
"rd_id": 15
},
{
"rd_id": 25
},
{
"rd_id": 32
},
{
"rd_id": 23
},
{
"rd_id": 4
},
{
"rd_id": 31
},
{
"rd_id": 26
}
]
}
]
},
{
"_id": {
"$oid": "654833cc0f0fd00429be14d2"
},
"gp_id": 115,
"date": {
"$date": "2020-07-26T04:00:00.000Z"
},
"sessions": [
{
"date": {
"$date": "2020-07-24T04:00:00.000Z"
},
"entries": [
{
"rd_id": 9
},
{
"rd_id": 7
},
{
"rd_id": 22
},
{
"rd_id": 18
},
{
"rd_id": 25
},
{
"rd_id": 10
},
{
"rd_id": 17
},
{
"rd_id": 12
},
{
"rd_id": 30
},
{
"rd_id": 14
},
{
"rd_id": 6
},
{
"rd_id": 21
},
{
"rd_id": 13
},
{
"rd_id": 3
},
{
"rd_id": 4
},
{
"rd_id": 15
},
{
"rd_id": 26
},
{
"rd_id": 23
},
{
"rd_id": 32
},
{
"rd_id": 16
},
{
"rd_id": 31
}
]
},
{
"date": {
"$date": "2020-07-24T04:00:00.000Z"
},
"entries": [
{
"rd_id": 9
},
{
"rd_id": 7
},
{
"rd_id": 22
},
{
"rd_id": 18
},
{
"rd_id": 25
},
{
"rd_id": 10
},
{
"rd_id": 17
},
{
"rd_id": 12
},
{
"rd_id": 30
},
{
"rd_id": 14
},
{
"rd_id": 6
},
{
"rd_id": 21
},
{
"rd_id": 13
},
{
"rd_id": 3
},
{
"rd_id": 4
},
{
"rd_id": 15
},
{
"rd_id": 26
},
{
"rd_id": 23
},
{
"rd_id": 32
},
{
"rd_id": 16
},
{
"rd_id": 31
}
]
},
{
"date": {
"$date": "2020-07-25T04:00:00.000Z"
},
"entries": [
{
"rd_id": 9
},
{
"rd_id": 7
},
{
"rd_id": 22
},
{
"rd_id": 18
},
{
"rd_id": 25
},
{
"rd_id": 10
},
{
"rd_id": 17
},
{
"rd_id": 12
},
{
"rd_id": 30
},
{
"rd_id": 14
},
{
"rd_id": 6
},
{
"rd_id": 21
},
{
"rd_id": 13
},
{
"rd_id": 3
},
{
"rd_id": 4
},
{
"rd_id": 15
},
{
"rd_id": 26
},
{
"rd_id": 23
},
{
"rd_id": 32
},
{
"rd_id": 31
},
{
"rd_id": 2
}
]
},
{
"date": {
"$date": "2020-07-25T04:00:00.000Z"
},
"entries": [
{
"rd_id": 9
},
{
"rd_id": 7
},
{
"rd_id": 22
},
{
"rd_id": 18
},
{
"rd_id": 25
},
{
"rd_id": 10
},
{
"rd_id": 17
},
{
"rd_id": 12
},
{
"rd_id": 30
},
{
"rd_id": 14
},
{
"rd_id": 6
},
{
"rd_id": 21
},
{
"rd_id": 13
},
{
"rd_id": 3
},
{
"rd_id": 4
},
{
"rd_id": 15
},
{
"rd_id": 26
},
{
"rd_id": 23
},
{
"rd_id": 32
},
{
"rd_id": 16
},
{
"rd_id": 31
},
{
"rd_id": 2
}
]
},
{
"date": {
"$date": "2020-07-25T04:00:00.000Z"
},
"entries": [
{
"rd_id": 25
},
{
"rd_id": 10
},
{
"rd_id": 13
},
{
"rd_id": 3
},
{
"rd_id": 4
},
{
"rd_id": 15
},
{
"rd_id": 26
},
{
"rd_id": 23
},
{
"rd_id": 32
},
{
"rd_id": 16
},
{
"rd_id": 31
},
{
"rd_id": 2
}
]
},
{
"date": {
"$date": "2020-07-25T04:00:00.000Z"
},
"entries": [
{
"rd_id": 9
},
{
"rd_id": 7
},
{
"rd_id": 22
},
{
"rd_id": 18
},
{
"rd_id": 25
},
{
"rd_id": 10
},
{
"rd_id": 17
},
{
"rd_id": 12
},
{
"rd_id": 30
},
{
"rd_id": 14
},
{
"rd_id": 6
},
{
"rd_id": 21
}
]
},
{
"date": {
"$date": "2020-07-26T04:00:00.000Z"
},
"entries": [
{
"rd_id": 9
},
{
"rd_id": 7
},
{
"rd_id": 22
},
{
"rd_id": 18
},
{
"rd_id": 25
},
{
"rd_id": 10
},
{
"rd_id": 17
},
{
"rd_id": 12
},
{
"rd_id": 30
},
{
"rd_id": 14
},
{
"rd_id": 6
},
{
"rd_id": 21
},
{
"rd_id": 13
},
{
"rd_id": 3
},
{
"rd_id": 4
},
{
"rd_id": 15
},
{
"rd_id": 26
},
{
"rd_id": 23
},
{
"rd_id": 32
},
{
"rd_id": 16
},
{
"rd_id": 31
}
]
},
{
"date": {
"$date": "2020-07-26T04:00:00.000Z"
},
"entries": [
{
"rd_id": 9
},
{
"rd_id": 7
},
{
"rd_id": 22
},
{
"rd_id": 18
},
{
"rd_id": 25
},
{
"rd_id": 10
},
{
"rd_id": 17
},
{
"rd_id": 12
},
{
"rd_id": 30
},
{
"rd_id": 14
},
{
"rd_id": 6
},
{
"rd_id": 21
},
{
"rd_id": 13
},
{
"rd_id": 3
},
{
"rd_id": 4
},
{
"rd_id": 15
},
{
"rd_id": 26
},
{
"rd_id": 23
},
{
"rd_id": 32
},
{
"rd_id": 16
},
{
"rd_id": 31
}
]
}
]
}]
riders:
[{
"_id": {
"$oid": "652c4d762a82567a6ee7a232"
},
"rd_id": 9,
"name": "Fabio Quartararo"
},
{
"_id": {
"$oid": "652c4d762a82567a6ee7a22c"
},
"rd_id": 3,
"name": "Andrea Dovizioso"
},
{
"_id": {
"$oid": "652c4d762a82567a6ee7a22d"
},
"rd_id": 4,
"name": "Johann Zarco"
},
{
"_id": {
"$oid": "652c4d762a82567a6ee7a242"
},
"rd_id": 25,
"name": "Miguel Oliveira"
},
{
"_id": {
"$oid": "652c4d762a82567a6ee7a237"
},
"rd_id": 14,
"name": "Joan Mir"
},
{
"_id": {
"$oid": "652c4d762a82567a6ee7a247"
},
"rd_id": 30,
"name": "Brad Binder"
},
{
"_id": {
"$oid": "652c4d762a82567a6ee7a249"
},
"rd_id": 32,
"name": "Bradley Smith"
},
{
"_id": {
"$oid": "652c4d762a82567a6ee7a22e"
},
"rd_id": 5,
"name": "Stefan Bradl"
},
{
"_id": {
"$oid": "652c4d762a82567a6ee7a22b"
},
"rd_id": 2,
"name": "Marc Marquez"
},
{
"_id": {
"$oid": "652c4d762a82567a6ee7a23b"
},
"rd_id": 18,
"name": "Valentino Rossi"
},
{
"_id": {
"$oid": "652c4d762a82567a6ee7a236"
},
"rd_id": 13,
"name": "Cal Crutchlow"
},
{
"_id": {
"$oid": "652c4d762a82567a6ee7a243"
},
"rd_id": 26,
"name": "Iker Lecuona"
},
{
"_id": {
"$oid": "652c4d762a82567a6ee7a24a"
},
"rd_id": 33,
"name": "Lorenzo Savadori"
},
{
"_id": {
"$oid": "652c4d762a82567a6ee7a271"
},
"rd_id": 72,
"name": "Garrett Gerloff"
},
{
"_id": {
"$oid": "652c4d762a82567a6ee7a238"
},
"rd_id": 15,
"name": "Aleix Espargaro"
},
{
"_id": {
"$oid": "652c4d762a82567a6ee7a239"
},
"rd_id": 16,
"name": "Alex Rins"
},
{
"_id": {
"$oid": "652c4d762a82567a6ee7a241"
},
"rd_id": 24,
"name": "Mika Kallio"
},
{
"_id": {
"$oid": "652c4d762a82567a6ee7a23f"
},
"rd_id": 22,
"name": "Francesco Bagnaia"
},
{
"_id": {
"$oid": "652c4d762a82567a6ee7a248"
},
"rd_id": 31,
"name": "Alex Marquez"
},
{
"_id": {
"$oid": "652c4d762a82567a6ee7a22f"
},
"rd_id": 6,
"name": "Danilo Petrucci"
},
{
"_id": {
"$oid": "652c4d762a82567a6ee7a244"
},
"rd_id": 27,
"name": "Michele Pirro"
},
{
"_id": {
"$oid": "652c4d762a82567a6ee7a230"
},
"rd_id": 7,
"name": "Maverick Viñales"
},
{
"_id": {
"$oid": "652c4d762a82567a6ee7a235"
},
"rd_id": 12,
"name": "Takaaki Nakagami"
},
{
"_id": {
"$oid": "652c4d762a82567a6ee7a23e"
},
"rd_id": 21,
"name": "Pol Espargaro"
},
{
"_id": {
"$oid": "652c4d762a82567a6ee7a233"
},
"rd_id": 10,
"name": "Franco Morbidelli"
},
{
"_id": {
"$oid": "652c4d762a82567a6ee7a23a"
},
"rd_id": 17,
"name": "Jack Miller"
},
{
"_id": {
"$oid": "652c4d762a82567a6ee7a240"
},
"rd_id": 23,
"name": "Tito Rabat"
}]
Thanks.
If I understand correctly, you want to replace rd_id in all entries of all sessions in the races collection with the _id of the document with the same rd_id from the riders collection. For examples, you want
sessions.0.entries.0 : { rd_id : 9 /* other fields */ } ,
sessions.0.entries.1 : { rd_id : 7 /* other fields */ }
/* other entries and sessions */
to becomes
sessions.0.entries.0 : { _id : 652c4d762a82567a6ee7a232 /* other fields */ }
sessions.0.entries.1 : { _id : 652c4d762a82567a6ee7a22c /* other fields */ }
/* other entries and sessions */
One question.
Is that a one time data conversion use-case or a regular operational use-case?
I guess because there would be 2 ways to do it.
1 . simple and slow using 2 $unwind stages with $group that ends with a $merge
2 . more complex but faster using a $lookup with 2 $map stages that also ends with a $merge
Steeve,
this is supposed to be used intermittently, so it can be slow, as long as it’s accurate
Thanks much
Panos
I am trying it in Mongosh and it complains that $map is an unrecognized pipeline stage name…
Here is a partial slow solution. I am out of time for today as I am not too sure how to $group the 2 level of arrays.
{ '$unwind': '$sessions' },
{ '$unwind': '$sessions.entries' },
{
'$lookup': {
from: 'riders',
localField: 'sessions.entries.rd_id',
foreignField: 'rd_id',
as: '_tmp.riders'
}
},
{ '$unwind': '_tmp.riders' },
{ "$set" : {
"sessions.entries" : { "$mergeObjects" : [
"$sessions.entries" , { _id : "$_tmp.riders._id" }
] }
} } ,
{ "$group" : /* ... not sure how to do it yet */ }
{ "$unset" : "_tmp" }
{ "$merge" : /* ... to be completed */ }
I reached a dead end with the (not so) simple and slow solution.
I have no clue on how to use $group to reconstruct the 2 level of arrays.
So here is something that seems to work:
pipeline = []
/* same lookup as above but without $unwind */
lookup_riders = {
'$lookup': {
from: 'riders',
localField: 'sessions.entries.rd_id',
foreignField: 'rd_id',
as: '_tmp.riders'
}
}
pipeline.push( lookup_riders )
/* The meat of the solution in a big stew */
set_sessions = { "$set": {
"_tmp.sessions": { "$map": {
"input": "$sessions",
"as": "session",
"in": { "$mergeObjects": [
"$$session" ,
{ "entries" : { "$map" : {
"input" : "$$session.entries" ,
"as" : "entry" ,
"in" : { "$mergeObjects" : [
"$$entry" ,
{ "_id" : { "$reduce" : {
"input" : "$_tmp.riders" ,
"initialValue" : null ,
"in" : { "$cond" : [
{ "$eq" : [ "$$entry.rd_id" , "$$this.rd_id" ] } ,
"$$this._id" ,
"$$value"
] }
} } }
] }
} } }
] }
} }
} }
pipeline.push( set_sessions )
Note, I put the results in _tmp because it is easier to debug when you do not overwrite right away the original values. So what remains to do is to replace the original sessions with _tmp.sessions with
{ "$set" : { "sessions" : "$_tmp.sessions" } }
and the clean up with
{ "$unset" : [ "_tmp" , "sessions.entries.rd_id" ] }
For those who hate big code stew my real code uses functions to divide the code in more manageable units.
function pipeline() {
return [
lookup_riders() ,
set_tmp_sessions() ,
replace_sessions() ,
cleanup()
] ;
}
function lookup_riders() {
return { "$lookup": {
"from": 'riders',
"localField": "sessions.entries.rd_id",
"foreignField": "rd_id",
"as": "_tmp.riders"
} } ;
}
function set_tmp_sessions() {
return { "$set" : {
"_tmp.sessions" : map_sessions()
} }
}
function map_sessions() {
return { "$map" : {
"input" : "$sessions" ,
"as" : "session" ,
"in" : { "$mergeObjects" : [
"$$session" ,
{ "entries" : map_entries() }
] }
} } ;
}
function map_entries() {
return { "$map" : {
"input" : "$$session.entries" ,
"as" : "entry" ,
"in" : { "$mergeObjects" : [
"$$entry" ,
{ "_id" : reduce_riders() }
] }
} } ;
}
function reduce_riders() {
return { "$reduce" : {
"input" : "$_tmp.riders" ,
"initialValue" : null ,
"in" : { "$cond" : [
{ "$eq" : [ "$$entry.rd_id" , "$$this.rd_id" ] } ,
"$$this._id" ,
"$$value"
] }
} } ;
}
function replace_sessions() {
return { "$set" : { "sessions" : "$_tmp.sessions" } } ;
}
function cleanup() {
return { "$unset" : [ "_tmp" , "sessions.entries.rd_id" ] } ;
}
db.races.aggregate( pipeline() )
This way curly braces are easier to matched. When I make a mistake in one function, I simply edit a smaller part of code rather than the big stew.
I recommend that you $out into a temporary collection, check the result and then $merge into the original.
AT YOUR OWN RISK
1 Like
Worked great. Thanks
1 Like
Cool. Another happy customer.
By the way I have an idea on how to group back 2 level of arrays.
You $unwind using includeArrayIndex:_index option on the top level array.
You would then $group with _id:{_id:$_id,index:"$_index}, using $first:$$ROOT and $push for you sub-level array elements. You end up with 1 group for each top level entries.
And to terminate another $group with _id:{$_id._id} using $first again with $push for the top-level array elements each element being the array you created in the previous $group.
1 Like
This topic was automatically closed 5 days after the last reply. New replies are no longer allowed.