question Re: Apache NiFi : How to change JSON data with both Object and Array to Object at once. (Use jolt) in Support Questions

Apache NiFi : How to change JSON data with both Object and Array to Object at once. (Use jolt)

Nicanor — Thu, 04 Apr 2024 02:11:44 GMT

I want to convert json data with both Object and Array into Object at once.

I used jolt, but I didn't reach the desired result.

The json data is as follows.

Input Data:

{
  "response": {
    "header": {
      "resultCode": "00",
      "resultMsg": "NORMAL_SERVICE"
    },
    "body": {
      "dataType": "XML",
      "items": {
        "item": {
          "icaoCode": null,
          "airportName": null,
          "metarMsg": {
            "METAR": {
              "automatedStation": false,
              "id": "metar-RKSS-20240404000000Z",
              "permissibleUsage": "OPERATIONAL",
              "status": "NORMAL",
              "schemaLocation": "link data link data link data link data link data",
              "extension": {
                "msgText": "METAR RKSS 040000Z 09007KT 030V120 9999 SCT035 BKN150 15/05&#13;\n          Q1015 NOSIG="
              },
              "observation": {
                "OM_Observation": {
                  "id": "obs-RKSS-20240404000000Z",
                  "type": {
                    "href": "link data"
                  },
                  "phenomenonTime": {
                    "TimeInstant": {
                      "id": "ti-20240404000000Z",
                      "timePosition": "2024-04-04T00:00:00Z"
                    }
                  },
                  "resultTime": {
                    "href": "#ti-20240404000000Z"
                  },
                  "procedure": {
                    "Process": {
                      "id": "p-49-2-metar",
                      "description": "WMO No. 49 Volume 2 Meteorological Service for International Air Navigation APPENDIX 3 TECHNICAL SPECIFICATIONS RELATED TO METEOROLOGICAL OBSERVATIONS AND REPORTS"
                    }
                  },
                  "observedProperty": {
                    "href": "link data"
                  },
                  "featureOfInterest": {
                    "SF_SpatialSamplingFeature": {
                      "id": "sp-RKSS",
                      "type": {
                        "href": "link data"
                      },
                      "sampledFeature": {
                        "AirportHeliport": {
                          "id": "aerodrome-RKSS",
                          "timeSlice": {
                            "AirportHeliportTimeSlice": {
                              "id": "aerodrome-RKSS-ts",
                              "validTime": null,
                              "interpretation": "BASELINE",
                              "designator": "RKSS",
                              "name": "GIMPO INTERNATIONAL AIRPORT",
                              "locationIndicatorICAO": "RKSS"
                            }
                          }
                        }
                      },
                      "shape": {
                        "Point": {
                          "axisLabels": "Latitude Longitude Altitude",
                          "id": "obs-point-RKSS",
                          "srsName": "link data",
                          "uomLabels": "deg deg m",
                          "pos": "37.33 126.48 18.0"
                        }
                      }
                    }
                  },
                  "result": {
                    "MeteorologicalAerodromeObservationRecord": {
                      "cloudAndVisibilityOK": false,
                      "id": "observation-record-RKSS-20240404000000Z",
                      "airTemperature": {
                        "uom": "Cel",
                        "$": 15
                      },
                      "dewpointTemperature": {
                        "uom": "Cel",
                        "$": 5
                      },
                      "qnh": {
                        "uom": "hPa",
                        "$": 1015
                      },
                      "surfaceWind": {
                        "AerodromeSurfaceWind": {
                          "variableWindDirection": false,
                          "meanWindDirection": {
                            "uom": "deg",
                            "$": 90
                          },
                          "meanWindSpeed": {
                            "uom": "[kn_i]",
                            "$": "007"
                          },
                          "extremeClockwiseWindDirection": {
                            "uom": "deg",
                            "$": 120
                          },
                          "extremeCounterClockwiseWindDirection": {
                            "uom": "deg",
                            "$": "030"
                          }
                        }
                      },
                      "visibility": {
                        "AerodromeHorizontalVisibility": {
                          "prevailingVisibility": {
                            "uom": "m",
                            "$": 10000
                          }
                        }
                      },
                      "cloud": {
                        "AerodromeObservedClouds": {
                          "layer": [
                            {
                              "CloudLayer": {
                                "amount": {
                                  "href": "link data"
                                },
                                "base": {
                                  "uom": "[ft_i]",
                                  "$": 3500
                                },
                                "cloudType": {
                                  "href": "link data",
                                  "title": "Stratocumulus"
                                }
                              }
                            },
                            {
                              "CloudLayer": {
                                "amount": {
                                  "href": "link data"
                                },
                                "base": {
                                  "uom": "[ft_i]",
                                  "$": 15000
                                },
                                "cloudType": {
                                  "href": "link data",
                                  "title": "Altocumulus"
                                }
                              }
                            }
                          ]
                        }
                      }
                    }
                  }
                }
              },
              "trendForecast": {
                "OM_Observation": {
                  "id": "trend-fcst-1",
                  "type": {
                    "href": "link data"
                  },
                  "phenomenonTime": {
                    "href": "#ti-20240404000000Z"
                  },
                  "resultTime": {
                    "href": "#ti-20240404000000Z"
                  },
                  "procedure": {
                    "href": "#p-49-2-metar"
                  },
                  "observedProperty": {
                    "href": "link data"
                  },
                  "featureOfInterest": {
                    "href": "#sp-RKSS"
                  },
                  "result": {
                    "MeteorologicalAerodromeTrendForecastRecord": {
                      "changeIndicator": "NO_SIGNIFICANT_CHANGES",
                      "cloudAndVisibilityOK": false,
                      "id": "trend-fcst-record-1-20240404000000Z"
                    }
                  }
                }
              }
            }
          }
        }
      },
      "numOfRows": 10000,
      "pageNo": 1,
      "totalCount": 1
    }
  }
}

