Skip to main content
user001
user001
Silver ⭐️
November 1, 2023
Solved

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

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

 

cotopaul
cotopaulAnswer
Platinum ⭐️
November 2, 2023

@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 in thread

4 replies

DianaTorres
Community Manager
DianaTorres
Community Manager
November 2, 2023

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

Translate
Regards, Diana Torres, Senior Community Moderator
cotopaul
cotopaulAnswer
Platinum ⭐️
November 2, 2023

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

Translate
    user001
    user001Author
    Silver ⭐️
    November 3, 2023

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

    Translate
    cotopaul
    cotopaul
    Platinum ⭐️
    November 6, 2023

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

    Translate
    Terms & ConditionsAccessibility statement