Add minimum available key to dictionary MongoDB

Question

I have documents in collection which have structure:

{key: {0: 2, 1: 5, 2: 100, 4: 15}}

I need to add pair key: value to dictionary. value may be anything. It doesn’t matter. But key must be the smallest integer that is not used in the dictionary as a key (starts from 0).

I can do it using python.

document = {key: {0: 2, 1: 5, 2: 100, 4: 15}}
new_key = 0  # key to insert in our dict
for i in range(max(document['key'].keys()) + 2):
    if i not in document['key'].keys():
        new_key = i

# update the document
document['key'][new_key] = value  # value may be anything

How to do it using MongoDB? Is it possible?

Answer 1

Here is a possibility (requires Mongo 4.2 or better):

// { key: { 0: 2, 1: 5, 2: 100, 4: 15 } }
db.collection.update(
  {},
  [
    { $set: {
      newkey: {
        $toString: {
          $min: {
            $setDifference: [
              { $range: [0, 10] },
              { $map: {
                input: { $objectToArray: "$key" },
                as: "x",
                in: { $toInt: "$$x.k" }
              }}
            ]
          }
        }
      }
    }},
    { $set: {
      key: {
        $arrayToObject: {
          $concatArrays: [
            { $objectToArray: "$key" },
            [{ k: "$newkey", v: 1000 }]
          ]
        }
      }
    }},
    { $unset: "newkey" }
])
// { key: { 0: 2, 1: 5, 2: 100, 4: 15, 3: 1000 } }

Note that we're using an update with an aggregation pipeline (symbolised by the square brackets in update({}, [...])) which is available starting in Mongo 4.2 and without which we can't update a document based on its own content.

The first $set stage is there to create a temporary value which is the new key (in our case 3) for which to add the new value:

• It starts by transforming the key object into an array of { k: "key", v: "value" } objects for an easy access to keys (see $objectToArray):

  { $objectToArray: "$key" }
  // { key: [{ k: "0", v: 2 }, { k: "1", v: 5 }, { k: "2", v: 100 }, { k: "4", v: 15 }] }
  

• That we then transform into an array of integers out of the k keys (see $map and $toInt):

  { $map: { input: { ... }, as: "x", in: { $toInt: "$$x.k" } } }
  // [ 0, 1, 2, 4 ]
  

• That we then intersect with a range between 0 and whatever (here 10) (see $setDifference and $range):

  $setDifference: [{ $range: [0, 10] }, {...}]
  // [ 3, 5, 6, 7, 8, 9 ]
  

  • but if you'd rather have an upper range based on the array's max key, it's possible too): just replace 10 with:

    { $max: { $map: { input: { $objectToArray: "$key" }, as: "x", in: { $toInt: "$$x.k" } } } }
    // 4
    

• From which we can extract the min:

  $min: { ... }
  // 3
  

• That we cast to string:

  $toString: { ... }
  // "3"
  

• Such that the intermediate version of the document becomes:

  { key: { 0: 2, 1: 5, 2: 100, 4: 15 }, newkey: "3" }
  

The second $set stage replaces the key object with the existing key object appended with the new key value:

• It starts by, again, transforming the key object in an array of { k: "key", v: "value" } objects:

  { $objectToArray: "$key" }
  // { key: [{ k: "0", v: 2 }, { k: "1", v: 5 }, { k: "2", v: 100 }, { k: "4", v: 15 }] }
  

• In order to append the new key/value through a $concatArrays operation:

  $concatArrays: [{...}, [{ k: "$newkey", v: 1000 }]]
  // { key: [{ k: "0", v: 2 }, { k: "1", v: 5 }, { k: "2", v: 100 }, { k: "4", v: 15 }, { k: "3", v: 1000 }] }
  

• Array that we can finally transform back into an object (see $arrayToObject):

  $arrayToObject: { ... }
  // { 0: 2, 1: 5, 2: 100, 4: 15, 3: 1000 }
  

The very last stage, the $unset stage, is just there to remove the intermediate variable newkey.

Note that it's possible to plug-in the first $set stage within the second one, by replacing "$newkey" with the content of the first $set, but that'd be less readable, I think.