Jolt Spec:

[
  {
    "operation": "shift",
    "spec": {
      "response": {
        "body": {
          "items": {
            "item": {
              "*": {
                "*": {
                  "extension": {
                    "msgText": "msgText"
                  },
                  "observation": {
                    "OM_Observation": {
                      "phenomenonTime": {
                        "TimeInstant": {
                          "timePosition": "phenomenonTime"
                        }
                      },
                      "featureOfInterest": {
                        "SF_SpatialSamplingFeature": {
                          "sampledFeature": {
                            "AirportHeliport": {
                              "timeSlice": {
                                "AirportHeliportTimeSlice": {
                                  "name": "featureOfInterest"
                                }
                              }
                            }
                          },
                          "shape": {
                            "Point": {
                              "pos": "shape"
                            }
                          }
                        }
                      },
                      "result": {
                        "MeteorologicalAerodromeObservationRecord": {
                          "airTemperature": {
                            "\\$": "airTemperature"
                          },
                          "dewpointTemperature": {
                            "\\$": "dewpointTemperature"
                          },
                          "qnh": {
                            "\\$": "qnh"
                          },
                          "surfaceWind": {
                            "AerodromeSurfaceWind": {
                              "meanWindDirection": {
                                "\\$": "meanWindDirection"
                              },
                              "meanWindSpeed": {
                                "\\$": "meanWindSpeed"
                              },
                              "windGustSpeed": {
                                "\\$": "windGustSpeed"
                              },
                              "extremeClockwiseWindDirection": {
                                "\\$": "extremeClockwiseWindDirection"
                              },
                              "extremeCounterClockwiseWindDirection": {
                                "\\$": "extremeCounterClockwiseWindDirection"
                              }
                            }
                          },
                          "visibility": {
                            "AerodromeHorizontalVisibility": {
                              "prevailingVisibility": {
                                "\\$": "AerodromeHorizontalVisibility"
                              }
                            }
                          },
                          "presentWeather": {
                            "href": "presentWeather"
                          },
                          "cloud": {
                            "AerodromeObservedClouds": {
                              "layer": {
                                "*": {
                                  "CloudLayer": {
                                    "amount": {
                                      "href": "cloud_amount"
                                    },
                                    "base": {
                                      "\\$": "cloud_base"
                                    },
                                    "cloudType": {
                                      "href": "cloudType_href",
                                      "title": "cloudType"
                                    }
                                  }
                                }
                              }
                            }
                          }
                        }
                      }
                    }
                  }
                }
              }
            }
          }
        }
      }
    }
    },
  {
    "operation": "shift",
    "spec": {
      "msgText": "&",
      "phenomenonTime": "&",
      "featureOfInterest": "&",
      "shape": "&",
      "airTemperature": "&",
      "dewpointTemperature": "&",
      "qnh": "&",
      "meanWindDirection": "&",
      "meanWindSpeed": "&",
      "windGustSpeed": "&",
      "extremeClockwiseWindDirection": "&",
      "extremeCounterClockwiseWindDirection": "&",
      "AerodromeHorizontalVisibility": "&",
      "cloud_amount": "&",
      "cloud_base": "&",
      "cloudType_href": "&",
      "cloudType": "&",
      "presentWeather": "&"
    }
   },
  {
    "operation": "modify-overwrite-beta",
    "spec": {
      "airTemperature": "=toString",
      "dewpointTemperature": "=toString",
      "qnh": "=toString",
      "meanWindDirection": "=toString",
      "meanWindSpeed": "=toString",
      "extremeClockwiseWindDirection": "=toString",
      "extremeCounterClockwiseWindDirection": "=toString",
      "AerodromeHorizontalVisibility": "=toString",
      "cloud_amount": "=toString",
      "cloud_base": "=toString",
      "cloudType_href": "=toString",
      "cloudType": "=toString"
    }
  }



  ]

