Jolt Transform - Unable to transform json array into elements are objects with the specified key/value pairs

Question

I'm trying to transform JSON array into elements are objects with the specified key/value pairs

{
  "Resource": "sorPersonRole",
  "Roleid": "1",
  "Timestamp": "2010-06-30 00:00:00.0",
  "Release": "Public",
  "DOB": "2064-09-05",
  "Active": "Y",
  "enterprise_id": "a12s33",
  "Inactive_enterprise_id": "",
  "emp_ID": "123456",
  "Inactive_emp_id": "000821972",
  "Username": "",
  "A_ID": "fsgf1234jhgfs3",
  "P_ID": "w123456",
  "Is Email Valid": "Y",
  "Flag": "N",
  "Registered": "spring",
  "Description": "mainland corp",
  "End Date": null
}

Expected output:

{
  "meta" : {
    "Resource" : "sorPersonRole",
    "Roleid" : "1",
    "Timestamp" : "2010-06-30 00:00:00.0",
    "Release" : "Public",
    "Sorid" : "w123456"
  },
  "sorAttributes" : {
    "DOB" : "2064-09-05",
    "Active" : "Y",
    "End Date" : null,
    "identifiers":
    [
        {
          "type" : "enterprise" 
          "enterprise_id" : "a12s33",
          "Username" : ""
        },
        {
          "type" : "former-enterprise"
          "Inactive_enterprise_id" : ""
        },
        {
          "type" : "UID"
          "emp_ID" : "123456",
          "Inactive_emp_id" : "000821972"
        },
        {
          "type" : "National"
          "A_ID" : "fsgf1234jhgfs3"
        }
    ],
    "mainLand:com:adhoc" : {
      "Is Email Valid" : "Y",
      "Flag" : "N",
      "Registered" : "spring",
      "Description" : "mainland corp"
    }
  }
}

current Jolt spec: which I am not getting desired output

[
  {
    "operation": "shift",
    "spec": {
      "Resource": "meta.&",
      "P_ID": "meta.Sorid",
      "Roleid": "meta.&",
      "Timestamp": "meta.&",
      "Release": "meta.&",
      "enterprise_id": "sorAttributes.Identifiers.type.enterprise.&",
      "Inactive_enterprise_id": "sorAttributes.Identifiers.type.former-enterprise.&",
      "emp_ID": "sorAttributes.Identifiers.type.UID.&",
      "Inactive_emp_id": "sorAttributes.Identifiers.type.UID.&",
      "Username": "sorAttributes.Identifiers.type.enterprise.&",
      "A_ID": "sorAttributes.Identifiers.type.National.&",
      "Is Email Valid": "sorAttributes.mainLand:com:adhoc.&",
      "Flag": "sorAttributes.mainLand:com:adhoc.&",
      "Registered": "sorAttributes.mainLand:com:adhoc.&",
      "Description": "sorAttributes.mainLand:com:adhoc.&",
      "*": "sorAttributes.&"
    }
    }

]

I have tried the different JsonSpecs provided on different websites, could able to match expected output. Also tried using two-shift operations but no luck, Any help or suggestion will be appreciated.

Thanks.

Jagadesh Jagadesh · Accepted Answer · 2020-07-25T09:07:22

This can help,

For the nodes to be shifted into the identifier array, shift one level more.

[
  {
    "operation": "shift",
    "spec": {
      "Resource": "meta.&",
      "Roleid": "meta.&",
      "Timestamp": "meta.&",
      "Release": "meta.&",
      "P_ID": "meta.Sorid",
      "DOB": "sorAttributes.&",
      "Active": "sorAttributes.&",
      "End Date": "sorAttributes.&",
      "Is Email Valid": "sorAttributes.mainLand:com:adhoc.&",
      "Flag": "sorAttributes.mainLand:com:adhoc.&",
      "Registered": "sorAttributes.mainLand:com:adhoc.&",
      "Description": "sorAttributes.mainLand:com:adhoc.&",
      "enterprise_id": {
        "#enterprise": "sorAttributes.identifiers[#2].type",
        "@": "sorAttributes.identifiers[#2].&",
        "@(1,Username)": "sorAttributes.identifiers[#2].Username"
      },
      "Inactive_enterprise_id": {
        "#former-enterprise": "sorAttributes.identifiers[#2].type",
        "@": "sorAttributes.identifiers[#2].&"
      },
      "Inactive_emp_id": {
        "#UID": "sorAttributes.identifiers[#2].type",
        "@": "sorAttributes.identifiers[#2].&",
        "@(1,emp_ID)": "sorAttributes.identifiers[#2].emp_ID"
      },
      "A_ID": {
        "#National": "sorAttributes.identifiers[#2].type",
        "@": "sorAttributes.identifiers[#2].&"
      }
    }
  }, {
    "operation": "modify-overwrite-beta",
    "spec": {
      "*": "=recursivelySquashNulls"
    }
}
]

Jolt Transform - Unable to transform json array into elements are objects with the specified key/value pairs

1 Answers