如何通过Datawave转换去除JSON文件中基于account_id的重复账户

需要处理一个包含12个账户的JSON文件，通过account_id字段去除重复账户，确保所有账户唯一，同时保留文件中的address、PhoneNumbers等其他数据，最终输出需包含7个唯一账户。

输入JSON #

{
    "Accounts": [
        {
            "account_id": "c93c9cc0-26b4-11ed-a261-0242ac120002",
            "account_Number": 458554,
            "account_name": "Ramu"
        },
        {
            "account_id": "e4b0efdc-26b5-11ed-a261-0242ac120002",
            "accountNumber": 741852,
            "account_name": "Rajesh"
        },
        {
            "account_id": "027aa0f8-26b6-11ed-a261-0242ac120002",
            "accountNumber": 963258,
            "account_name": "Harsha"
        },
        {
            "account_id": "09ebdd0c-26b6-11ed-a261-0242ac120002",
            "accountNumber": 852456,
            "account_name": "Vamsi"
        }
    ],
    "address": [
        {
            "street": "XXXX",
            "state": "XXXX",
            "country": "XXXX"
        }
    ],
    "Accounts": [
        {
            "account_id": "f4974e1e-26b5-11ed-a261-0242ac120002",
            "accountNumber": 246598,
            "account_name": "Indu"
        },
        {
            "account_id": "2fa15b30-26b6-11ed-a261-0242ac120002",
            "accountNumber": 789789,
            "account_name": "Suresh"
        },
        {
            "account_id": "c93c9cc0-26b4-11ed-a261-0242ac120002",
            "accountNumber": 458554,
            "account_name": "Ramu"
        },
        {
            "account_id": "e4b0efdc-26b5-11ed-a261-0242ac120002",
            "accountNumber": 741852,
            "account_name": "Rajesh"
        }
    ],
    "PhoneNumbers": [
        {
            "phcountry": "XXXX",
            "phno": "XXXX"
        }
    ],
    "Accounts": [
        {
            "account_id": "09ebdd0c-26b6-11ed-a261-0242ac120002",
            "accountNumber": 852456,
            "account_name": "Vamsi"
        },
        {
            "account_id": "c93c9cc0-26b4-11ed-a261-0242ac120002",
            "account_Number": 458554,
            "account_name": "Ramu"
        },
        {
            "account_id": "2ad45a96-b907-4e2e-ae90-4f429c3fc0e4",
            "accountNumber": 741852,
            "account_name": "Savitri"
        },
        {
            "account_id": "f4974e1e-26b5-11ed-a261-0242ac120002",
            "accountNumber": 246598,
            "account_name": "Indu"
        }
    ]
}

期望输出（7个唯一账户） #

{
    "Accounts": [
        {
            "account_id": "c93c9cc0-26b4-11ed-a261-0242ac120002",
            "account_Number": 458554,
            "account_name": "Ramu"
        },
        {
            "account_id": "e4b0efdc-26b5-11ed-a261-0242ac120002",
            "accountNumber": 741852,
            "account_name": "Rajesh"
        },
        {
            "account_id": "027aa0f8-26b6-11ed-a261-0242ac120002",
            "accountNumber": 963258,
            "account_name": "Harsha"
        },
        {
            "account_id": "09ebdd0c-26b6-11ed-a261-0242ac120002",
            "accountNumber": 852456,
            "account_name": "Vamsi"
        }
    ],
    "address": [
        {
            "street": "XXXX",
            "state": "XXXX",
            "country": "XXXX"
        }
    ],
    "Accounts": [
        {
            "account_id": "f4974e1e-26b5-11ed-a261-0242ac120002",
            "accountNumber": 246598,
            "account_name": "Indu"
        },
        {
            "account_id": "2fa15b30-26b6-11ed-a261-0242ac120002",
            "accountNumber": 789789,
            "account_name": "Suresh"
        }
    ],
    "PhoneNumbers": [
        {
            "phcountry": "XXXX",
            "phno": "XXXX"
        }
    ],
    "Accounts": [
        {
            "account_id": "2ad45a96-b907-4e2e-ae90-4f429c3fc0e4",
            "accountNumber": 741852,
            "account_name": "Savitri"
        }
    ]
}

解决方案 #

原JSON存在多个同名Accounts键，标准JSON解析器会覆盖重复键，因此需要先自定义解析逻辑保留所有Accounts数组，再基于account_id去重。以下是Python实现代码：

import json

def parse_duplicate_keys(pairs):
    """处理JSON中的重复键，将同名键的值合并为列表"""
    result = {}
    for key, value in pairs:
        if key in result:
            if isinstance(result[key], list):
                result[key].append(value)
            else:
                result[key] = [result[key], value]
        else:
            result[key] = value
    return result

# 读取输入JSON文件
with open('input.json', 'r', encoding='utf-8') as f:
    data = json.load(f, object_pairs_hook=parse_duplicate_keys)

# 记录已出现的account_id
seen_ids = set()

# 遍历所有Accounts数组，过滤重复账户
if isinstance(data['Accounts'], list):
    for account_list in data['Accounts']:
        filtered = []
        for acc in account_list:
            acc_id = acc['account_id']
            if acc_id not in seen_ids:
                filtered.append(acc)
                seen_ids.add(acc_id)
        # 替换原数组为过滤后的结果
        account_list[:] = filtered

# 将处理后的数据写入输出文件
with open('output.json', 'w', encoding='utf-8') as f:
    json.dump(data, f, indent=4)

代码说明 #

1. 重复键解析：parse_duplicate_keys函数将JSON中重复的Accounts键对应的值合并为列表，确保所有账户数组都被保留。
2. 去重逻辑：使用集合seen_ids跟踪已出现的account_id，遍历每个账户数组时，只保留首次出现的账户，后续重复的直接过滤。
3. 保留其他字段：处理过程中不会修改address、PhoneNumbers等其他字段，仅对Accounts数组做去重操作。

运行代码后，输出的output.json将与期望输出一致，包含7个唯一账户。

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