OutPut Data:

{
  "msgText" : "METAR RKSS 040000Z 09007KT 030V120 9999 SCT035 BKN150 15/05&#13;\n          Q1015 NOSIG=",
  "phenomenonTime" : "2024-04-04T00:00:00Z",
  "featureOfInterest" : "GIMPO INTERNATIONAL AIRPORT",
  "shape" : "37.33 126.48 18.0",
  "airTemperature" : "15",
  "dewpointTemperature" : "5",
  "qnh" : "1015",
  "meanWindDirection" : "90",
  "meanWindSpeed" : "007",
  "extremeClockwiseWindDirection" : "120",
  "extremeCounterClockwiseWindDirection" : "030",
  "AerodromeHorizontalVisibility" : "10000",
  "cloud_amount" : [ "link data", "link data" ],
  "cloud_base" : [ "3500", "15000" ],
  "cloudType_href" : [ "link data", "link data" ],
  "cloudType" : [ "Stratocumulus", "Altocumulus" ]
}

Expected data:

{
  "msgText" : "METAR RKSS 040000Z 09007KT 030V120 9999 SCT035 BKN150 15/05&#13;\n          Q1015 NOSIG=",
  "phenomenonTime" : "2024-04-04T00:00:00Z",
  "featureOfInterest" : "GIMPO INTERNATIONAL AIRPORT",
  "shape" : "37.33 126.48 18.0",
  "airTemperature" : "15",
  "dewpointTemperature" : "5",
  "qnh" : "1015",
  "meanWindDirection" : "90",
  "meanWindSpeed" : "007",
  "extremeClockwiseWindDirection" : "120",
  "extremeCounterClockwiseWindDirection" : "030",
  "AerodromeHorizontalVisibility" : "10000",
  "cloud_amount0" : "link data",
  "cloud_amount1" : "link data"
  "cloud_base0" : "3500",
  "cloud_base1" : "15000",
  "cloudType_href0" : "link data",
  "cloudType_href1" : "link data",
  "cloudType0" : "Stratocumulus",
  "cloudType1" : "Altocumulus"
}

As above, could you please advise a jolt script in which the data I expect is output?

Re: Apache NiFi : How to change JSON data with both Object and Array to Object at once. (Use jolt)

VidyaSargur — Thu, 04 Apr 2024 08:35:10 GMT

Hi @Nicanor, Welcome to our community! To help you get the best possible answer, I have tagged in our NiFi experts @MattWho @SAMSAL who may be able to assist you further.

Please feel free to provide any additional information or details about your query, and we hope that you will find a satisfactory solution to your question.

Re: Apache NiFi : How to change JSON data with both Object and Array to Object at once. (Use jolt)

saquibsk — Thu, 04 Apr 2024 10:04:10 GMT

Hi @Nicanor,

you can bring another JoltTransformJSON processor and write down the below JOLT spec to extract the element of list.

JOLT Spec

