MongoDB update a document's array element at specific index and insert a new document when there's no match

Question

UPDATE: the real purpose of this question is to find a FAST solution, I'm new to MongoDB so I thought it's fast if use a single query, however, any fast solution is okay.

Basically I'm looking for a solution to solve the following problem. Since there're hundreds of such operations at each moment, the operation needs to be FAST.

So I have a curves collection with documents like this:

{
    curveId: 12,
    values: [1, 2, 12, 7, ...]
}

Suppose I'd like to set a curve value at index 3 and result in:

{
    curveId: 12,
    values: [1, 2, 12, new_value, ...]
}

in case of no matched curveId, a new curve is created:

{
    curveId: 12,
    values: [null, null, null, new_value]
}

so I write this upsert query:

db.curves.update({
    curveId: 12
},{
    $set: { "values.3": new_value }
},{
    upsert: true
})

This works when there's a matched document. However, if there's no match, it will create a new document like this:

{
    curveId: 12,
    values: { '3': new_value }
}

The values is not an array, not what I was expecting.

I've googled quite some time but found no solution yet. Is it even possible to solve the problem with one query?

Thank you.

Answer 1

This alternate uses $map every time, with $concatArrays and $slice. It has the advantage of being concise and much cleaner than my previous solution. And there's no real performance impact of using $map when document retrieval has already occurred.

Like before, an array of "values or nulls" is created which either fills in, creates, or pads null values up to the size requirement with the given index. Using $zip and $first: "$$this", it results in an array which either has values from values or null when missing or padded.

db.collection.update({ curveId: 12 },
[
  {
    $set: {
      values: {
        $let: {
          vars: {
            // index & new value to set
            idx: 3,
            new_val: 1000,
            vals_nulls: {
              $map: {
                input: {
                  $zip: {
                    inputs: [
                      { $ifNull: ["$values", []] },
                      { $range: [0, 3] }  // repeat index here :-(
                    ],
                    useLongestLength: true
                  }
                },
                in: { $first: "$$this" }
              }
            }
          },
          in: {
            $concatArrays: [
              { $slice: ["$$vals_nulls", "$$idx"] },
              ["$$new_val"],
              {
                $slice: [
                  "$$vals_nulls",
                  { $add: ["$$idx", 1] },
                  { $add: [{ $size: "$$vals_nulls" }, 1] }
                ]
              }
            ]
          }
        }
      }
    }
  }
],
{ upsert: true, multi: true }
)

Mongo Playground

Mongo Playground with index=0