Search code examples
apache-nifijolt

JOLT- How to sort data basis on json key value pair


I would like to sort the data of JSON Object key values and the arrangement should be in ascending format. and the second challenge i can see this, number of questions and answers count is not fixed to three, can be increase or decreases.

Input :

{
  ".0.answer": "In tag functionality ",
  ".0.question": "Provide a brief description",
  ".0.questionSequence": 1,
  ".1.answer": "In Git if we want to know",
  ".1.question": "Use Case Description ",
  ".1.questionSequence": 3,
  ".2.answer": "In Git if we want to know PIT",
  ".2.question": "Business Justification ",
  ".2.questionSequence": 2
}

Desired output :

{
  ".0.answer": "In tag functionality ",
  ".0.question": "Provide a brief description",
  ".0.questionSequence": 1,
  ".2.answer": "In Git if we want to know PIT",
  ".2.question": "Business Justification ",
  ".2.questionSequence": 2,
  ".1.answer": "In Git if we want to know",
  ".1.question": "Use Case Description ",
  ".1.questionSequence": 3
}

JOLT :

[
  {
    "operation": "shift",
    "spec": {
      "*": "*questionSequence"
    }
  },
  {
    "operation": "sort"
  }
]

Solution

  • I think I got it. please read the comments on each spec to understand how we get to the desired result:

    [
      // group each question\answer\sequence under
      // their own parent using the index attached to each field 0,1,2...
      {
        "operation": "shift",
        "spec": {
          ".*.*": {
            "@": "&(0,1).&"
          }
        }
      }
      ,
      //create new sequence field of string type based
      // on the integer value of .*.questionSequence. The
      // new sequence will be used for sorting, but since
      // its number string we need to make sure that the order
      // remains correct even when the number goes into a new
      // tenth digit 10s,20s,30s... To overcome this we can 
      // add prefix represent number of digits in a string number
      // For example 2=>1.2 , 12=>2.12, 102=>3.102 and so on.
      // This will ensure that 9 will come before 10 because
      // it will be stored as 1.9 and 2.10
      {
        "operation": "modify-overwrite-beta",
        "spec": {
          "*": {
            "sequenceOrderStr": "=toString(@(1,\\.&1\\.questionSequence))",
            "numberOfDigit": "=size(@(1,sequenceOrderStr))",
            "finalSequenceOrder": "=concat(@(1,numberOfDigit),'.',@(1,sequenceOrderStr))"
          }
        }
      }
      ,
      // regroup each question\anwer\sequence field
      // under thew new sequence order field created above
      {
        "operation": "shift",
        "spec": {
          "*": {
            ".*.*": "@(1,finalSequenceOrder).&"
          }
        }
      }
      ,
      // sort based on the finalSequenceOrder value
      {
        "operation": "sort"
      },
      // re structure to flatten the list as 
      // the original input but with the correct order
      {
        "operation": "shift",
        "spec": {
          "*": {
            "*": "&"
          }
        }
      }
    ]