How to update a nested key containing a dot?

Working with Data
1

In a document like this:

[
  {
    a: "",
    b: 1,
    "createdAccounts": {
      "test@gmail.com": {
        "id": ["a", "b", "c"]
      }
    }
  }
]

I’m querying an email as:

    const email = "test@gmail.com"
    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 would like to ask two questions:

1-
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 = "test@gmail.com"
let account = await db.collection("users").findOne({ "createdAccounts.email": email });

wouldnt work.

2-
Using the aggregate query I get as response:

"test@gmail.com": {
    "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?

2

Hi Michel,

For your 1st question you can use $getFields to query fields that have special characters like $ and . in their names. You can check the query in the following playground - Mongo playground

1 Like
3

You could solve many of your issues by having a schema that does not use data value as field names.

From

you should go to something akin to the attribute pattern like:

"createdAccounts": {
      "user" : "test@gmail.com" ,
      "id": ["a", "b", "c"]
    }

No dots in the field name and the added benefit is that you can created an index on createdAccounts.user.

4

The document is already on this dot format, so is not possible to update a key that contains a dot?

5

I am not sure if I understand what you mean. But using $map from your $objectToArray, you can rename the k and v of $objectToArray to field names that make sense for your collection. With a final $merge into itself you have permanently updated you collection.

See (without the final $merge)

About the final $merge, I prefer to do it in 2 steps. Step 1 into a temporary collection so that I can verify the results. Step 2 a simple $merge from the temp. coll. into the real one.