question How to convert two arrays to key value with Jolt? in Support Questions

How to convert two arrays to key value with Jolt?

DataD — Sat, 16 Nov 2019 16:49:16 GMT

I have some JSON that contains two arrays that I would like to convert to key/value pairs. I've already create a spec to convert my input data into the format below.

Input

{ "rows" : [ { "row" : [ "row1", "row2", "row3" ], "header" : [ "header1", "header2", "header3" ] }, { "row" : [ "row4", "row5", "row6" ], "header" : [ "header1", "header2", "header3" ] } ] }

Is it possible to convert it again with Jolt to something like:

{ "header1" : "row1", "header2" : "row2", "header3" : "row3", "header1" : "row4", "header2" : "row5", "header3" : "row6" }

Any guidance would be highly appreciated 🙂

Re: How to convert two arrays to key value with Jolt?

marcusmattson — Sat, 16 Nov 2019 22:19:13 GMT

If the original input data (before your Jolt transform) looks something like this...

{
  "headers": [
    "header1",
    "header2",
    "header3"
  ],
  "rows": [
    [
      "row1",
      "row2",
      "row3"
    ],
    [
      "row4",
      "row5",
      "row6"
    ]
  ]
}

...then it may be easier using SplitJSON and EvaluateJSONPath processors in this scenario.

The GenerateFlowFile processor has some sample data that matches the above format using books as the example. The result will be a separate JSON file for each array within "rows". You can configure the MergeContent to bundle the JSON records as needed.

EvaluateJSONPath

SplitJSON

EvaluateJSONPath

ReplaceText

Re: How to convert two arrays to key value with Jolt?

PVVK — Wed, 30 Sep 2020 18:25:54 GMT

Hi @DataD,

Please find the below spec:

[
 {
  "operation": "shift",
  "spec": {
   "rows": {
    "*": {
     "row": {
      "*": {
       "@": "[&3].@(3,header[&1])"
      }
     }
    }
   }
  }
 }
]

This will give the output as:

[ {
"header1" : "row1",
"header2" : "row2",
"header3" : "row3"
}, {
"header1" : "row4",
"header2" : "row5",
"header3" : "row6"
} ]

I didn't convert it to

{
"header1" : "row1",
"header2" : "row2",
"header3" : "row3",
"header1" : "row4",
"header2" : "row5",
"header3" : "row6"
}

because that is not a valid json as header1,2 and 3 are repeated keys in the same level of the json.