Jolt Transform Nested Grouping

Question

I have a JSON that has this flat structure:

[{
    "PK": "1111",
    "SOURCE_DB": "Oracle",
    "CONTACT_TYPE": "Phone",
    "CONTACT_SUBTYPE": "Work",
    "EMAIL": null
    "PHONE_COUNTRY_CODE": "44",
    "PHONE_NUMBER": "12345678",
    "PHONE_EXT": "907643",
    "STATUS": "Active"
    }, {
    "PK": "1111",
    "SOURCE_DB": "Oracle",
    "CONTACT_TYPE": "Phone",
    "CONTACT_SUBTYPE": "Home",
    "EMAIL": null
    "PHONE_COUNTRY_CODE": "353",
    "PHONE_NUMBER": "87654321",
    "PHONE_EXT": null,
    "STATUS": "Active"
    }, {
    "PK": "1111",
    "SOURCE_DB": "",
    "CONTACT_TYPE": "Email",
    "CONTACT_SUBTYPE": "Personal",
    "EMAIL": "me@mail.com"
    "PHONE_COUNTRY_CODE": null,
    "PHONE_NUMBER": null,
    "PHONE_EXT": null,
    "STATUS": "Active"
    },
    {
    "PK": "2222",
    "SOURCE_DB": "DB2",
    "CONTACT_TYPE": "Phone",
    "CONTACT_SUBTYPE": "Home",
    "EMAIL": null
    "PHONE_COUNTRY_CODE": "44",
    "PHONE_NUMBER": "98761234",
    "PHONE_EXT": null,
    "STATUS": "Inactive"
    }, {
    "PK": "2222",
    "SOURCE_DB": "DB2",
    "CONTACT_TYPE": "Email",
    "CONTACT_SUBTYPE": "Work",
    "EMAIL": "you@mail.co.uk"
    "PHONE_COUNTRY_CODE": null,
    "PHONE_NUMBER": null,
    "PHONE_EXT": null,
    "STATUS": "Active"
    }
]

Then, I want to group them, first by Key (PK), then within each entry, ContactMethods will be grouped together. This is the output:

{
    "Accounts": [{
            "Reference": {
                "Key": "1111",
                "System": "Oracle"
            },
            "ContactMethods": {
                "Phone": [{
                        "Subtype": "Work",
                        "CountryCode": "44",
                        "Number": "12345678",
                        "Extension": "907643",
                        "Active": true
                    }, {
                        "Subtype": "Home",
                        "CountryCode": "353",
                        "Number": "87654321",
                        "Extension": null,
                        "Active": true
                    }
                ],
                "Email": [{
                        "Subtype": "Personal",
                        "EmailAddress": "my@mail.com",
                        "Active": true
                    }
                ]
            }
        }, {
            "Reference": {
                "Key": "2222",
                "System": "DB2"
            },
            "ContactMethods": {
                "Phone": [{
                        "Subtype": "Home",
                        "CountryCode": "44",
                        "Number": "98761234",
                        "Extension": null,
                        "Active": false
                    }
                ],
                "Email": [{
                        "Subtype": "Work",
                        "EmailAddress": "you@mail.co.uk",
                        "Active": true
                    }
                ]
            }
        }
    ]
}

I am able to group this by PK, but I am having difficulty on the second part, on how to do the grouping within the nested structure. Can you show a sample spec and put some explanation?

Answer 1

Possible but really convoluted / verbose. This is pushing the bounds of what should be done with Jolt.

One pivot and some remapping is maintainable, but this is complicated enough that it will be very hard to debug if something goes wrong / you data is weird.

Requires 5 steps. Two to fix STATUS from a string to a boolean. Two to pivot and sub-pivot the data. And the last one to put everything in the right final place.

I recommend examine each step examine each step in it's own tab/copy of the Jolt demo site to see / grok what each step is doing.

Spec

[
  {
    // ninja in a true and false value so that 
    // Status "Active" / "Inactive" can be "mapped" to booleans
    "operation": "default",
    "spec": {
      "*": {
        "FALSE": false,
        "TRUE": true
      }
    }
  },
  {
    // fix STATUS
    "operation": "shift",
    "spec": {
      "*": {
        // 
        "STATUS": {
          // Match "Active" as make STATUS be true
          "Active": {
            "@(2,TRUE)": "[&3].STATUS"
          },
          // Everything else set to false
          "*": {
            "@(2,FALSE)": "[&3].STATUS"
          }
        },
        // match and discard TRUE and FALSE
        "TRUE|FALSE": null,
        // pass everything else thru
        "*": "[&1].&"
      }
    }
  },
  {
    // now, group by PK value
    "operation": "shift",
    "spec": {
      // top level array
      "*": {
        "PK": {
          "*": { // match any value of PK
            // go back up and grab the whole block and write
            // it to the ouput where the key, is the value of PK
            "@2": "&1[]"
          }
        }
      }
    }
  },
  {
    // sub group by CONTACT_TYPE, with the complication of 
    // pulling one entry off to serve as the "Reference"
    "operation": "shift",
    "spec": {
      "*": { // pk value
        "0": { // special case the Zeroth item so that
          // we can pull off once copy to serve as the 
          // Reference
          "@": "&2.Reference",
          // sub group by CONTACT_TYPE
          "CONTACT_TYPE": {
            "*": {
              "@2": "&4.ContactMethods.&1[]"
            }
          }
        },
        "*": { // all the rest of the array indicies
          // sub group by CONTACT_TYPE
          "CONTACT_TYPE": {
            "*": {
              "@2": "&4.ContactMethods.&1[]"
            }
          }
        }
      }
    }
  },
  {
    // Data fixing and Grouping done, now put everything
    // in its final place
    "operation": "shift",
    "spec": {
      "*": { // top level pk
        "Reference": {
          "PK": "Accounts[#3].Reference.Key",
          "SOURCE_DB": "Accounts[#3].Reference.System"
        },
        "ContactMethods": {
          "Phone": {
            "*": {
              "CONTACT_SUBTYPE": "Accounts[#5].ContactMethods.Phone[&1].Subtype",
              "PHONE_COUNTRY_CODE": "Accounts[#5].ContactMethods.Phone[&1].CountryCode",
              "PHONE_NUMBER": "Accounts[#5].ContactMethods.Phone[&1].Number",
              "PHONE_EXT": "Accounts[#5].ContactMethods.Phone[&1].Extension",
              "STATUS": "Accounts[#5].ContactMethods.Phone[&1].Active"
            }
          },
          "Email": {
            "*": {
              "CONTACT_SUBTYPE": "Accounts[#5].ContactMethods.Email[&1].Subtype",
              "EMAIL": "Accounts[#5].ContactMethods.Email[&1].EmailAddress",
              "STATUS": "Accounts[#5].ContactMethods.Email[&1].Active"
            }
          }
        }
      }
    }
  }
]