JoltTransformJson Spec

[#1]

Hi ,

I need to flatten the following input,

[#2 · Accepted solution]

@Sanaz Janbakhsh

Can you once try the below Spec

 [{ "operation": "shift",
  "spec": {
    "payloadMetaData": {
      "rawPayload": "rawPayload",
      "fcount": "fcount",
      "fport": "fport",
      "applicationMetaData": {
        "id": {
          "entityType": "entityType",
          "id": "id"
        },
        "customerId": {
          "entityType": "entityTypeCustomer",
          "id": "idCustomer"
        }
      },
      "gatewayMetaDataList": {
        "*": {  //* means if you are having more than 1 message in array then it results array of mac addresses in it.
          "mac": "mac"
        }
      },
      "deviceMetaData": {
        "id": {
          "entityType": "entityTypeDevice",
          "id": "DeviceId"
        },
        "name": "DeviceName",
        "deviceClass": "deviceClass",
        "deviceEUI": "deviceEUI",
        "appEUI": "appEUI"
      }
    },
    "payload": "payload"
  }
}]

input:-

{
  "payloadMetaData": {
    "applicationMetaData": {
      "id": {
        "entityType": "APPLICATION",
        "id": "7d11abf0-aa82-11e7-afd4-63e5a28ad7fa"
      },
      "customerId": {
        "entityType": "CUSTOMER",
        "id": "a84ab1a0-aa81-11e7-afd4-63e5a28ad7fa"
      },
      "subCustomerId": null,
      "name": "TekTelic-Industrial"
    },
    "gatewayMetaDataList": [
      {
        "id": {
          "entityType": "GATEWAY",
          "id": "e5ca6840-aa81-11e7-afd4-63e5a28ad7fa"
        },
        "name": "ColinsGateway",
        "mac": "647FDAFFFE00417C",
        "latitude": 51.04697912502887,
        "longitude": -114.06121730804443,
        "altitude": null,
        "rxInfo": {
          "channel": 2,
          "codeRate": "4/5",
          "crcStatus": 1,
          "dataRate": {
            "modulation": "LORA",
            "spreadFactor": 7,
            "bandwidth": 125
          },
          "frequency": 902700000,
          "loRaSNR": 6.8,
          "mac": "647fdafffe00417c",
          "rfChain": 0,
          "rssi": -51,
          "size": 21,
          "time": "2017-11-01T21:51:27Z",
          "timestamp": 1400148651,
          "rsig": null
        }
      }
    ],
    "deviceMetaData": {
      "id": {
        "entityType": "DEVICE",
        "id": "61348ab0-ada2-11e7-ac06-63e5a28ad7fa"
      },
      "name": "TekTelic-Industrial-0017",
      "deviceClass": "CLASS_A",
      "deviceEUI": "647FDA0000000155",
      "appEUI": "647FDA8000000155"
    },
    "fcount": 3706,
    "fport": 10
  }
}

Output:-

{
  "fcount" : 3706,
  "fport" : 10,
  "entityType" : "APPLICATION",
  "id" : "7d11abf0-aa82-11e7-afd4-63e5a28ad7fa",
  "entityTypeCustomer" : "CUSTOMER",
  "idCustomer" : "a84ab1a0-aa81-11e7-afd4-63e5a28ad7fa",
  "mac" : "647FDAFFFE00417C",
  "entityTypeDevice" : "DEVICE",
  "DeviceId" : "61348ab0-ada2-11e7-ac06-63e5a28ad7fa",
  "DeviceName" : "TekTelic-Industrial-0017",
  "deviceClass" : "CLASS_A",
  "deviceEUI" : "647FDA0000000155",
  "appEUI" : "647FDA8000000155"
}

in addition if you want to flatten out all the elements of array then extract all the json message contents by using eval json path processor with Destination as flowfile-attribute, then use split json processor with

JsonPath Expression as $.gatewayMetaDataList 

then use jolt transform with below spec

[{
  "operation": "shift",
  "spec": {
    "*": {
      "*": "id-&",
      "id": {
        "*": "&"
      },
      "rxInfo": {
        "*": "rxInfo-&",
        "dataRate": {
          "*": "dataRate-&"
        }
      }
    }
  }
}]

Right now you are doing flattening out json array elements then use attributes to json processor

and keep all the attributes that you have extracted, now this processor will create new json message which is flattened out with each array elements in it.
(or)
if you want to add all array elements to respective keys then use below spec

 [{
  "operation": "shift",
  "spec": {
    "payloadMetaData": {
      "gatewayMetaDataList": {
        "*": {
          "*": "id-&",
          "id": {
            "*": "&"
          },
          "rxInfo": {
            "*": "rxInfo-&",
            "dataRate": {
              "*": "dataRate-&"
            }
          }
        }
      }
    }
  }
}

[#3]

Hi Shu,

Thanks for your advise. I think i missed one of the important step in your instruction.

You are right, i want to flatten all the elements including elements of array.

I decided to take two different options and for both i was not successful :

1- I tried to flattened all the attribute names which are not pert of array and so i used the attached Jolt Spec1.txt but then the result doesn't include gatewayMetaDataList and so i use EvaluateJsonPath for the array in the next step

2- I used "evaluateJsonPath" with destination of flowfile-attribute and then split json processor with JsonPathExpressionas $.gatewayMetaDataList" but i get the attached error. untitled.png

Could you please advise which step i did wrong?

Thanks, SJ

[#4]

@Sanaz Janbakhsh,
Use the below spec you can get gatewayMetaDataList array as is.

[{
  "operation": "shift",
  "spec": {
    "payloadMetaData": {
      "*": "&",
      "applicationMetaData": {
        "id": {
          "entityType": "entityType",
          "id": "ApplicationId"
        },
        "customerId": {
          "entityType": "entityTypeCustomer",
          "id": "CustomerId"
        },
        "subCustomerId": "subCustomerId",
        "name": "SubCustomerName"
      },
      "deviceMetaData": {
        "id": {
          "entityType": "entityTypeDevice",
          "id": "DeviceId"
        },
        "name": "DeviceName",
        "deviceClass": "deviceClass",
        "deviceEUI": "deviceEUI",
        "appEUI": "appEUI"
      },
      "rawPayload": "rawPayload",
      "fcount": "fcount",
      "fport": "fport"
    },
    "payload": "payload"
  }
}]

Flow:-

1.JoltTransform //flatten all the message except gatewayMetaDataList array
2.EvaluateJsonPath //to extract all the content and keep it as attributes to the flowfile and change Destination Property to Flowfile-Attribute.
3.SplitJson //split the json on gatewayMetaDataList array 
4.JoltTransform //flatten gatewayMetaDataList array 
5.EvaluateJsonPath //to extract all the content and keep it as attributes to the flowfile and change Destination Property to Flowfile-Attribute.
6.AttributesToJson //keep all the attribute names and change Destination Property to Flowfile-Content.

You can use the below xml for reference and change the eval json path as per your requirements.

jolt-11-13.xml

[#5]

Excellent help. Thanks alot.