Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

JOLT Transform from flat repeating to nested array

Highlighted

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

Re: JOLT Transform from flat repeating to nested array

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