MongoDB：为数组元素添加同数组字段拼接的phone字段

问题 #

MongoDB新手，接触仅几天。有一个员工集合，phone字段是拆分的电话号码数组，需要给数组每个元素添加phone字段，值为该元素area、prefix、number拼接的完整号码，带分机号则追加分机信息。用PHP编写的聚合查询运行后，每个数组元素的phone字段变成了所有号码的数组，而非对应元素的拼接值。试过$project但因要保留所有员工字段（不同员工字段可能不同）无法使用，find方法结果也一样，求解决。

原数据示例 #

{
  _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')
}

期望效果 #

{
[...]
  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'
    }
  ],
[...]
}

错误的PHP代码 #

$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',
                    ],
                  ],
                  ''
                ],
              ],
            ],
          ],
        ],
      ],
    ],
  ],
]);

错误结果 #

{
[...]
  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' ]
    }
  ],
[...]
}

解决方案 #

问题根源是直接给phone.phone赋值$map的结果，这会把整个$map生成的数组广播到每个phone元素的phone字段。正确做法是用$map重新构建整个phone数组，在每个元素里合并原有字段和新的phone字段。

修改后的PHP代码：

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

关键说明 #

1. 用$map遍历原phone数组，逐个处理每个元素
2. 通过$mergeObjects把原元素的所有字段和新生成的phone字段合并，既保留原有数据，又添加新字段
3. 新phone字段通过$concat拼接基础号码，$cond判断分机号是否为空，非空时追加分机信息

这样每个数组元素的phone字段就是对应元素的拼接值，不会出现所有号码的数组。

内容的提问来源于stack exchange，提问作者santaclown