求助：如何转换非标准JSON至可导入Azure SQL DW的格式？

一、使用ADF数据流直接处理（无需中转CSV） #

这是最直接的端到端方案，无需转换格式即可完成导入：

• 源数据集配置：选择JSON格式，将根路径设置为ia::result（实际业务数据存储在该数组中）
• 派生列转换：重命名带点的字段（SQL列名不允许包含小数点），同时修正数据类型，示例表达式：

  ach_accountNumber = ach.accountNumber
  audit_createdBy = audit.createdBy
  audit_createdDateTime = toTimestamp(audit.createdDateTime, 'yyyy-MM-dd''T''HH:mm:ss''Z''')
  totalDue = toDecimal(totalDue, 18, 2)
  

• 空值处理：用coalesce()函数为空值设置默认值，比如coalesce(creditLimit, 0)或coalesce(taxId, '')
• 目标表映射：在Sink组件中将处理后的字段直接映射到Azure SQL DW的目标列，确保数据类型完全匹配

二、PowerShell转换为合规CSV #

如果必须通过CSV中转，重点解决带点字段和空值问题：

# 读取JSON文件
$jsonContent = Get-Content -Path "你的输入文件路径.json" -Raw | ConvertFrom-Json

# 提取数据数组，批量处理带点字段并替换空值
$processedData = $jsonContent.'ia::result' | ForEach-Object {
    $newObj = [PSCustomObject]@{}
    # 遍历所有字段，将点号替换为下划线
    foreach ($key in $_.PSObject.Properties.Name) {
        $newKey = $key -replace '\.', '_'
        $value = $_.$key
        # 空值替换为空字符串，避免CSV导出异常
        $newObj | Add-Member -MemberType NoteProperty -Name $newKey -Value $(if ($null -eq $value) { '' } else { $value })
    }
    $newObj
}

# 导出到CSV
$processedData | Export-Csv -Path "输出文件路径.csv" -NoTypeInformation -Encoding UTF8

三、Azure SQL DW直接读取JSON导入 #

若JSON文件存储在Azure Blob存储，可通过SQL语句直接读取并导入，无需额外工具：

-- 先创建匹配的目标表
CREATE TABLE VendorData (
    alwaysCreateBill BIT,
    billingType NVARCHAR(50),
    creditLimit DECIMAL(18,2),
    currency NVARCHAR(3),
    ach_accountNumber NVARCHAR(50),
    audit_createdBy NVARCHAR(10),
    audit_createdDateTime DATETIME2,
    bankFiles_paymentCurrency NVARCHAR(3),
    -- 补充其他字段定义
)

-- 从Blob存储读取JSON并插入表中
INSERT INTO VendorData
SELECT 
    JSON_VALUE(vendor.value, '$.alwaysCreateBill') as alwaysCreateBill,
    JSON_VALUE(vendor.value, '$.billingType') as billingType,
    TRY_CAST(JSON_VALUE(vendor.value, '$.creditLimit') AS DECIMAL(18,2)) as creditLimit,
    JSON_VALUE(vendor.value, '$.currency') as currency,
    JSON_VALUE(vendor.value, '$.ach.accountNumber') as ach_accountNumber,
    JSON_VALUE(vendor.value, '$.audit.createdBy') as audit_createdBy,
    TRY_CAST(JSON_VALUE(vendor.value, '$.audit.createdDateTime') AS DATETIME2) as audit_createdDateTime,
    JSON_VALUE(vendor.value, '$.bankFiles.paymentCurrency') as bankFiles_paymentCurrency
    -- 补充其他字段的提取逻辑
FROM OPENROWSET(
    BULK 'https://你的存储账户.blob.core.windows.net/容器名/目标JSON文件.json',
    FORMAT = 'CSV',
    FIELDTERMINATOR ='0x0b',
    FIELDQUOTE = '0x0b'
) WITH (jsonContent NVARCHAR(MAX)) AS j
CROSS APPLY OPENJSON(jsonContent, '$.ia::result') as vendor

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