Jolt Transformation to "unpivot" repeating columns into nested JSON array

user_1809704 0
Viewed 164

My source data is a huge flatten CSV which contains data that I'd like to normalise and store in a number of separate database tables. I'm wondering if I can use a JOLT transformation to alter the structure of my CSV data which I've already a flat JSON object.

For example, the object below has two 'parent' rows and three repeating pairs of columns which are the 'child' rows:

{
  "id": 1,
  "name": "zzz",
  "code_1": "abc",
  "date_1": "2021-01-01",
  "code_2": "def",
  "date_2": "2021-01-02",
  "code_3": "ghi",
  "date_3": "2021-01-03"
}

Can I use JOLT to generate the output:

{
  "id": 1,
  "name": "zzz",
  "codes": [
    {
      "code": "abc",
      "date": "2021-01-01"
    },
    {
      "code": "def",
      "date": "2021-01-02"
    },
    {
      "code": "ghi",
      "date": "2021-01-03"
    }
  ]
}

I've had a play around with the Jolt Playground, but as yet I've not found something which could achieve this goal.

Any hints, tips, or pointers welcome.

Thanks in advance.

UPDATE:

I'm getting closer, with the following spec and output. My codes and dates are in different objects however:

spec:

[
  {
    "operation": "shift",
    "spec": {
      "identifier": "id",
      "name": "name",
      "code*": "codes[].code",
      "date*": "codes[].date"
    }
  }
]

output:

{
  "id" : 1,
  "name" : "zzz",
  "codes" : [ {
    "code" : "abc"
  }, {
    "date" : "2021-01-01"
  }, {
    "code" : "def"
  }, {
    "date" : "2021-01-02"
  }, {
    "code" : "ghi"
  }, {
    "date" : "2021-01-03"
  } ]
}

(Edit: corrected the required output.)

jolt json
1 Answers

You can use shift transformations twice as the elements are grouped under common numbered keys by _1,_2,_3 in the first step, and then remove keys of those objects while nesting them within codes list such as

[
  {
    "operation": "shift",
    "spec": {
      "*": "&",
      "code*": { "@": "&(0,1).code" },
      "date*": { "@": "&(0,1).date" }
    }
  },
  {
    "operation": "shift",
    "spec": {
      "_*": "codes[]",
      "*": "&"
    }
  }
]
user_58413060
Related