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

[#1]

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
\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
\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
\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?

[#2]

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.

[#3]

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.

[#4]

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'?

[#5]

@Nicanor ,

Could you please provide the JSON input also?

[#6 · Accepted solution]

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.