mongodb update/upsert and nested arrays

Question

I have a collection that contains metrics about conferences and users on my website and I would like to store them by conference. Each conference has one or many users, and each user has one or many sessions, and each session has one or many metrics.

Here's an example of a conference document that I want to save :

{
    "conf":"conference12345",
    "users":[
        { //User 1
            "uid":"2dd8b4e3-9dcd-4da6-bc36-aa0988dc9642",
            "sessions":[
                [// User 1 => session 1
                    {
                        "ts": 1234567891,
                        "br":"Chrome 92",
                        "os":"Windows",
                    },
                    {
                        "ts": 1234567899,
                        "bw":100
                    }
                ],
                [// User 1 => session 2
                    {
                        "ts": 1234567900,
                        "br":"Chrome 92",
                        "os":"Windows",
                    },
                    {
                        "ts": 1234567950,
                        "bw":500
                    }
                ],
            ]
        },
        { //User 2
            "uid":"2dd8b4e3-9dcd-4da6-bc36-aa0988dc9666",
            "sessions":[
                [// User 2 => session 1
                    {
                        "ts": 1234567891,
                        "br":"Chrome 90",
                        "os":"Mac",
                    },
                    {
                        "ts": 1234567899,
                        "bw":100
                    }
                ]
            ]
        }
    ]
}

I want to insert new data at once by using db.collection.update with upsert option. But I had a hard time figuring out how. Metrics should be added to the last element of "sessions" array.

Here's an example of data that I want to add :

// New user. a new document should be created because it's a new conference.
{ 
        "conf": "conferenceXYZ",
        "uid": "1cd8b4e3-9dcd-4da6-bc36-aa0988dc9512",
        "metrics": {
                "ts": 1234567891,
                "br":"Chrome 90",
                "os":"Mac",
        }
}

// Existing user. metrics should be added to his last session
{ 
        "conf": "conference12345",
        "uid": "2dd8b4e3-9dcd-4da6-bc36-aa0988dc9642",
        "metrics": {
                "ts": 1234567891,
                "bw":356
        }
}

I tried to use update with upsert option but it's a difficult to find the missing pieces. Any help would be greatly appreciated.

Answer 1

Query

• first filter/$set is to put your data
• checks if new user (its new user in case upsert or user not found)
• if new user pushes the new user else finds the user and adds the new session

PlayMongo

update(
{"conf": "conference12345"},
[{"$set": 
   {"uid": "2dd8b4e3-9dcd-4da6-bc36-aa0988dc9642",
    "usession": [{"ts": 1234567891, "bw": 356}]}},
 {"$set": 
   {"new-user": 
     {"$eq": 
       [{"$filter": 
           {"input": {"$cond": ["$users", "$users", []]},
            "as": "u",
            "cond": {"$eq": ["$$u.uid", "$uid"]}}},
         []]}}},
 {"$set": 
   {"users": 
     {"$cond": 
       ["$new-user",
         {"$concatArrays": 
           [{"$cond": [{"$isArray": ["$users"]}, "$users", []]},
             [{"uid": "$uid", "sessions": ["$usession"]}]]},
         {"$map": 
           {"input": "$users",
            "as": "u",
            "in": 
             {"$cond": 
               [{"$eq": ["$$u.uid", "$uid"]},
                 {"$mergeObjects": 
                   ["$$u",
                     {"sessions": 
                       {"$concatArrays": ["$$u.sessions", ["$usession"]]}}]},
                "$$u"]}}}]}}},
 {"$unset": ["uid", "usession", "new-user"]}],
{"upsert": true, "multi": true})