Mule 4：嵌套JSON扁平化转换并加载至数据库

需求概述 #

需将包含嵌套数组的JSON转换为两种扁平化结构，用于数据库加载：

1. 竖线分隔的文本格式（适合批量导入）
2. 拆分后的JSON数组（每个嵌套子项对应独立JSON对象）

原始JSON输入 #

{"Report_Entry": [
{
   "ContractNumber": "1111111",
   "Company": "ABCD INC.",
   "Contract_Lines_group": [
      {
         "LineShipToCustomer": "GOOD INC.",
         "LineReferenceId": "123456789_EXP"
      },
      {
         "LineShipToCustomer": "XYZ TELECOM",
         "LineReferenceId": "123456789_TIME"
      }
   ],
   "ContractName": "TEST Contract",
   "ReferenceId": "123456789"
},
{
   "ContractNumber": "222222",
   "Company": "FASLSE NEWS INC.",
   "Contract_Lines_group": [
      {
         "LineShipToCustomer": "LIVE NEWS INC.",
         "LineReferenceId": "789999_EXP"
      },
      {
         "LineShipToCustomer": "SKY NEWS INC.",
         "LineReferenceId": "789999_TIME"
      }
   ],
   "ContractName": "FALSE NEWS Contract",
   "ReferenceId": "6789999"
}
]}

注：原始输入JSON存在语法错误（部分键值对后缺失逗号），上述代码已修正该问题以保证可正常解析。

预期输出 #

1. 竖线分隔文本格式 #

ContractNumber|Company|LineShipToCustomer|LineReferenceId|ContractName|ReferenceId

-- Set 1

1111111|ABCD INC.|GOOD INC.|123456789_EXP|TEST Contract|123456789
1111111|ABCD INC.|XYZ TELECOM|123456789_TIME|TEST Contract|123456789

-- Set 2

222222|FASLSE NEWS INC.|LIVE NEWS INC.|789999_EXP|FALSE NEWS Contract|6789999
222222|FASLSE NEWS INC.|SKY NEWS INC.|789999_TIME|FALSE NEWS Contract|6789999

2. 拆分后的JSON数组格式 #

[
{
   "ContractNumber": "1111111",
   "Company": "ABCD INC.",
   "Contract_Lines_group": [
      {
         "LineShipToCustomer": "GOOD INC.",
         "LineReferenceId": "123456789_EXP"
      }
   ],
   "ContractName": "TEST Contract",
   "ReferenceId": "123456789"
}, 
{
   "ContractNumber": "1111111",
   "Company": "ABCD INC.",
   "Contract_Lines_group": [
      {
         "LineShipToCustomer": "XYZ TELECOM",
         "LineReferenceId": "123456789_TIME"
      }
   ],
   "ContractName": "TEST Contract",
   "ReferenceId": "123456789"
},
{
   "ContractNumber": "222222",
   "Company": "FASLSE NEWS INC.",
   "Contract_Lines_group": [
      {
         "LineShipToCustomer": "LIVE NEWS INC.",
         "LineReferenceId": "789999_EXP"
      }
   ],
   "ContractName": "FALSE NEWS Contract",
   "ReferenceId": "6789999"
},
{
   "ContractNumber": "222222",
   "Company": "FASLSE NEWS INC.",
   "Contract_Lines_group": [
      {
         "LineShipToCustomer": "SKY NEWS INC.",
         "LineReferenceId": "789999_TIME"
      }
   ],
   "ContractName": "FALSE NEWS Contract",
   "ReferenceId": "6789999"
}   
]

实现方案（Python） #

以下代码可同时生成两种预期输出：

import json

# 加载原始JSON数据
with open('input.json', 'r') as f:
    data = json.load(f)

report_entries = data['Report_Entry']
flattened_json = []
text_lines = []
header = "ContractNumber|Company|LineShipToCustomer|LineReferenceId|ContractName|ReferenceId"
text_lines.append(header)

# 遍历每个合同条目
for idx, entry in enumerate(report_entries, 1):
    text_lines.append(f"\n-- Set {idx}\n")
    contract_num = entry['ContractNumber']
    company = entry['Company']
    contract_name = entry['ContractName']
    ref_id = entry['ReferenceId']
    
    # 遍历每个合同行
    for line in entry['Contract_Lines_group']:
        # 生成扁平化文本行
        text_line = f"{contract_num}|{company}|{line['LineShipToCustomer']}|{line['LineReferenceId']}|{contract_name}|{ref_id}"
        text_lines.append(text_line)
        
        # 生成拆分后的JSON对象
        flattened_entry = entry.copy()
        flattened_entry['Contract_Lines_group'] = [line]
        flattened_json.append(flattened_entry)

# 保存竖线分隔文本
with open('output.txt', 'w') as f:
    f.write('\n'.join(text_lines))

# 保存拆分后的JSON
with open('flattened_output.json', 'w') as f:
    json.dump(flattened_json, f, indent=3)

数据库加载说明 #

• 竖线分隔文本：可直接使用数据库批量导入工具（如MySQL的LOAD DATA INFILE、PostgreSQL的COPY命令），指定分隔符为|即可完成加载。
• 拆分后的JSON数组：可通过编程语言遍历数组逐条插入数据库，或利用数据库JSON导入功能（如MongoDB直接导入，关系型数据库解析JSON字段后插入对应表）。

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