Reading date using textractjsonfield

[Anonymous]

Hi All,

 I have created tmongoinput to read the collection from the source MongoDb. The collection has a datearray, Inorder to read this array I have used textractJsonfield component. I am reading by JsonPath ,  jsonfield i have given as DateArray, and in the loop I have give it as "$.[*]" mapping json query i have given it as "testdate1". The sample JSON field is shown below. The output that I should get is the date value : 2016-04-05T23:00:00.000Z, but I am getting it as {"$date":"2016-04-05T23:00:00.000Z"}. Is there way in textractjsonfield that i can remove the $date value and read the value alone. Can you please help me in this.

"DateArray" : [
{
"testdate1" : ISODate("2016-04-05T23:00:00.000Z"),
"testDate2" : ISODate("2016-04-29T23:00:00.000Z"),
}
},
{
"testdate1" : ISODate("2016-05-05T23:00:00.000Z"),
"testdate2" : ISODate("2016-05-30T23:00:00.000Z")
}
]

[Anonymous]

Hello,

---

@nannan wrote:

Hi All,

 I have created tmongoinput to read the collection from the source MongoDb. The collection has a datearray, Inorder to read this array I have used textractJsonfield component. I am reading by JsonPath ,  jsonfield i have given as DateArray, and in the loop I have give it as "$.[*]" mapping json query i have given it as "testdate1". The sample JSON field is shown below. The output that I should get is the date value : 2016-04-05T23:00:00.000Z, but I am getting it as {"$date":"2016-04-05T23:00:00.000Z"}. Is there way in textractjsonfield that i can remove the $date value and read the value alone. Can you please help me in this.

"DateArray" : [
{
"testdate1" : ISODate("2016-04-05T23:00:00.000Z"),
"testDate2" : ISODate("2016-04-29T23:00:00.000Z"),
}
},
{
"testdate1" : ISODate("2016-05-05T23:00:00.000Z"),
"testdate2" : ISODate("2016-05-30T23:00:00.000Z")
}
]

---

Would you mind posting your tExtractJsonfield component setting screenshot on forum which will be helpful for us to address your problem?

Best regards

Sabrina

[Anonymous]

Hi,

@xdshi @nannan did you already find a solution? I have the same problem. This is the part of the JSON (source is MongoDB) and I want to extract amount, date, orderItemId eg.

"cancels" : [
    [ 
        {
            "amount" : 199,
            "date" : ISODate("2018-04-24T11:56:44.045Z"),
            "orderItemId" : "123456789",
            "quantity" : 1,
            "reason" : "no reason",
            "sku" : "ABC-123",
            "user" : "user@email.de"
        }
    ]
]

And this is the tExtractJSONFields component:

 

I get date in this format {"$date":"2018-04-24T11:56:44.045Z"} and it can not be parsed into a date format (Unparseable date: "{"$date":"2018-04-24T11:56:44.045Z"}").

Any help would be appreciated.

[Anonymous]

I found a solution.

I changed "date" to "date.$date" and turned the string to a date type with TalendDate.parseDate("yyyy-MM-dd HH:mm:ss",row2.created_at) using a tMap after that.