I have a simple collection with a document like this one:

{
    _id: ObjectId('62b196e43581370007773393'),
    name: 'John',
    jobs: [
        {
            company: 'ACME',
            type: 'programmer',
            technologies: [
                {
                    level: 1,
                    name: 'Java'
                },
                {
                    level: 3,
                    name: 'MongoDB'
                }
            ]
        }
    ]
}

I'd like to collect all technologies into a new field in the "job" sub-document for "programmers" jobs only, to achieve this effect:

(...)
jobs: [
 { 
   it_technologies : ["Java", "MongoDB"]
   (...)

 }
]
(...)

The problem is that I do not know how to refer to and collect proper elements of the documents with this query:

db.runCommand({update: "employees",      
    updates: [
         {
           q: {},
           u: { $set: { "jobs.$[j].it_technologies": "<how to collect all technologies in this job?>" } },
           upsert: false,
           multi: true,
           arrayFilters: [{
            $and:
              [
                {"j.type": "programmer"},
                {"j.technologies": {$exists : true, $ne: []} }
              ]
            }]
         }
      ] });

Is it at all possible? I'd be grateful for any clue!

CodePudding user response：

Think that you need to work the update with the aggregation pipeline.

1. map - Iterate with the jobs array and return a new array.

   1.1. $cond - Check the condition (Match the current document of jobs type and technology is not an empty array). If fulfilled, then update the document, else remains existing.

   1.1.1. $mergeObjects - Merge current document with the document with it_technologies.

db.collection.update({},
[
  {
    $set: {
      "jobs": {
        $map: {
          input: "$jobs",
          in: {
            $cond: {
              if: {
                $and: [
                  {
                    $eq: [
                      "$$this.type",
                      "programmer"
                    ]
                  },
                  {
                    $ne: [
                      "$$this.technologies",
                      []
                    ]
                  }
                ]
              },
              then: {
                $mergeObjects: [
                  "$$this",
                  {
                    it_technologies: "$$this.technologies.name"
                  }
                ]
              },
              else: "$$this"
            }
          }
        }
      }
    }
  }
],
{
  upsert: false,
  multi: true,
  
})

Sample Mongo Playground