How can i insert or update an object in nested array when document exists or insert document if it doesn't exist with single atomic update?

Working with Data
, ,
1

What i need to implement in a single operation:

  • If document exists:
    • check if object exists in nested array:
      • yes → update object inside array
      • no → insert object into array
  • If document doesn’t exist:
    • insert a new document

Assume I have a collection with a single document:

[
  {
    "_id": {
      "a": 1,
      "b": 2
    },
    "data": [
      {
        "id": 1,
        "c": {
          "d": 1
        }
      },
      {
        "id": 2,
        "c": {
          "d": 2
        }
      }
    ]
  }
]

Case 1: update object in nested array

Nested object (with id = 1) to be updated:

{
  "id": 1,
  "c": {
    "d": 10
  }
}

Expected result:

{
  "_id": {
    "a": 1,
    "b": 2
  },
  "data": [
    {
      "id": 1,
      "c": {
        "d": 10
      }
    },
    {
      "id": 2,
      "c": {
        "d": 2
      }
    }
  ]
}

Case 2: insert object into nested array

Object to be inserted:

{
  "id": 3,
  "c": {
    "d": 3
  }
}

Expected result:

{
  "_id": {
    "a": 1,
    "b": 2
  },
  "data": [
    {
      "id": 1,
      "c": {
        "d": 1
      }
    },
    {
      "id": 2,
      "c": {
        "d": 2
      }
    },
    {
      "id": 3,
      "c": {
        "d": 3
      }
    }
  ]
}

Case 3: Document with _id doesn’t exist → need to insert into collection:

Document to be inserted:

{
  "_id": {
    "a": 2,
    "b": 2
  },
  "data": [
    {
      "id": 1,
      "c": {
        "d": 1
      }
    }
  ]
}

Expected result - collection of two documents:

[
  {
    "_id": {
      "a": 1,
      "b": 2
    },
    "data": [
      {
        "id": 1,
        "c": {
          "d": 1
        }
      },
      {
        "id": 2,
        "c": {
          "d": 2
        }
      }
    ]
  },
  {
    "_id": {
      "a": 2,
      "b": 2
    },
    "data": [
      {
        "id": 1,
        "c": {
          "d": 1
        }
      }
    ]
  }
]

I have tried to use updateOne() with aggregation pipeline:

db.collection.updateOne({
  "_id": {
    "a": 1,
    "b": 2
  }
},
[
  {
    $set: {
      data: {
        $cond: [
          {
            $in: [
              1,
              "$data.id"
            ]
          },
          {
            $map: {
              input: "$data",
              in: {
                $mergeObjects: [
                  "$this",
                  {
                    $cond: [
                      {
                        $eq: [
                          "$this.id",
                          1
                        ]
                      },
                      {
                        "id": 1,
                        "c": {
                          "d": 10
                        }
                      },
                      {}
                    ]
                  }
                ]
              }
            }
          },
          {
            $concatArrays: [
              "$data",
              [
                {
                  "id": 1,
                  "c": {
                    "d": 10
                  }
                }
              ]
            ]
          }
        ]
      }
    }
  }
],
{
  upsert: true
})

It works fine, but when document doesn’t exist, i get an error:

fail to run update: write exception: write errors: [$in requires an array as a second argument, found: missing]

Сan anybody help? Any help would be appreciate

2

I think you are describing the use case for a Transaction.

3

I think you should be able to get a starting array that is either the document’s data field or empty array if the document didn’t exist by using $ifNull, something like { $ifNull: [ "$data", [] ] }.

1 Like
4

@Christian_Rorvik, thanks for your reply!

I correct my aggregation pipeline to:

db.collection.update({
  "_id": {
    "a": 1,
    "b": 2
  }
},
[
  {
    $set: {
      data: {
        $cond: [
          {
            $in: [
              1,
              {
                $ifNull: [
                  "$data.id",
                  []
                ]
              }
            ]
          },
          {
            $map: {
              input: "$data",
              in: {
                $mergeObjects: [
                  "$$this",
                  {
                    $cond: [
                      {
                        $eq: [
                          "$$this.id",
                          1
                        ]
                      },
                      {
                        "id": 1,
                        "c": {
                          "d": 10
                        }
                      },
                      {}
                    ]
                  }
                ]
              }
            }
          },
          {
            $concatArrays: [
              {
                $ifNull: [
                  "$data",
                  []
                ]
              },
              [
                {
                  "id": 1,
                  "c": {
                    "d": 10
                  }
                }
              ]
            ]
          }
        ]
      }
    }
  }
],
{
  upsert: true
})

I have checked all my test cases and it seems working!

5

@Jack_Woehr, thanks for your reply!

I’d like to avoid using the transaction approach due to our prod MongoDB infrastructure. I’m really afraid of poor performance or other pitfalls in cluster with PSA architecture.

6

@kekestan , if transactions don’t work adequately in MongoDB, the MongoDB architects should be brought into the discussion! Transactions are an essential part of a database model.

7

This topic was automatically closed 5 days after the last reply. New replies are no longer allowed.