Extracting json date value from mongoDB using tExractJSONfields component

[TamilM]

Hi All,

I am trying to extract some attributes out of mongoDB json but im getting wrong values out of it,

My date_of_birth values are extracted like this "{"$date":602316000000}" , any help on this will be highly appreciated ?!

please find my json below 

{
"_id": "cb865234b9c0-332-ae9e-23423",
"purchase_type": "PRE_QUAL",
"lead_id": ,
"loan_number": "11111",
"city": "Fort ",
"state": "TX",
"zip_code": "5465",
"property_type": "Single Family Residence",
"property_type_a": "Detached",
"property_type_b": "1 Unit",
"property_use": "PrimaryResidence",
"working_with_realtor": false,
"down_payment_amount": 250000,
"veteran_details": {
"borrower_position": "",
"veteran_branch_of_service": ""
},
"estimated_purchase_price": 575000,
"borrowers": [{
"customer_id": NumberLong(12345678),
"borrower_position": 1,
"first_name": "Russell ",
"last_name": "Smith",
"middle_name": "W",
"address": "5409 El Campo",
"city": "Fort Worth ",
"state": "TX",
"zip_code": "76107",
"marital_status": "SEPARATED",
"last4SSN": "XXXX",
"date_of_birth": ISODate("1959-01-17T00:00:00.000Z"),
"email": "XXX@gmail.com",
"phone": "XXXXX",
"va_status": false,
"veteran_type_of_service": "NO",
"employment_status": ["employed"],
"self_employed": false,
"bankruptcy": false,
"foreclosure": false,
"us_citizen": true,
"primary_residence_intent": true,
"income": 12000,
"income_frequency": "Monthly",
"income_breakdown": ["base"],
"income_details": {
"base": 12000
}
}],
"brand_name": "UWM",
"shard_key": "cb",
"created_dttm": "2020-02-10T15:52:37.036+00:00",
"additionalProperties": {

}
}

[Anonymous]

It doesn't look like you have shown us the same JSON. The JSON you have given us does not have a "date" element, but your image shows you using $.date as your JSONPath. 

With the example you gave, I set the loop to ....

"$.borrowers.[*]"

....then I set the date of birth column to ....

"date_of_birth"

....and I returned....

ISODate("1959-01-17T00:00:00.000Z")

Obviously there will be a requirement to format this appropriately, but it retrieves the correct value.