Apache NiFi - JOLT Expression: Flatten Hierarchical Data & Distribute Value from JSON into Array of Objects

Question

Hi Folks,This is likely a multipart problem, but I've been trying to get the following sample JSON to distribute the "id" value from the root of the JSON object into a flattened array of objects with the "id" value and the "code" value from each category (or subcategory in the nested structure).  For example, given the following JSON payload: {  "id": "1234",  "categories": [    {      "code": "A",      "name": "Alpha",      "subcategories": [        {          "code": "AA",          "name": "Alpha Alpha"        },        {          "code": "AB",          "name": "Alpha Bravo"        }      ]    },    {      "code": "B",      "name": "Bravo"    },    {      "code": "C",      "name": "Charlie"    },    {      "code": "D",      "name": "Delta"    }  ]} The Desired Output would be:  [  {    "individualId": "1234",    "categoryCode": "A",    "categoryName": "Alpha"  },  {    "individualId": "1234",    "categoryCode": "AA",    "categoryName": "Alpha Alpha"  },  {    "individualId": "1234",    "categoryCode": "AB",    "categoryName": "Alpha Bravo"  },  {    "individualId": "1234",    "categoryCode": "B",    "categoryName": "Bravo"  },  {    "individualId": "1234",    "categoryCode": "C",    "categoryName": "Charlie"  },  {    "individualId": "1234",    "categoryCode": "D",    "categoryName": "Delta"  }] I tried the following JOLT Transformation Expression:  [  {    "operation": "shift",    "spec": {      "id": "id",      "categories": {        "*": {          "code": "categories[&1].categoryCode",          "name": "categories[&1].categoryName",          "@(2,id)": "categories[&1].individualId",          "subcategories": {            "*": {              "code": "categories[&3].categoryCode",              "name": "categories[&3].categoryName",              "@(4,id)": "categories[&3].individualId"            }          }        }      }    }  },  {    "operation": "shift",    "spec": {      "categories": {        "*": "[]"      }    }  }] But got this, not sure why the subcategories keep getting grouped into the first element,. Any insight or help would be much appreciated:  [ {  "individualId" : [ "1234", "1234", "1234" ],  "categoryCode" : [ "A", "AA", "AB" ],  "categoryName" : [ "Alpha", "Alpha Alpha", "Alpha Bravo" ]}, {  "individualId" : "1234",  "categoryCode" : "B",  "categoryName" : "Bravo"}, {  "individualId" : "1234",  "categoryCode" : "C",  "categoryName" : "Charlie"}, {  "individualId" : "1234",  "categoryCode" : "D",  "categoryName" : "Delta"} ]

drewski7 · Accepted Answer

@MDTechie - Here is a provided solution for the question you asked. I usually like to Jolt Transforms step by step. Please see the Jolt Spec below along with explanations for each step.

[
  {
    "operation": "shift",
    "spec": {
      "id": "id",
      "categories": {
        "*": {
          "@": "categories[]",
          "subcategories": {
            "*": {
              "@": "categories[]"
            }
          }
        }
      }
    }
  },
  {
    "operation": "remove",
    "spec": {
      "categories": {
        "*": {
          "subcategories": ""
        }
      }
    }
  },
  {
    "operation": "shift",
    "spec": {
      "categories": {
        "*": {
          "code": "categories[&1].categoryCode",
          "name": "categories[&1].categoryName",
          "@(2,id)": "categories[&1].individualId"
        }
      }
    }
  },
  {
    "operation": "shift",
    "spec": {
      "categories": {
        "*": ""
      }
    }
  }
]

The first operation

Copies the id field from the original input to the top level of the output.
Iterates over the categories array and:
- Copies each category object to a new array called categories[].
- If a category contains a subcategories array, each subcategory object is also copied into the same categories[] array, effectively flattening the nested structure.

The second operation

Searches within each category object in the categories[] array.
Removes the subcategories field if it exists.

The third operation

Iterates over the categories[] array, processing each category object.
Renames the code field to categoryCode and the name field to categoryName for consistency with the new output schema.
Adds a new field called individualId inside each category object, using the id value from two levels up (the root of the original input).

The fourth operation:

Takes each object from the categories[] array and moves it to the root level.
As a result, the categories array wrapper is removed, leaving a flat array of individual objects.

I suggest piecing it out in a JOLT tester to understand it a little better. Hope this helps! 🙂

If you found the solution helpful, please "Accept as Solution"

DianaTorres · Answer

@MDTechie Welcome to the Cloudera Community!

To help you get the best possible solution, I have tagged our NiFi experts @MattWho @SAMSAL who may be able to assist you further.

Please keep us updated on your post, and we hope you find a satisfactory solution to your query.

Sign up

Please use your Cloudera account.

Login to the community

Please use your Cloudera account.