Support Questions

Find answers, ask questions, and share your expertise
Announcements
Celebrating as our community reaches 100,000 members! Thank you!

JoltTransformJson Spec

avatar
Expert Contributor

Hi ,

I need to flatten the following input,

1 ACCEPTED SOLUTION

avatar
Master Guru

@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-&"
            }
          }
        }
      }
    }
  }
}

View solution in original post

4 REPLIES 4

avatar
Master Guru

@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-&"
            }
          }
        }
      }
    }
  }
}

avatar
Expert Contributor

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

avatar
Master Guru

@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

avatar
Expert Contributor

Excellent help. Thanks alot.