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.

Takis · Accepted Answer · 2022-03-18T02:32:06.613

0

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})

edited Mar 18 '22 at 02:32

answered Sep 01 '21 at 18:01

Takis

8,314
2
14
25

Thanks for your suggestion. This query should be slightly edited to add the metrics in the last element of `sessions` array. A new session element should be created if the metrics object contains `br` and `os` elements – HKHAIT Sep 02 '21 at 11:34
the query above, always adds the new metrics object in the sessions array. It never merges with existing metrict, you want to update existing metrics also? based on what creteria? same `ts`? Its best to write all the cases and what you want to happen in each case, i wrote the cases that the query examines, expand those cases if you have more cases. – Takis Sep 02 '21 at 18:03
Thank you for the taking time to consider this issue. Here's my modification https://mongoplayground.net/p/ghiWD3Cm2wy. a new metric must go into the last array of sessions unless the metric contains `br` which means a new array should be created in sessions array – HKHAIT Sep 03 '21 at 13:41
i added one more case, if `br` to create new array else push it in the last session. hope this time to work for you, query is big, but its simple JSON makes it harder to read but ok – Takis Sep 03 '21 at 21:46
This is a great job. Thank you – HKHAIT Sep 14 '21 at 16:08

mongodb update/upsert and nested arrays

1 Answers1