使用NoSQL Booster SQL查询实现MongoDB文档数组聚合转换遇阻

嘿，我懂你用SQL来处理Mongo数据的思路——毕竟熟悉的工具用着顺手，NoSQL Booster这个偷懒技巧确实挺香的！你遇到的问题其实很典型：SQL的列映射逻辑没法直接处理Mongo里的嵌套数组字段，所以最后一行尝试提取sale_products.name并映射到line_items.item_name才会失效。

问题根源 #

你的原查询最后一行"object_raw_origin_data.register_sales.sale_products.name"会把数组里所有的name值拼接成一个单一结果（或者返回数组的字符串形式），而不是生成一个包含item_name子文档的line_items数组。要处理这种嵌套数组的字段转换，得借助Mongo的聚合表达式，而NoSQL Booster的SQL查询支持嵌入这些表达式。

解决方案1：修改SQL查询，嵌入Mongo聚合逻辑 #

你可以用JSON_QUERY函数在SQL里写入Mongo的$map操作，来遍历原数组并转换字段：

SELECT 
  object_origin, 
  party_uuid AS company, 
  connection_uuid AS connection, 
  object_created AS object_creation_date, 
  "object_raw_origin_data.register_sales.sale_date" AS transaction_date, 
  "object_raw_origin_data.register_sales.total_price" + "object_raw_origin_data.register_sales.total_tax" AS transaction_gross_value, 
  "object_raw_origin_data.register_sales.total_price" AS transaction_net_value, 
  'goods-service-transaction' AS object_class, 
  'point-of-sale' AS object_origin_category, 
  'offline' AS object_origin_type, 
  object_origin AS object_origin, 
  "object_raw_origin_data.register_sales.invoice_number" AS transaction_reference, 
  "object_raw_origin_data.register_sales.status" AS transaction_status, 
  'GBP' AS transaction_currency, 
  -- 用Mongo的$map转换数组字段
  JSON_QUERY(
    "{ 'line_items': { '$map': { 'input': '$object_raw_origin_data.register_sales.sale_products', 'as': 'prod', 'in': { 'item_name': '$$prod.name' } } } }",
    'strict'
  ) AS line_items
FROM Vend_raw_transactions

这里的$map会遍历sale_products数组，把每个元素的name字段重命名为item_name，最终生成符合要求的line_items数组。

解决方案2：直接使用Mongo聚合脚本（更直观） #

如果愿意稍微跳出SQL的舒适区，直接写Mongo聚合管道会更清晰，逻辑和你的SQL完全一致，而且处理数组更原生：

db.Vend_raw_transactions.aggregate([
  {
    $project: {
      object_origin: 1,
      company: "$party_uuid",
      connection: "$connection_uuid",
      object_creation_date: "$object_created",
      transaction_date: "$object_raw_origin_data.register_sales.sale_date",
      transaction_gross_value: { $add: ["$object_raw_origin_data.register_sales.total_price", "$object_raw_origin_data.register_sales.total_tax"] },
      transaction_net_value: "$object_raw_origin_data.register_sales.total_price",
      object_class: "goods-service-transaction",
      object_origin_category: "point-of-sale",
      object_origin_type: "offline",
      transaction_reference: "$object_raw_origin_data.register_sales.invoice_number",
      transaction_status: "$object_raw_origin_data.register_sales.status",
      transaction_currency: "GBP",
      line_items: {
        $map: {
          input: "$object_raw_origin_data.register_sales.sale_products",
          as: "prod",
          in: { item_name: "$$prod.name" }
        }
      }
    }
  }
])

额外注意点 #

如果register_sales本身是一个数组（而不是单个对象），你需要先处理这个层级的数组，比如用$arrayElemAt取第一个元素：

// 假设register_sales是数组，取第一个元素的sale_products
input: "$object_raw_origin_data.register_sales.0.sale_products"

或者用$unwind展开数组后再处理，具体看你的数据结构。

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