question how to convert a date from below JSON file from mm/yy to yyyy-mm format using NIFI in Support Questions

how to convert a date from below JSON file from mm/yy to yyyy-mm format using NIFI

user001 — Wed, 01 Nov 2023 23:33:06 GMT

Need help to convert a date from below JSON file from mm/yy to yyyy-mm format using NIFI

{

"book_id": "3791"

"Key": {

"value": [

{

"Number": "553",

"Date": "05/20"

{

"Number": "5537290000000522",

"Date": "03/22"

}

]

}

Re: how to convert a date from below JSON file from mm/yy to yyyy-mm format using NIFI

DianaTorres — Thu, 02 Nov 2023 00:53:31 GMT

@user001 Welcome to the Cloudera Community!

To help you get the best possible solution, I have tagged our NiFi experts @MattWho @cotopaul who may be able to assist you further.

Please keep us updated on your post, and we hope you find a satisfactory solution to your query.

Re: how to convert a date from below JSON file from mm/yy to yyyy-mm format using NIFI

cotopaul — Thu, 02 Nov 2023 09:01:53 GMT

@user001,

How I would do this:
- Create an UpdateRecord Processor, where I define a JsonTreeReader, for reading the input file and a JsonRecordSetWriter, for writing the newly formatted file.
- Within this same processor, I would add a new property where I define the path to the column you are trying to modify. In your case, assuming that this is the entire JSON File, you would have /Key/value[*]/Date.
- The value for this newly generated property should be the transformation you are trying to achieve. In your case, it would be ${field.value:toDate("MM/yy"):format("yyyy-MM")}
- Next, within the same exact processor, I would modify the Replacement Value Strategy from the default value into Literal Value.

And that's pretty much all you have to do.

Re: how to convert a date from below JSON file from mm/yy to yyyy-mm format using NIFI

user001 — Fri, 03 Nov 2023 18:56:59 GMT

@cotopaul

Hello , Thankyou for your reply.
In my local NIFI when I created the updateRecord processor, it suppported- UpdateRecord 1.21.0
with which I was able to transform successfully.
But In the Sandbox environment When I created the updateRecord processor, it supported- UpdateRecord 1.18.0.2.1.5.1005-1
with which I am getting below error.
UpdateRecord[id=bbde4766-0009-18f4-0000-00000eaf934b] Failed to process FlowFile[filename=32534d2a-ebfb-4e98-84a8-fdf5c865989f]; will route to failure: java.lang.IllegalStateException: Null String illegal for SerializedString

I have verified the Input Json in both local as well as the Sandbox and are same
But getting the above error in Sandbox Env.

Could you please help me on this?

Re: how to convert a date from below JSON file from mm/yy to yyyy-mm format using NIFI

cotopaul — Mon, 06 Nov 2023 08:28:44 GMT

@user001,

I do not know what your Sandbox environment is, but I just tested with NiFi 1.15 and the solution works like a charm. So either you are doing something wrong, or you have something configured incorrectly in your sandbox environment. Or maybe NiFI 1.18 is bugged - but I highly doubt it, as there would have been far more posts reporting similar issues.