title: MongoDB upsert into array of elements
body: <p>In MongoDB I have a collection ("sheets") with documents in this structure:</p>
  
  <pre><code>{
    _id: "xxx",
    name: "yyy",
    cells: [
      {
        x: 0, y: 0, sheet_id: "xxx", data: {content: "hello", type: "string"}
      },
      {
        x: 0, y: 1, sheet_id: "xxx", data: {content: "hello 2", type: "string"}
      }
    ]
  }
  </code></pre>
  
  <p>I also have a compound unique index for cells.x + cells.y + cells.sheet_id
  with partial <code>{ 'cells.x': { $exists: true } }</code></p>
  
  <p>I want to insert or update an object in the <em>cells</em> array.</p>
  
  <pre><code>const result = await db.collection("sheets").updateOne(
      { _id: "xxx", 'cells.x': 0, 'cells.y': 1 },
      {
          $set: {
              'cells.$.sheetId': "xxx",
              'cells.$.x': 0,
              'cells.$.y': 1,
              'cells.$.data': {type: "string", content: "hello 3"},
          },
      },
      { upsert: true }
  );
  </code></pre>
  
  <p>with this update, if an element with matching x, y and sheet_id exists, it updates it correctly.
  but if it does not exists, for example:</p>
  
  <pre><code>$set: {
      'cells.$.sheetId': "xxx",
      'cells.$.x': 50,
      'cells.$.y': 100,
      'cells.$.data': {type: "string", content: "hello NEW"}
  }
  </code></pre>
  
  <p>I get error:
  <code>MongoError: The positional operator did not find the match needed from the query</code></p>
  
  <p>how can I make it so:</p>
  
  <ul>
  <li>a "new" cell will be inserted into the <em>cells</em> array when no cell with a matching x-y-sheet_id was found.</li>
  <li>but a cell with matching x-y-sheet_id will be updated without inserting a new '<em>cell</em>.</li>
  </ul>