Jolt transformation Multiple level of arrays

Abhinav Prakash 0
Viewed 27

I am trying to write a jolt specification to be able to transform an input json. I have the following Input:

[
  {
    "classId": 213,
    "className": "Science",
    "classAnimal": "Lion",
    "schoolId": 1,
    "teacherId": 22,
    "teacherName": "John",
    "studentId": "student01",
    "studentName": "Ron"
  },
  {
    "classId": 213,
    "className": "Science",
    "classAnimal": "Lion",
    "schoolId": 1,
    "teacherId": 23,
    "teacherName": "Rina",
    "studentId": "student02",
    "studentName": "Ria"
  },
  {
    "classId": 214,
    "className": "Robotics",
    "classAnimal": "Cow",
    "schoolId": 1,
    "teacherId": 25,
    "teacherName": "Lin",
    "studentId": "student03",
    "studentName": "Roman"
  },
  {
    "classId": 214,
    "className": "Robotics",
    "classAnimal": "Cow",
    "schoolId": 1,
    "teacherId": 27,
    "teacherName": "Albert",
    "studentId": "student02",
    "studentName": "Krish"
  },
  {
    "classId": 214,
    "className": "Robotics",
    "classAnimal": "Cow",
    "schoolId": 1,
    "teacherId": 24,
    "teacherName": "Nova",
    "studentId": "student04",
    "studentName": "Kumar"
  },
  {
    "classId": 218,
    "className": "Arts",
    "classAnimal": "Rabbit",
    "schoolId": 2,
    "teacherId": 33,
    "teacherName": "Mihir",
    "studentId": "student08",
    "studentName": "Laya"
  },
  {
    "classId": 225,
    "className": "Economics",
    "classAnimal": "Horse",
    "schoolId": 2
  }
]

The expected output after jolt transform is:

[
  {
    "schoolList": [
      {
        "schoolId": 1,
        "classList": [
          {
            "classId": 213,
            "className": "Science",
            "classAnimal": "Lion",
            "teachers": [
              {
                "teacherId": 22,
                "teacherName": "John"
              },
              {
                "teacherId": 23,
                "teacherName": "Rina"
              }
            ],
            "students": {
              "student02": "Ria",
              "student01": "Ron"
            }
          },
          {
            "classId": 214,
            "className": "Robotics",
            "classAnimal": "cow",
            "teachers": [
              {
                "teacherId": 25,
                "teacherName": "Lin"
              },
              {
                "teacherId": 27,
                "teacherName": "Albert"
              },
              {
                "teacherId": 24,
                "teacherName": "Nova"
              }
            ],
            "students": {
              "student02": "Krish",
              "student03": "Roman",
              "student04": "Kumar"
            }
          }
        ]
      },
      {
        "schoolId": 2,
        "classList": [
          {
            "classId": 218,
            "className": "Arts",
            "classAnimal": "Rabbit",
            "teachers": [
              {
                "teacherId": 33,
                "teacherName": "Mihir"
              }
            ],
            "students": {
              "student08": "Laya"
            }
          },
          {
            "classId": 225,
            "className": "Economics",
            "classAnimal": "Horse",
            "teachers": [
              
            ]
          }
        ]
      }
    ]
  }
]

I am having problem in traversing and then merging the data in groups as in the expected output. I am able to create grouping of SchoolList but for the deeper groupings I am facing problems. Mainly bringing the teachers array and students array together is a challenge.

Could someone please help. I would be grateful.

jolt json arrays
1 Answers

You can use the following transformation specs starting with determining the objects separated by @(..,schoolId) and @(..,classId) identifiers such as

[
  {
    "operation": "shift",
    "spec": {
      "*": {
        "schoolId": "@(1,schoolId).&",
        "*": "@(1,schoolId).classList[@(1,classId)].&",
        "teacher*": {
          "@": "@(2,schoolId).classList[@(2,classId)].teachers[&2].&"
        },
        "stu*": {
          "@(1,studentName)": "@(2,schoolId).classList[@(2,classId)].students.@(2,studentId)"
        }
      }
    }
  },
  {
    // get rid of repeated components of each array for those marked with ONE
    "operation": "cardinality",
    "spec": {
      "*": {
        "schoolId": "ONE",
        "*": { //level of "classList"
          "*": {
            "*": "ONE",
            "teachers": "MANY",
            "students": {
              "*": "ONE"
            }
          }
        }
      }
    }
  },
  {
    // get rid of null elements generated due to [@(2,classId)] usage of "classList" array
    "operation": "modify-overwrite-beta",
    "spec": {
      "*": "=recursivelySquashNulls"
    }
  },
  {
    // sort "schoolId" to be the first, and "classList" to be the second in the order while nesting all content within "schoolList" array
    "operation": "shift",
    "spec": {
      "*": {
        "schoolId": "[0].schoolList[#2].&", // "[0]" stands for adding extra nesting square brackets at the outermost level
        "classList": "[0].schoolList[#2].&"
      }
    }
  }
]
Barbaros Özhan0
Related