MongoDB聚合操作语法错误排查及SQL等效实现求助

问题背景 #

拥有丰富SQL使用经验，但MongoDB/JSON新手，执行聚合操作时触发错误：uncaught exception: SyntaxError: missing : after property id，确认问题出在$group阶段，注释该部分后查询可正常运行。目标是实现以下SQL的等效MongoDB聚合逻辑：

select
   (extract(year from t.updated_at) * 100 + extract(month from t.updated_at)) as year_month
   ,c.client_name
   ,c.client_label
   ,c.client_code
   ,c.client_country
   ,c.client_base_currency
   ,c.client_invoice_currency
   ,sum(t.usd_value) as total_vol

from transactions t

left join clients c
  on t.client = c._id

where t.updated_at between '2022-01-01' and '2022-03-31'

group by 1,2,3,4,5,6,7

原错误聚合脚本：

db.transactions.aggregate([
{
    $match:
    {
        $and:
        [
            {
                "updated_at": { $gte: ISODate("2022-01-01") }
            },
            {
                "updated_at": { $lte: ISODate("2022-03-31") }
            },
        ]
    }    
},
{
 $lookup:
     {
        from: 'clients',
        localField: 'client',
        foreignField: '_id',
        as: 'clients'
     }
 },
 {
     $unwind: '$clients'
 },
 {
     $addFields:
     {
         "client_name": "$clients.client_name"
         ,"client_label": "$clients.client_label"
         ,"client_code": "$clients.client_code"
         ,"client_country": "$clients.client_country"
         ,"client_base_currency": "$clients.client_base_currency"
         ,"client_invoice_currency": "$clients.client_invoice_currency"
     }
 },
 {
     $project:
     {
         client_name: 1
         ,client_label: 1
         ,client_code: 1
         ,client_country: 1
         ,client_base_currency: 1
         ,client_invoice_currency: 1         
         ,updated_at: 1
         ,usd_value: 1
     }
 },
 {
      $group:
      {
          _id: 
              { $dateToString: { "date": "$updated_at", "format": "%Y-%m"  } }
              ,"$client_name"
              ,"$client_label"
              ,"$client_code"
              ,"$client_country"
              ,"$client_base_currency"
              ,"$client_invoice_currency"
              ,total_vol: { $sum: "$usd_value" }
      }                 
 }
 ])

错误核心原因 #

$group阶段的_id字段语法错误：MongoDB要求$group的_id必须是键值对组成的对象，所有分组字段都要作为_id的属性（指定键名），不能直接罗列字段表达式。原脚本里直接写"$client_name"这种无键名的语法，导致JSON解析失败。

另外原$match中的$and可以简化，同一个字段的范围查询无需用$and包裹。

修正后的完整聚合脚本 #

db.transactions.aggregate([
    // 对应SQL的WHERE条件，简化范围查询写法
    {
        $match: {
            "updated_at": { 
                $gte: ISODate("2022-01-01"), 
                $lte: ISODate("2022-03-31") 
            }
        }
    },
    // 对应SQL的LEFT JOIN
    {
        $lookup: {
            from: 'clients',
            localField: 'client',
            foreignField: '_id',
            as: 'clients'
        }
    },
    // 处理LEFT JOIN后的数组，preserveNullAndEmptyArrays保留无匹配的记录（对应LEFT JOIN特性）
    {
        $unwind: {
            path: '$clients',
            preserveNullAndEmptyArrays: true
        }
    },
    // 提取clients子文档的字段到顶层，对应SQL中选取c.*的字段
    {
        $addFields: {
            "client_name": "$clients.client_name",
            "client_label": "$clients.client_label",
            "client_code": "$clients.client_code",
            "client_country": "$clients.client_country",
            "client_base_currency": "$clients.client_base_currency",
            "client_invoice_currency": "$clients.client_invoice_currency"
        }
    },
    // 可选：过滤需要的字段，和SQL的SELECT字段对应
    {
        $project: {
            client_name: 1,
            client_label: 1,
            client_code: 1,
            client_country: 1,
            client_base_currency: 1,
            client_invoice_currency: 1,
            updated_at: 1,
            usd_value: 1
        }
    },
    // 对应SQL的GROUP BY，_id包含所有分组字段，total_vol对应SUM(usd_value)
    {
        $group: {
            _id: {
                year_month: { $dateToString: { "date": "$updated_at", "format": "%Y-%m" } },
                client_name: "$client_name",
                client_label: "$client_label",
                client_code: "$client_code",
                client_country: "$client_country",
                client_base_currency: "$client_base_currency",
                client_invoice_currency: "$client_invoice_currency"
            },
            total_vol: { $sum: "$usd_value" }
        }
    },
    // 可选：把_id里的字段展开到顶层，和SQL查询结果格式更一致
    {
        $project: {
            _id: 0,
            year_month: "$_id.year_month",
            client_name: "$_id.client_name",
            client_label: "$_id.client_label",
            client_code: "$_id.client_code",
            client_country: "$_id.client_country",
            client_base_currency: "$_id.client_base_currency",
            client_invoice_currency: "$_id.client_invoice_currency",
            total_vol: 1
        }
    }
])

关键说明 #

1. $unwind添加preserveNullAndEmptyArrays: true：确保没有匹配client的transaction记录也被保留，完全对应SQL的LEFT JOIN逻辑。
2. $group的_id对象：每个分组字段都指定了键名（如year_month、client_name），符合MongoDB的JSON语法要求。
3. 最后添加的$project：将_id内的分组字段展开到顶层，让输出格式和SQL查询结果结构更接近，可选但更直观。

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