How to update a nested key containing a dot?

Question

In a document like this:

[
  {
    a: "",
    b: 1,
    "createdAccounts": {
      "[email protected]": {
        "id": ["a", "b", "c"]
      }
    }
  }
]

I'm querying an email as:

    const email = "[email protected]"
    let account = await db.collection("users").aggregate(
    [
        {
            "$project": {
                "createdAccounts": {
                    "$objectToArray": "$createdAccounts"
                }
            }
        },
        {
            "$match": {
                "createdAccounts.k": email
            }
        },
        {
            "$project": {
                "createdAccounts": {
                    "$arrayToObject": "$createdAccounts"
                }
            }
        }
    ]).toArray().then(results =>
    {
        const document = results[0];
        if (document && document.createdAccounts)
            return document.createdAccounts[email];        
        return null; 
    })

I wonder if there's a "better way" to query the email, I'm using aggregate because the searched key can contain a dot (.) in its path

and in this case, this:

const email = "[email protected]"
let account = await db.collection("users").findOne({ "createdAccounts.email": email });

wouldnt work.

Using the aggregate query I get as response:

"[email protected]": {
    "id": ["a", "b", "c"]
}

Suppose i need to modify the id array, as its inside a key containing a dot (.) i couldn't find how to update it.

Also, the update method will concatenate the array or overwrite it?

Answer 1

Firstly, you really should use the Attribute pattern for this. Mongo doesn't handle "unknown keys" very well, unlike JS (and Python) where you can iterate over keys & values.

This pipeline will use your current structure to append an element in the id field, for the matched email:

db.users.aggregate([
  {
    "$project": {
      "createdAccounts": {
        "$objectToArray": "$createdAccounts"
      }
    }
  },
  {
    "$match": {
      // replace with the `email` you want to match
      "createdAccounts.k": "[email protected]"
    }
  },
  {
    "$set": {
      "createdAccounts.v.id": {
        "$concatArrays": [
          // unclear why I need this "first" but the
          // concat produces an extra array if I don't
          { "$first": "$createdAccounts.v.id" },
          ["X"]  // <-- put the element you want to add
        ]
      }
    }
  },
  {
    "$project": {
      "createdAccounts": {
        "$arrayToObject": "$createdAccounts"
      }
    }
  },
  {
    "$merge": {
      "into": "users",
      "on": "_id",
      "whenMatched": "merge",
      "whenNotMatched": "discard"
    }
  }
])

Note that in Mongo Playground it will only show the document as per the pipeline result. The merge result can be seen by checking the updated document in the collection.

Result in the users collection:

{
  "_id": 1,
  "a": "",
  "b": 1,
  "createdAccounts": {
    "[email protected]": {
      "id": [
        "a",
        "b",
        "c",
        "X"
      ]
    }
  }
}