Support Questions

Find answers, ask questions, and share your expertise

JoltTransformJson Spec

Rising Star

Hi ,

I need to flatten the following input,

1 ACCEPTED SOLUTION

Super 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

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

Rising Star

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

Super 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

Rising Star

Excellent help. Thanks alot.

Take a Tour of the Community
Don't have an account?
Your experience may be limited. Sign in to explore more.