Support Questions

Find answers, ask questions, and share your expertise
Announcements
Celebrating as our community reaches 100,000 members! Thank you!

JOLT Transform from flat repeating to nested array

avatar
Explorer

I'm trying to transform flat repeating values from flat JSON to nested Array.In the below is a sample, there are IN1_1 and IN1_2 repeating values. The numeric being the index. I've tried a number of different JOLT specs, but can't seem to get it right.

{
"IN1_1.NameOfInsured.DegreeEgMD": "MME",
"IN1_2.NameOfInsured.FamilyName": "SEIBERT",
"IN1_1.NameOfInsured.GivenName": "GALE",
"IN1_2.InsuredsRelationshipToPatient.Identifier": "SELF",
"IN1_2.InsurancePlanID.Identifier": "Medicare",
"IN1_1.NameOfInsured.SecondAndFurtherGivenNamesOrInitialsThereof": "MATTHEW",
"IN1_2.NameOfInsured.SecondAndFurtherGivenNamesOrInitialsThereof": "MATTHEW",
"IN1_2.SetIDIN1": "2",
"IN1_2.NameOfInsured.DegreeEgMD": "MME",
"IN1_1.NameOfInsured.FamilyName": "SEIBERT",
"IN1_1.InsuredsRelationshipToPatient.Identifier": "SELF",
"IN1_2.NameOfInsured.GivenName": "GALE",
"IN1_1.SetIDIN1": "1",
"IN1_1.InsurancePlanID.Identifier": "UNIT HLTH"
}

Desired output

{
	"Insurance":[
	{
		"IN1_1.SetIDIN1": "1",
		"IN1_1.InsurancePlanID.Identifier": "UNIT HLTH",
		"IN1_1.InsuredsRelationshipToPatient.Identifier": "SELF",
		"IN1_1.NameOfInsured.SecondAndFurtherGivenNamesOrInitialsThereof": "MATTHEW",
		"IN1_1.NameOfInsured.GivenName": "GALE",
		"IN1_1.NameOfInsured.FamilyName": "SEIBERT",
		"IN1_1.NameOfInsured.DegreeEgMD": "MME"
	 },
	 {
		"IN1_2.SetIDIN1": "2",
		"IN1_2.InsurancePlanID.Identifier": "Medicare",
		"IN1_2.InsuredsRelationshipToPatient.Identifier": "SELF",
		"IN1_2.NameOfInsured.SecondAndFurtherGivenNamesOrInitialsThereof": "MATTHEW",
		"IN1_2.NameOfInsured.GivenName": "GALE",
		"IN1_2.NameOfInsured.FamilyName": "SEIBERT",
		"IN1_2.NameOfInsured.DegreeEgMD": "MME"
	}
	]
}
1 REPLY 1

avatar
Explorer

I've gotten a little closer using pivots, but now i want to remove the pivot values that make the nested arrays.... My spec:

[
  {
    //split IN1.* and pivot to key value pairs
    "operation": "shift",
    "spec": {
      "IN1_*.*": {
        "$(0,1)": "pivoted.&1.key1",
        "$(0,2)": "pivoted.&1.key2",
        "@": "pivoted.&1.value"
      }
    }
  },
  {
    "operation": "shift",
    "spec": {
      "pivoted": {
        "*": {
          "value": "@(1,key1).@(1,key2)"
        }
      }
    }
  }
]

New output:

{
  "1" : {
    "SetIDIN1" : "1",
    "InsurancePlanID.Identifier" : "UNIT HLTH",
    "InsuredsRelationshipToPatient.Identifier" : "SELF",
    "NameOfInsured.SecondAndFurtherGivenNamesOrInitialsThereof" : "MATTHEW",
    "NameOfInsured.GivenName" : "GALE",
    "NameOfInsured.FamilyName" : "SEIBERT",
    "NameOfInsured.DegreeEgMD" : "MME"
  },
  "2" : {
    "SetIDIN1" : "2",
    "InsurancePlanID.Identifier" : "Medicare",
    "InsuredsRelationshipToPatient.Identifier" : "SELF",
    "NameOfInsured.SecondAndFurtherGivenNamesOrInitialsThereof" : "MATTHEW",
    "NameOfInsured.GivenName" : "GALE",
    "NameOfInsured.FamilyName" : "SEIBERT",
    "NameOfInsured.DegreeEgMD" : "MME"
  }
}

Desired output:

{
	"Insurance":[
	{
		"IN1_1.SetIDIN1": "1",
		"IN1_1.InsurancePlanID.Identifier": "UNIT HLTH",
		"IN1_1.InsuredsRelationshipToPatient.Identifier": "SELF",
		"IN1_1.NameOfInsured.SecondAndFurtherGivenNamesOrInitialsThereof": "MATTHEW",
		"IN1_1.NameOfInsured.GivenName": "GALE",
		"IN1_1.NameOfInsured.FamilyName": "SEIBERT",
		"IN1_1.NameOfInsured.DegreeEgMD": "MME"
	 },
	 {
		"IN1_2.SetIDIN1": "2",
		"IN1_2.InsurancePlanID.Identifier": "Medicare",
		"IN1_2.InsuredsRelationshipToPatient.Identifier": "SELF",
		"IN1_2.NameOfInsured.SecondAndFurtherGivenNamesOrInitialsThereof": "MATTHEW",
		"IN1_2.NameOfInsured.GivenName": "GALE",
		"IN1_2.NameOfInsured.FamilyName": "SEIBERT",
		"IN1_2.NameOfInsured.DegreeEgMD": "MME"
	}
	]
}