Support Questions

Find answers, ask questions, and share your expertise

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

avatar
Explorer

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"
            }
        ]
    }
}

 

1 ACCEPTED SOLUTION

avatar

@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.

cotopaul_0-1698915673418.png

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

View solution in original post

4 REPLIES 4

avatar
Community Manager

@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.


Regards,

Diana Torres,
Community Moderator


Was your question answered? Make sure to mark the answer as the accepted solution.
If you find a reply useful, say thanks by clicking on the thumbs up button.
Learn more about the Cloudera Community:

avatar

@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.

cotopaul_0-1698915673418.png

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

avatar
Explorer

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

avatar

@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.