Transform to json format

[akhil_dhir_14]

Please help to transform below input json to mentioned output format.
Noteworthy: Input JSON start as an object and there is another object 2 within it. Output JSON expectation is to merge into 1 object.

Input JSON:

[
{
“Key 1” : “12345”,
“Key 2” : “6789”,
“Key 3” : “abc-123”
},
[
{
“object 2” : " "
},
{
“Key 4” : “00002”,
“Key 5” : “N/A”,
“Key 6” : “Success”
},
{
“Key 7” : “00001”,
“Key 8” : “Column Datatype check failed”,
“Key 9” : “Fail”
},
{
“Key 10” : “00003”,
“Key 11” : “Sheet Validation check failed”,
“Key 12” : “Fail”
}
]
]

Output JSON:

{ “Key 1”:“12345” ,
“Key 2”: “6789”,
“Key 3”: “abc-123”,

"object 1": [
  {
    "Key 4": "00002",
    "Key 5" : "N/A",
    "Key 6": "Success"
  },
  {
    "Key 7": "00001",
    "Key 8" : "Column Datatype check failed",
    "Key 9": "Fail"
  },
  {
    "Key 10": "00003",
    "Key 11" : "Sheet Validation check failed",
    "Key 12": "Fail"
  }
]

}

[takbb]

Hi @akhil_dhir_14 , welcome to the KNIME community.

I don’t know if you have managed to resolve your problem already, as you posted about this 4 days ago. I was wondering if your actual question is more generic than it appears, as it seems very specific to the particular data that you have. This always concerns me a little, since if you are trying to have some form of general process that can be used to achieve future transformations, it may be that certain “rules” need to be stated.

For example, should you always just remove “object 2” from the result. Is “object 2” always at the beginning of the json array?

Without additional more general information, the solution I have come up with achieves the required output based on the example data given, but isn’t particularly “general purpose”, so that may be what you are requiring, but if you need something more generalised then you will need to specify more detail.

I suspect that the question of providing a more generalised solution is the primary reason for the lack of responses. Certainly it had given me to question how this solution should be approached.

Anyway, I hope this is useful, or at least gives some pointers.

79102 - transform json - 1.knwf (25.7 KB)

[akhil_dhir_14]

@takbb Thank you so much for taking out time to provide this solution.

Yes, I did manage to solve by using series of Column expressions with replace function etc. , but that doesn’t sound to be an apt solution as compared with yours. Also, I did miss a trick of Groupby(List) and converting back to JSON.

Way I handled:
replace(replace(column(“JSON”), “[[”,“[” ),“]]”,“]”)
replace(replace(replace(column(“JSON (#1)”),“[{"Key 1” ,“{"Key 1” ),“},{"Object 1”,“,"Object 1”),“}]}]”, “}]}”)

Yes, you’re correct this is not a generalized requirement, which may vary if JSON structure changes tomorrow. Having said that, I am expecting entries to be dynamic in the inner object and I feel this can also be managed within your solution.

I happen to have a way forward from here. Thanks again for working this out.

[takbb]

Good to know you managed to find a solution @akhil_dhir_14 and glad to hear that this may be helpful for the future