使用Jolt动态匹配值合并数组的JSON转换需求

输入JSON #

[
  {
    "getPatientDemographicDetailsOutput": [
      {
        "Message": "SUCCESS",
        "Results": [
          [
            {
              "APS_Age__c": "7 Years",
              "Id": "1234",
              "LastName": "LName0915-6",
              "APS_DOB__c": "2014-11-25",
              "Contacts__r": {
                "totalSize": 2,
                "records": [
                  {
                    "Alternate_Phone_1__c": "1458296321",
                    "Alternate_Phone_2__c": "(732) 318-3232",
                    "Correspondence_City__c": "Charlotte",
                    "Email_Address__c": "sohel@abc.com",
                    "Correspondence_State__c": "NC",
                    "State__c": "NC",
                    "Patient__c": "1234",
                    "Correspondence_Time_Zone__c": "EST",
                    "Correspondence_ZipCode_Name__c": "28222",
                    "Primary__c": true,
                    "Primary_Phone__c": "Mobile",
                    "Correspondence_Country__c": "USA",
                    "Country__c": "USA",
                    "Address_Line_2__c": "City",
                    "City__c": "Charlotte",
                    "Type__c": "Self",
                    "Zip_Code_Name__c": "28222",
                    "Address_Line_1__c": "Bangalore",
                    "Id": "11111",
                    "Correspond_Address_Line_1__c": "Bangalore",
                    "Correspond_Address_Line_2__c": "City",
                    "Mobile_Phone_for_Campaigns__c": "+1(732) 318-1232"
                  },
                  {
                    "Correspondence_City__c": "INTERNAL REVENUE SERVICE",
                    "Correspondence_State__c": "NY",
                    "Type__c": "Caregiver",
                    "Patient__c": "1234",
                    "Id": "22222",
                    "Correspond_Address_Line_1__c": "test address",
                    "Correspondence_Time_Zone__c": "EST",
                    "Correspondence_ZipCode_Name__c": "00501",
                    "Primary__c": false,
                    "Correspondence_Country__c": "USA"
                  }
                ],
                "done": true
              }
            }
          ],
          [
            {
              "Contact__c": "11111",
              "Text_Consent_Date__c": "2019-11-23",
              "Text_Messaging__c": "Yes",
              "Id": "54545454"
            }
          ]
        ]
      }
    ]
  }
]

预期输出JSON #

[
    {
        "APS_Age__c": "7 Years",
        "Id": "1234",
        "LastName": "LName0915-6",
        "APS_DOB__c": "2014-11-25",
        "Alternate_Phone_1__c": "1458296321",
        "Alternate_Phone_2__c": "(732) 318-3232",
        "Correspondence_City__c": "Charlotte",
        "Correspond_Address_Line_1__c": "Bangalore",
        "Correspond_Address_Line_2__c": "City",
        "Mobile_Phone_for_Campaigns__c": "+1(732) 318-1232",
        "Primary__c": true,
        "Text_Consent_Date__c": "2019-11-23",
        "Text_Messaging__c": "Yes"
    }
]

转换逻辑 #

• 提取Results[0][0]中的患者核心字段：APS_Age__c、Id、LastName、APS_DOB__c
• 筛选Contacts__r.records中Primary__c为true的主联系人，提取指定字段：Alternate_Phone_1__c、Alternate_Phone_2__c、Correspondence_City__c、Correspond_Address_Line_1__c、Correspond_Address_Line_2__c、Mobile_Phone_for_Campaigns__c、Primary__c
• 匹配Results[1]中Contact__c等于主联系人Id的条目，提取Text_Consent_Date__c和Text_Messaging__c
• 将上述所有字段合并为单个对象，最终输出为数组格式

Jolt转换规范 #

以下是实现该转换的Jolt规范：

[
  // 展开外层数组和getPatientDemographicDetailsOutput数组，拆分Results为patient和consent节点
  {
    "operation": "shift",
    "spec": {
      "*": {
        "getPatientDemographicDetailsOutput": {
          "*": {
            "Results": {
              "0": "patient",
              "1": "consent"
            }
          }
        }
      }
    }
  },
  // 提取患者核心字段，筛选主联系人并提取指定字段，保存主联系人ID用于匹配
  {
    "operation": "shift",
    "spec": {
      "patient": {
        "*": {
          "APS_Age__c": "&",
          "Id": "&",
          "LastName": "&",
          "APS_DOB__c": "&",
          "Contacts__r": {
            "records": {
              "*": {
                "Primary__c": {
                  "true": {
                    "@(2,Alternate_Phone_1__c)": "Alternate_Phone_1__c",
                    "@(2,Alternate_Phone_2__c)": "Alternate_Phone_2__c",
                    "@(2,Correspondence_City__c)": "Correspondence_City__c",
                    "@(2,Correspond_Address_Line_1__c)": "Correspond_Address_Line_1__c",
                    "@(2,Correspond_Address_Line_2__c)": "Correspond_Address_Line_2__c",
                    "@(2,Mobile_Phone_for_Campaigns__c)": "Mobile_Phone_for_Campaigns__c",
                    "@(2,Primary__c)": "Primary__c",
                    "@(2,Id)": "contactId"
                  }
                }
              }
            }
          }
        }
      },
      "consent": "&"
    }
  },
  // 通过主联系人ID匹配consent条目，提取同意字段，合并所有需要的字段
  {
    "operation": "shift",
    "spec": {
      "consent": {
        "*": {
          "Contact__c": {
            "@(3,contactId)": {
              "@(2,Text_Consent_Date__c)": "Text_Consent_Date__c",
              "@(2,Text_Messaging__c)": "Text_Messaging__c"
            }
          }
        }
      },
      "APS_Age__c": "&",
      "Id": "&",
      "LastName": "&",
      "APS_DOB__c": "&",
      "Alternate_Phone_1__c": "&",
      "Alternate_Phone_2__c": "&",
      "Correspondence_City__c": "&",
      "Correspond_Address_Line_1__c": "&",
      "Correspond_Address_Line_2__c": "&",
      "Mobile_Phone_for_Campaigns__c": "&",
      "Primary__c": "&"
    }
  },
  // 将结果包装为数组，匹配预期输出格式
  {
    "operation": "shift",
    "spec": {
      "*": "[]"
    }
  }
]

规范说明 #

1. 第一步Shift：拆解输入层级，将Results的两个子数组分别映射为patient和consent节点，简化后续字段访问
2. 第二步Shift：提取患者核心信息，筛选出主联系人并提取目标字段，同时留存主联系人ID用于后续匹配
3. 第三步Shift：通过主联系人ID匹配consent中的对应条目，提取同意相关字段，合并所有需要的字段
4. 第四步Shift：将最终的单个对象包装为数组，对齐预期输出结构

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