Search code examples
jsontransformapache-nifijolt

JoltTransformJson - How to get column names as values

i have JSON value as below :

{
  "table": "table_name",
  "op_type": "U",
  "before": {
    "AAAA": "1-1111",
    "BBBB": "2022-08-31 03:57:01"
  },
  "after": {
    "AAAA": "1-1111",
    "BBBB": "2022-08-31 10:10:34",
    "DDDD": "2023-08-31 23:59:59"
  }
}

I want to add column_names field like this :

,"changed_columns": "AAAA,BBBB,DDDD"

is there a way to do this?

Solution

  • You can use the following specs in which the main idea is to arrange the attributes so as to generate an array with unique elements within the an array by using successive shift transformation, then combine them within a modify transformation such as

    [
  {
    // combine common key names for each respective values for the attributes
    "operation": "shift",
    "spec": {
      "before|after": {
        "*": {
          "$": "&"
        }
      }
    }
  },
  {
    // construct an array from those newly formed keys
    "operation": "shift",
    "spec": {
      "*": {
        "$": "changed_columns"
      }
    }
  },
  {
    // make them comma-separated 
    "operation": "modify-overwrite-beta",
    "spec": {
      "*": "=join(',',@(1,&))"
    }
  }
]

    the demo on the site http://jolt-demo.appspot.com/ is

    enter image description here

    Edit : If your aim is to keep newly generated attribute along with the existing ones, then you can prefer using the following spec

    [
  {
    "operation": "shift",
    "spec": {
      "*": "&", //else case
      "before|after": {
        "*": {
          "$": "cc.&",
          "@": "&2.&"
        }
      }
    }
  },
  {
    "operation": "shift",
    "spec": {
      "cc": {
        "*": {
          "$": "changed_columns"
        }
      },
      "*": "&" //else case
    }
  },
  {
    "operation": "modify-overwrite-beta",
    "spec": {
      "changed_columns": "=join(',',@(1,&))"
    }
  }
]