Support Questions
Find answers, ask questions, and share your expertise
Announcements
Check out our newest addition to the community, the Cloudera Innovation Accelerator group hub.

JOLT Transform from flat repeating to nested array

New Contributor

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

New Contributor

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"
	}
	]
}