[ { "operation": "shift", "spec": { "msgText": "&", "phenomenonTime": "phenomenonTime", "cloud_base": "cloud_base_List", "cloud_amount": "cloud_amount_List" } }, // Extracting Values from the List { "operation": "modify-overwrite-beta", "spec": { // Extract the last element from each list "cloud_base1": "=lastElement(@(1,cloud_base_List))", "cloud_base2": "=firstElement(@(1,cloud_base_List))" } } ]

JOLT Output

{ "msgText" : "METAR RKSS 040000Z 09007KT 030V120 9999 SCT035 BKN150 15/05\n Q1015 NOSIG=", "phenomenonTime" : "2024-04-04T00:00:00Z", "cloud_base_List" : [ 3500, 15000 ], "cloud_amount_List" : [ "link data", "link data" ], "cloud_base1" : 15000, "cloud_base2" : 3500 }

One more suggestion, please remove the unrequired line of code. you can typecast the data type by using UpdateRecord processor. Because JOLT transformation may consume large amounts of memory.

[ { "operation": "shift", "spec": { "response": { "body": { "items": { "item": { "*": { "*": { "extension": { "msgText": "msgText" }, "observation": { "OM_Observation": { "phenomenonTime": { "TimeInstant": { "timePosition": "phenomenonTime" } }, "featureOfInterest": { "SF_SpatialSamplingFeature": { "sampledFeature": { "AirportHeliport": { "timeSlice": { "AirportHeliportTimeSlice": { "name": "featureOfInterest" } } } }, "shape": { "Point": { "pos": "shape" } } } }, "result": { "MeteorologicalAerodromeObservationRecord": { "airTemperature": { "\\$": "airTemperature" }, "dewpointTemperature": { "\\$": "dewpointTemperature" }, "qnh": { "\\$": "qnh" }, "surfaceWind": { "AerodromeSurfaceWind": { "meanWindDirection": { "\\$": "meanWindDirection" }, "meanWindSpeed": { "\\$": "meanWindSpeed" }, "windGustSpeed": { "\\$": "windGustSpeed" }, "extremeClockwiseWindDirection": { "\\$": "extremeClockwiseWindDirection" }, "extremeCounterClockwiseWindDirection": { "\\$": "extremeCounterClockwiseWindDirection" } } }, "visibility": { "AerodromeHorizontalVisibility": { "prevailingVisibility": { "\\$": "AerodromeHorizontalVisibility" } } }, "presentWeather": { "href": "presentWeather" }, "cloud": { "AerodromeObservedClouds": { "layer": { "*": { "CloudLayer": { "amount": { "href": "cloud_amount" }, "base": { "\\$": "cloud_base" }, "cloudType": { "href": "cloudType_href", "title": "cloudType" } } } } } } } } } } } } } } } } } } ]

"Accept as Solution" if its work for you.

Re: Apache NiFi : How to change JSON data with both Object and Array to Object at once. (Use jolt)

Nicanor — Fri, 05 Apr 2024 04:15:52 GMT

Thank you for your solution.
It was a lot of inspiration for me.

Therefore, I would like to ask you another question.

Expected data:

{
  "cloud_amount0" : "link data0",
  "cloud_amount1" : "link data1",
  "cloud_amount2" : "link data2",
  "cloud_amount3" : "link data3",
}

Is there a jolt whose key name is dynamically increasing like 'cloud_mount'?

Re: Apache NiFi : How to change JSON data with both Object and Array to Object at once. (Use jolt)

saquibsk — Fri, 05 Apr 2024 18:24:05 GMT

@Nicanor ,

Could you please provide the JSON input also?

Re: Apache NiFi : How to change JSON data with both Object and Array to Object at once. (Use jolt)

saquibsk — Fri, 05 Apr 2024 19:27:13 GMT

Hi @Nicanor ,

Let's take a below example:

Json Input:

[ { "cloud_base": [ "3500", "15000" ], "cloudType": [ "Stratocumulus", "Altocumulus" ] } ]

Jolt Spec:

[ { "operation": "shift", "spec": { "*": { "cloud_base": { "*": { "*": { "@1": "cloud_base_&" } } }, "cloudType": { "*": { "*": { "@1": "cloudType_&" } } } } } } ]

Output:

{ "cloud_base_3500" : "3500", "cloud_base_15000" : "15000", "cloudType_Stratocumulus" : "Stratocumulus", "cloudType_Altocumulus" : "Altocumulus" }

"Accept as Solution" if its work for you.