MongoDB/PyMongo: upsert array element

Question

I have the following document:

    {'software_house': 'k1',
     'client_id': '1234',

     'transactions': [
      {'antecedents': 12345,
       'consequents': '015896018',
       'antecedent support': 0.0030889166727954697},

      {'antecedents': '932696735',
       'consequents': '939605046',
       'antecedent support': 0.0012502757961314996}
      ...
                     ]}

In which key 'transactions' stores within an array 3 features, for each item.

I would like to update each item contained in the 'transactions' array, that matches with the same 'software_house', 'client_id', 'transactions.antecedents' and 'transactions.consequents'; and thus:

• Overwriting the element within the array if it does exist
• Appending a new value within 'transactions' if it doesn't

How could I achieve that using pymongo?

Answer 1

You can do this with an update with aggregation pipeline. You can first $filter the element matched. Then $setUnion with the item you want to upsert

PyMongo:

db.collection.update_many(filter = {
  // the criteria you want to match outside array
  "software_house": "k1",
  "client_id": "1234"
},
update = [
  {
    "$addFields": {
      "transactions": {
        "$filter": {
          "input": "$transactions",
          "as": "t",
          "cond": {
            $not: {
              $and: [
                // the criteria you want to match in array
                {
                  $eq: [
                    "$$t.antecedents",
                    12345
                  ]
                },
                {
                  $eq: [
                    "$$t.consequents",
                    "015896018"
                  ]
                }
              ]
            }
          }
        }
      }
    }
  },
  {
    "$addFields": {
      "transactions": {
        "$setUnion": [
          "$transactions",
          [
            {
              "antecedents": 12345,
              "consequents": "the entry you want to upsert",
              "antecedent support": -1
            }
          ]
        ]
      }
    }
  }
])

Native MongoDB query:

db.collection.update({
  // the criteria you want to match outside array
  "software_house": "k1",
  "client_id": "1234"
},
[
  {
    "$addFields": {
      "transactions": {
        "$filter": {
          "input": "$transactions",
          "as": "t",
          "cond": {
            $not: {
              $and: [
                // the criteria you want to match in array
                {
                  $eq: [
                    "$$t.antecedents",
                    12345
                  ]
                },
                {
                  $eq: [
                    "$$t.consequents",
                    "015896018"
                  ]
                }
              ]
            }
          }
        }
      }
    }
  },
  {
    "$addFields": {
      "transactions": {
        "$setUnion": [
          "$transactions",
          [
            {
              "antecedents": 12345,
              "consequents": "the entry you want to upsert",
              "antecedent support": -1
            }
          ]
        ]
      }
    }
  }
],
{
  multi: true
})

Here is the Mongo playground for your reference.