Unable to transform stringfy json array of objects to json object using jolt transformer

Question

Input json

"sales" : "[{\"Option\":\"Britania\",\"value\":\"200\"}{\"Option\":\"Parle\",\"value\":\"100\"}{\"Option\":\"mariegold\",\"value\":\"500\"}{\"Option\":\"snacks\",\"value\":\"200\"}]",

jolt transformer used

[
  {
    "operation": "modify-overwrite-beta",
    "spec": {
      "CREATIONDATETIME": "=substring(@(1,CREATIONDATETIME),0,19)"
    }
  },
  {
    "operation": "shift",
    "spec": {
      "sales": "extendedAttributes.salesValueOptions",
      "status": {
        "SUBMITTED": {
          "#submitted": "key6"
        }
      }
    }
  },
  {
    "operation": "modify-default-beta",
    "spec": {
      "key6": "pending"
    }
  }
]

Output needed

{
  "sales": {
    "option1": "Britannia",
    "value1": "0",
    "option2": "cadbury",
    "value2": "0",
    "option3": "Parle",
    "value3": "0"
  }
}

I have tried the different JsonSpecs provided at the JOLT github help page. But I am not able to solve this. Any help or pointers will be appreciated.

Is it okay in the output to get the keys having from zero suffix? Like "option0" : "Britannia", value0": "0","option1": "cadbury", "value1": "0", .. so on... — Arun Sai Mustyala, Sep 24 '21 at 13:14
This is the data that i am getting from the db it is saved inside a json object in this form and needed to convert it using jolt — DHANANJAY RAGHAV, Sep 25 '21 at 18:18
Then, how did you able to test your input on that [site](http://jolt-demo.appspot.com) ? Didn't you get a red alert when pressed the **JSON Validate** button...? — Barbaros Özhan, Sep 25 '21 at 18:22
Please clarify your specific problem or provide additional details to highlight exactly what you need. As it's currently written, it's hard to tell exactly what you're asking. — Community, Oct 03 '21 at 12:33

Barbaros Özhan · Answer 1 · 2021-09-25T14:08:53.387

If the index would start from zero as default, and no need to eliminate the case with Option = 'None of the above', then only one step of spec would be sufficient such as

[
  {
    "operation": "shift",
    "spec": {
      "*": {
        "*": {
          "*": "&2.&&1"
        }
      }
    }
  }
]

Output :

{
  "sales" : {
    "Option0" : "Britannia",
    "value0" : "0",
    "Option1" : "Cadbury",
    "value1" : "0",
    "Option2" : "Parle",
    "value2" : "0",
    "Option3" : "None of the above",
    "value3" : "0"
  }
}

if you want to get rid of the case with Option = 'None of the above', then apply the following spec

[
  {
    "operation": "shift",
    "spec": {
      "*": {
        "*": {
          "Option": {
            "None of the above": "",
            "*": {
              "@2": "&4.&3"
            }
          }
        }
      }
    }
  },
  {
    "operation": "shift",
    "spec": {
      "*": {
        "*": {
          "*": "&1"
        }
      }
    }
  },
  {
    "operation": "shift",
    "spec": {
      "*": {
        "*": {
          "*": "&2.&1.&"
        }
      }
    }
  },
  {
    "operation": "shift",
    "spec": {
      "*": {
        "*": {
          "*": "&2.&&1"
        }
      }
    }
  }
]

Output :

{
  "sales" : {
    "Option0" : "Britannia",
    "value0" : "0",
    "Option1" : "Cadbury",
    "value1" : "0",
    "Option2" : "Parle",
    "value2" : "0"
  }
}

While for your current case you can use the following multiple steps

[
  {
    "operation": "shift",
    "spec": {
      "*": {
        "*": {
          "Option": {
            "None of the above": "",
            "*": {
              "@2": "&4.&3"
            }
          }
        }
      }
    }
  },
  {
    "operation": "shift",
    "spec": {
      "*": {
        "*": {
          "*": "&1"
        }
      }
    }
  },
  {
    "operation": "shift",
    "spec": {
      "*": {
        "*": {
          "*": "&2.&1.&",
          "$": "&2.&1.idx"
        }
      }
    }
  },
  {
    "operation": "modify-overwrite-beta",
    "spec": {
      "*": {
        "*": {
          "idx": "=intSum(1,@(1,idx))"
        }
      }
    }
  },
  {
    "operation": "shift",
    "spec": {
      "*": {
        "*": {
          "*": "&2.@(1,idx).&"
        }
      }
    }
  },
  {
    "operation": "shift",
    "spec": {
      "*": {
        "*": {
          "*": "&2.&&1"
        }
      }
    }
  },
  {
    "operation": "remove",
    "spec": {
      "*": {
        "idx*": ""
      }
    }
  }
]

Output :

{
  "sales" : {
    "Option1" : "Britannia",
    "value1" : "0",
    "Option2" : "Cadbury",
    "value2" : "0",
    "Option3" : "Parle",
    "value3" : "0"
  }
}

where the first two step is to be used to get rid of the case with Option = 'None of the above' . modify-overwrite-beta spec is to generate incremented indexes("idx"). Those indexes are sticked to the key names as suffixes, and then they're removed at the last spec.

Tried using them here https://jolt-demo.appspot.com/#inception was getting null for both the answers provided — DHANANJAY RAGHAV, Sep 25 '21 at 17:42
@DHANANJAYRAGHAV No. I'm already using that site too. Check out [case1](https://usaupload.com/file/4W0S/j1.PNG) , [case2](https://usaupload.com/file/4W0T/j2.PNG) , [case3](https://usaupload.com/file/4W0U/j3.PNG) please. — Barbaros Özhan, Sep 25 '21 at 18:04

score 0 · Answer 2 · answered Sep 25 '21 at 13:40

Jolt Spec:

[
//First remove the NoneOfTheAbove Element from the array
  {
    "operation": "shift",
    "spec": {
      "sales": {
        "*": {
          "Option": {
            "None of the above": null,
            "*": {
              "@2": "filteredOutput"
            }
          }
        }
      }
    }
  },
  // then map the output accordingly
  {
    "operation": "shift",
    "spec": {
      "filteredOutput": {
        "*": {
          "Option": "sales.option&1",
          "value": "sales.value&1"
        }
      }
    }
  }


]

Output:

{
  "sales" : {
    "option0" : "Britannia",
    "value0" : "0",
    "option1" : "Cadbury",
    "value1" : "0",
    "option2" : "Parle",
    "value2" : "0"
  }
}

Please try and let me know if any issues.

Tried using them here jolt-demo.appspot.com/#inception was getting null for the answer provided — DHANANJAY RAGHAV, Sep 25 '21 at 17:42

Unable to transform stringfy json array of objects to json object using jolt transformer

2 Answers2