I have a document with format like this:
{
"f1": "v1",
"f2": {
"id": 1,
"sub": "subv",
"updatedAt": 123
}
}
I have an another source that give me a inputf2
object.
I want to write an upsert query to find document with matching filter {"f2.id": inputf2.id}
. I̶f̶ ̶f̶o̶u̶n̶d̶ ̶a̶n̶d̶ ̶i̶f̶ ̶f̶2̶.̶u̶p̶d̶a̶t̶e̶d̶A̶t̶ ̶<̶ ̶i̶n̶p̶u̶t̶f̶2̶.̶u̶p̶d̶a̶t̶e̶d̶A̶t̶ ̶t̶h̶e̶n̶ ̶u̶p̶d̶a̶t̶e̶ ̶w̶h̶o̶l̶e̶ ̶f̶2̶ ̶t̶o̶ ̶i̶n̶p̶u̶t̶f̶2̶.̶ ̶O̶t̶h̶e̶r̶w̶i̶s̶e̶,̶ ̶i̶n̶s̶e̶r̶t̶ ̶a̶ ̶n̶e̶w̶ ̶d̶o̶c̶u̶m̶e̶n̶t̶ ̶w̶i̶t̶h̶ ̶n̶e̶w̶ ̶f̶i̶e̶l̶d̶ ̶f̶1̶ ̶(̶v̶1̶ ̶i̶s̶ ̶a̶ ̶h̶a̶r̶d̶c̶o̶d̶e̶d̶ ̶v̶a̶l̶u̶e̶)̶.
Edit clearer logic:
- If found the id AND f2.updatedAt < inputf2.updatedAt => update whole f2 to inputf2.
- If found the id AND f2.updatedAt >= inputf2.updatedAt => do nothing (no update f2, no create new document).
- If not found the id => create new document with new field f1 (v1 is a hardcoded value)
Example:
Input:
{"id": 1,"sub": "newsubv","updatedAt": 100}
Because 100 < 123. id = 1 will not be updated. Output:
{
"f1": "v1",
"f2": {"id": 1,"sub": "subv","updatedAt": 123}
}
Input:
{"id": 1,"sub": "newsubv","updatedAt": 150}
Because 150 > 123. id = 1 will be updated. Output:
{
"f1": "v1",
"f2": {"id": 1,"sub": "newsubv","updatedAt": 150}
}
Input:
{"id": 2,"sub": "newsubv","updatedAt": 100}
Because input id = 2 is not found in db. It will be inserted whatever updatedAt
is. Output:
{
"f1": "v1",
"f2": {"id": 1,"sub": "subv","updatedAt": 123}
},
{
"f1": "v1",
"f2": {"id": 2,"sub": "newsubv","updatedAt": 100}
}
I tried with both 2 types of update, update document or update aggregattion pipeline, but seem that any of them fit my requirement.
With update document
I can use $setOnInsert
but can’t set f2
with f2.updatedAt < inputf2.updatedAt
condition:
db.collection.update({
"f2.id": "1"
},
{
"$set": {
// Can not check updatedAt = 100 < 123 and then do nothing
"f2": {
"id": 1
"sub": "newsubv",
"updatedAt": 100
}
},
"$setOnInsert": {
"f1": "v1"
}
},
{"upsert": true});
With update aggregation pipeline
I can update f2
with f2.updatedAt < inputf2.updatedAt
condition with a bit tricky by cloning f2
to new field oldf2
, then apply the condition and remove oldf2
. But in aggregation, there is no $setOnItem
function:
db.test.updateOne(
{"f2.id": 1},
[
{$set: {"oldf2": "$f2"}},
{
$set: {
"f2": {
$cond: [{$lt: ["$f2.updatedAt", 100]}, {
"id": 1,
"sub": "newsubv",
"updatedAt": 100
}, "$oldf2"]
}
}
},
{$set: {"oldf2": "$$REMOVE"}},
// How to apply something like $setOnInsert function?
],
{"upsert":true})
I must use updateOne
because there is a list of f2
item and I want to batch update these items in bulkWrite
.
Noted: f2.id
is an unique field in collection
Can anyone help me to write query for this logic? Thank you guys very much.
2
Answers
Finally, I can write the query. Here is my solution:
Testcase:
Input 1: Mongo Playground
Input 2: Mongo Playground
Input 3: Mongo Playground
You can use the first method almost as you did, just with a condition.
Edit: But if you want to change
f2.id
in case of insert, you should separate thef2
, like this:You can see it on the playground
And you can play with the
inputf2.updatedAt
to be 100 or 150 to see both cases that I pasted here:If
f2.updatedAt
>=inputf2.updatedAt
, we will get two different documents on the db. The new one will haveinputf2.updatedAt
, but with new id, as this should be a unique id:Otherwise,
f2.updatedAt
<inputf2.updatedAt
: the document on the db will be updated, but will keep its original id:BTW, there is no problem to use this method inside a bulk update