Search code examples
jsonapache-nifijolt

How to get column value of key in JOLT

I'm looking for breaking following nested JSON file and transform it into a SQL prepared format.

Input JSON file:

{
  "Product1": {
    "Purchase": 31
  },
  "Product2": {
    "Purchase": 6213,
    "Cancel": 1988,
    "Change": 3702,
    "Renewal": 5934
  }
}

Desired output:

[
  {
    "product": "Product1",
    "Purchase": 31
  },
  {
    "product": "Product2",
    "Purchase": 6213,
    "Cancel": 1988,
    "Change": 3702,
    "Renewal": 5934
  }
]
Solution

  • What you need is using a $ wildcard within a shift transformation spec in order to replicate the current attributes's key such as

    [
  {
    "operation": "shift",
    "spec": {
      "*": {
        "$": "[#2].product",// $ grabs the value after going tree one level up from the current level
        "*": "[#2].&"// keeps the current attributes conforming to the objects nested within a common array 
      }
    }
  }
]