MongoDB: Adding a field in an array composed of $concat of the other fields in same array

Question

Let me start by saying that I'm brand new to MongoDB. I only started a few days ago.

I have a collection of employees, one of the fields represents the phone number broken down into parts. It looks like this:

  {
    _id: ObjectId('666225dbe02f02db7f19465a'),
    lastName: 'Smith',
    firstName: 'John',
    middleInitial: '',
    email: 'john.smith@corp.com',
    phone: [
      {
        international: '',
        area: '555',
        prefix: '555',
        number: '1234',
        ext: '',
        name: 'work'
      },
      {
        international: '',
        area: '555',
        prefix: '555',
        number: '2345',
        ext: '',
        name: 'cell'
      }
    ],
    address: {
      work: {
        city: 'New York',
        province: 'NY',
        buildingName: 'Empire State Building',
        address: '20 W 34th St.',
        postalCode: '10001',
        mailstop: '49B'
      }
    },
    positionId: ObjectId('666225dbe92f02db8f09465d')
  }

What I'm trying to achieve is to have the phone number regrouped inside each array element like this:

  {
[...]
    phone: [
      {
        international: '',
        area: '555',
        prefix: '555',
        number: '1234',
        ext: '',
        name: 'work',
        phone: '(555) 555-1234'
      },
      {
        international: '',
        area: '555',
        prefix: '555',
        number: '2345',
        ext: '',
        name: 'cell',
        phone: '(555) 555-2345'
      }
    ],
[...]
  }

I am using PHP so my code example will be in PHP. I tried a few things, but this is what I have right now.

$documents = $collection->aggregate([
  [
    '$addFields'=>[
      'phone.phone'=>[
        '$map'=>[
          'input'=> '$phone',
          'as'=> "p",
          'in'=>[
            '$concat'=>[
              "(", '$$p.area', ") ",
              '$$p.prefix', "-",
              '$$p.number',
              [
                '$cond'=> [
                  [
                    '$ne'=>[
                      '$$p.ext',
                      ''
                    ],
                  ],
                  [
                    '$concat'=>[
                      ' ext. ',
                      '$$p.ext',
                    ],
                  ],
                  ''
                ],
              ],
            ],
          ],
        ],
      ],
    ],
  ],
]);

Unfortunately, this returns an array of ALL phone numbers in EACH element of the phone array

  {
[...]
    phone: [
      {
        international: '',
        area: '555',
        prefix: '555',
        number: '1234',
        ext: '',
        name: 'work',
        phone: [ '(555) 555-1234', '(555) 555-2345' ]
      },
      {
        international: '',
        area: '555',
        prefix: '555',
        number: '2345',
        ext: '',
        name: 'cell',
        phone: [ '(555) 555-1234', '(555) 555-2345' ]
      }
    ],
[...]
  }

I tried using $project but I need to have ALL fields of the employee since not all employees have the same fields and project does not allow me to "return ALL fields by default", I have to specify all of them. I also tried the $addFields (seen above), I tried using find instead of aggregate but I had the same result. I have searched for hours on the internet and the MongoDB documentation to find a solution but can't find any (or don't know how to type precisely what I'm looking for).

I know this can be done really easily in PHP, but I'm trying to learn MongoDB.

Thank you for any help you can provide.

Answer 1

The reason why your attempt is resulting in an array field is that you are operating on the whole phone array field. You are actually on the right track to use $map to iterate the array entries one-by-one. You just need to use $mergeObjects to perform element-wise operations.

db.collection.update({},
[
  {
    "$set": {
      "phone": {
        "$map": {
          "input": "$phone",
          "as": "p",
          "in": {
            "$mergeObjects": [
              "$$p",
              {
                phone: {
                  "$concat": [
                    "(",
                    "$$p.area",
                    ") ",
                    "$$p.prefix",
                    "-",
                    "$$p.number"
                  ]
                }
              }
            ]
          }
        }
      }
    }
  }
],
{multi: true})

Mongo Playground