insert multiple objects into nested arrays with condition

Question

I'm a mongo beginner and struggling to insert multiple objects into multiple nested array in one document.

The document looks like this:

[
  {
    "id": 1,
    "name": "myObject",
    "sections": [
      {
        "id": "section1",
        "items": [
          {
            "id": 1,
            "name": "item1.1",
            "scores": [
              {
                "userId": 13,
                "score": 10
                
              }
            ]
          },
          {
            "id": 2,
            "name": "item1.2",
            "scores": [
              {
                "userId": 66,
                "score": 10
              }
            ]
          }
        ]
      },
      {
        "id": "section2",
        "items": [
          {
            "id": 3,
            "name": "item2.1",
            "scores": [
              {
                "userId": 13,
                "score": 20
              }
            ]
          }
        ]
      }
    ]
  }
]

I now want to insert new scores for userId=10 for every item in every section.
The score is of course different for every item.
let's assume scores like this (all for userId=10)

[
   {
     "sectionId": "section1"
     "itemName: "item1.1"
     "score: 10,
     "userId": 10
   },
   {
     "sectionId": "section1"
     "itemName: "item1.2"
     "score: 15,
     "userId": 10
   },
   {
     "sectionId": "section2"
     "itemName: "item2.1"
     "score: 33,
     "userId": 10
   }
]

Added for clarification

the updated document should look like the following.

{
    "id": 1,
    "name": "myObject",
    "sections": [
      {
        "id": "section1",
        "items": [
          {
            "id": 1,
            "name": "item1.1",
            "scores": [
              {
                "userId": 13,
                "score": 10
              },
              { // <-- newly added score
                "userId": 10,
                "score": 10
              }
            ]
          },
          {
            "id": 2,
            "name": "item1.2",
            "scores": [
              {
                "userId": 66,
                "score": 10
              },
              { // <- newly added score
                "userId": 10,
                "score": 15
              }
            ]
          }
        ]
      }
// the remaining document is omitted for brevity but the above should also be applied to this sections

so far I have been able to achieve what I want for one single score like this

db.collection.update({
  id: 1
},
{
  $push: {
    "sections.$[item].items.$[score].scores": {
      "userId": 10,
      "score": 13
    },
  }
},
{
  arrayFilters: [
    {
      "score.userId": {
        $ne: 10
      }
    },
    {
      "item.name": {
        $eq: "item1.1"
      }
    }
  ]
})

This inserts a score for userId=10 in itemName=item1.1 if no score for userId=10 exists.

But I'm struggling on how to insert multiple scores into multiple items.
I saw that you can merge objects together, so maybe this would be an option although it kinda fells like an overkill.

So how can I insert all my scores for the different items in one atomic operation?

EDIT: Added clarification about the desired result.

Takis · Accepted Answer · 2022-03-16 23:35:51Z

Query

pipeline update, requires MongoDB >= 4.2
reduce on the data that you want to insert, with initial value the sections
nested 3 maps that always do the same
- if its not the key-value i want, keep the old value
- else (merge {:newkey (map ...)})
if userId exists updates its score, else insert new userID and score
query assumes that there is a score array even if empty, i mean it only creates new userId+score, not sections items etc

*you can avoid the set/unset and use driver variables in all the places where data is used

PlayMongo

db.collection.update({},
[
  {
    "$set": {
      "data": [
        {
          "sectionId": "section1",
          "itemName": "item1.1",
          "userId": 10,
          "score": 20
        },
        {
          "sectionId": "section1",
          "itemName": "item1.1",
          "userId": 13,
          "score": 30
        },
        {
          "sectionId": "section2",
          "itemName": "item2.1",
          "score": 33,
          "userId": 10
        }
      ]
    }
  },
  {
    "$set": {
      "sections": {
        "$reduce": {
          "input": "$data",
          "initialValue": "$sections",
          "in": {
            "$let": {
              "vars": {
                "data": "$$this"
              },
              "in": {
                "$map": {
                  "input": "$$value",
                  "in": {
                    "$cond": [
                      {
                        "$ne": [
                          "$$section.id",
                          "$$data.sectionId"
                        ]
                      },
                      "$$section",
                      {
                        "$mergeObjects": [
                          "$$section",
                          {
                            "items": {
                              "$map": {
                                "input": "$$section.items",
                                "in": {
                                  "$cond": [
                                    {
                                      "$ne": [
                                        "$$item.name",
                                        "$$data.itemName"
                                      ]
                                    },
                                    "$$item",
                                    {
                                      "$mergeObjects": [
                                        "$$item",
                                        {
                                          "scores": {
                                            "$let": {
                                              "vars": {
                                                "user_exist": {
                                                  "$in": [
                                                    "$$data.userId",
                                                    "$$item.scores.userId"
                                                  ]
                                                }
                                              },
                                              "in": {
                                                "$cond": [
                                                  {
                                                    "$not": [
                                                      "$$user_exist"
                                                    ]
                                                  },
                                                  {
                                                    "$concatArrays": [
                                                      "$$item.scores",
                                                      [
                                                        {
                                                          "userId": "$$data.userId",
                                                          "score": "$$data.score"
                                                        }
                                                      ]
                                                    ]
                                                  },
                                                  {
                                                    "$map": {
                                                      "input": "$$item.scores",
                                                      "in": {
                                                        "$cond": [
                                                          {
                                                            "$ne": [
                                                              "$$score.userId",
                                                              "$$data.userId"
                                                            ]
                                                          },
                                                          "$$score",
                                                          {
                                                            "$mergeObjects": [
                                                              "$$score",
                                                              {
                                                                "score": "$$data.score"
                                                              }
                                                            ]
                                                          }
                                                        ]
                                                      },
                                                      "as": "score"
                                                    }
                                                  }
                                                ]
                                              }
                                            }
                                          }
                                        }
                                      ]
                                    }
                                  ]
                                },
                                "as": "item"
                              }
                            }
                          }
                        ]
                      }
                    ]
                  },
                  "as": "section"
                }
              }
            }
          }
        }
      }
    }
  },
  {
    "$unset": [
      "data"
    ]
  }
])

thanks, this almost solves it. But it does update the scores instead of inserting them as new scores. I tried to combine the innermost $map with something like $push or $concatArrays but I'm struggling to get it to work. Any ideas?
you mean that yoou want if the user doesn't exist to insert?the above only updates existing ones, give an example if you can on your querstion
yes, exactly. Sorry if it wasn't clear. I updated the question, hope it's clear now :)
its ok maybe i just saw the sample data and i didn't read all text, now it inserst also if userId doesn't exists, but query is big, because deeply nested, and multiple updates in the same field. If you don't like this, maybe change your schema to be less nested.
thanks, this does exactly what I need, but I think I'll check if I can remove some of the nesting.

Collectives™ on Stack Overflow

insert multiple objects into nested arrays with condition

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related