JOLT - Transform Array of JSON objects containing nested Arrays into their respective Array group

Question

I want to transform a JSON input file into expected JSON output file:

My input file:

[
  {
    "category": [
      {
        "id": "id1",
        "name": "Connected Home & Housewares1"
      },
      {
        "id": "id2",
        "name": "Housewares1"
      },
      {
        "id": "id3",
        "name": "Household Batteries1"
      },
      {
        "id": "id4",
        "name": "Alkaline Batteries1"
      }
    ]
  },
  {
    "category": [
      {
        "id": "id1",
        "name": "Connected Home & Housewares2"
      },
      {
        "id": "id2",
        "name": "Housewares2"
      },
      {
        "id": "id3",
        "name": "Household Batteries2"
      },
      {
        "id": "id4",
        "name": "Alkaline Batteries2"
      }
    ]
  }
]

(Above is just an example of 2 records, but it contains ~50k records)

Expected Output:

[
  {
    "childSKUs": [
      {
        "divisionName": "Connected Home & Housewares1",
        "deptName": "Housewares1",
        "className": "Household Batteries1"
      }
    ]
  },
  {
    "childSKUs": [
      {
        "divisionName": "Connected Home & Housewares2",
        "deptName": "Housewares2",
        "className": "Household Batteries2"
      }
    ]
  }
]

My current JOLT Spec:

[
  {
    "operation": "shift",
    "spec": {
      "*": {
        "category": {
          "0": {
            "name": "[0].childSKUs[&1].divisionName"
          },
          "1": {
            "name": "[0].childSKUs[&1].deptName"
          },
          "2": {
            "name": "[0].childSKUs[&1].className"
          }
        }
      }
    }
  }
]

But it is only giving following output:

[
  {
    "childSKUs": [
      {
        "divisionName": [
          "Connected Home & Housewares1",
          "Connected Home & Housewares2"
        ]
      },
      {
        "deptName": [
          "Housewares1",
          "Housewares2"
        ]
      },
      {
        "className": [
          "Household Batteries1",
          "Household Batteries2"
        ]
      }
    ]
  }
]

I tried various options by playing in https://jolt-demo.appspot.com but no luck so far. Any help is appreciated. Would also be great if you explain your SPEC how was it done.

score 0 · Answer 1 · answered Nov 07 '19 at 10:04

Ok, I figured it out myself.

Thanks to @Pokuri's answer here which gave me the idea to arrive at the solution: https://stackoverflow.com/a/55861132/248847

Correct JOLT SPEC for above question:

[
  {
    "operation": "shift",
    "spec": {
      "*": {
        "category": {
          "0": {
            "name": "[&3].childSKUs[&1].divisionName"
          },
          "1": {
            "name": "[&3].childSKUs[0].deptName"
          },
          "2": {
            "name": "[&3].childSKUs[0].className"
          }
        }
      }
    }
  }
]

JOLT - Transform Array of JSON objects containing nested Arrays into their respective Array group

1 Answers1