JOLT transformation for Nifi, flattening array and moving keys

Question

I'm trying to flatten a JSON output while moving part of it into a lower array. In the sample below I need to keep everything under "values" and also move "timestamp" into the values results. Any tips or documentation that can help?

Thank you!

Input:

{   "readings": [
    {
      "meter": "78522546",
      "records": [
        {
          "timestamp": "22/02/2019 17:10:00",
          "values": {
            "DateTime": 1550848206,
            "ExportWh": 2136.5,
          }
        },
        {
          "timestamp": "22/02/2019 17:15:00",
          "values": {
            "DateTime": 1550848206,
            "ExportWh": 2136.5,
          }
        }
      ]
    },
    {
      "meter": "78522548",
      "records": [
        {
          "timestamp": "22/02/2019 17:15:00",
          "values": {
            "DateTime": 1550848246,
            "ExportWh": 0,
          }
        }
      ]
    },
    {
      "meter": "78522570",
      "records": [
        {
          "timestamp": "22/02/2019 17:10:00",
          "values": {
            "DateTime": 1550848226,
            "ExportWh": 3152012603293.037,
          }
        }
      ]
    },
    {
      "meter": "78522572",
      "records": [
        {
          "timestamp": "22/02/2019 17:10:00",
          "values": {
            "DateTime": 1550848236,
            "ExportWh": 1112.172,
          }
        }
      ]
    },
    {
      "meter": "78522589",
      "records": [
        {
          "timestamp": "22/02/2019 17:10:00",
          "values": {
            "DateTime": 1550848217,
            "ExportWh": 0,
          }
        }
      ]
    }   ] }

Desired output:

{
    "timestamp": "22/02/2019 17:10:00",
    "DateTime": 1550848206,
    "ExportWh": 2136.5
},{
    "timestamp": "22/02/2019 17:15:00",
    "DateTime": 1550848206,
    "ExportWh": 2136.5
},{
    "timestamp": "22/02/2019 17:15:00",
    "DateTime": 1550848246,
    "ExportWh": 0
},{
    "timestamp": "22/02/2019 17:10:00",
    "DateTime": 1550848226,
    "ExportWh": 3152012603293.037
},{
    "timestamp": "22/02/2019 17:10:00",
    "DateTime": 1550848236,
    "ExportWh": 1112.172
},{
    "timestamp": "22/02/2019 17:10:00",
    "DateTime": 1550848217,
    "ExportWh": 0
}

This is how far I got with the spec but from here kept on going round in circles:

[
  {
    "operation": "shift",
    "spec": {
      "readings": {
        "*": {
          "records": {
            "*": {
              "timestamp": "&",
              "@(0,values)": ""
            }
          }
        }
      }
    }
  }
]

Jagadesh Jagadesh · Accepted Answer · 2020-09-17T17:18:15

Check this spec

[
  {
    "operation": "shift",
    "spec": {
      "readings": {
        "*": {
          "records": {
            "*": {
              "values": {
                "@": "[].@(2,timestamp)"
              }
            }
          }
        }
      }
    }
  }, {
    "operation": "shift",
    "spec": {
      "*": {
        "*": {
          "@": "[]",
          "$": "[&2].timestamp"
        }
      }
    }
  }
]

JOLT transformation for Nifi, flattening array and moving keys

1 Answers