Hello,
I'm looking for help with an expression to extract values in nested JSON objects.
I've figured out how to extract any elements in "field" array where "lastModified" is after "lastExtractionDate".
But problem is that I also need to extract the values in elements that has a "plannedChanges" object.
See the provided examples below for clarification:
This is how the input documents look:
{
"guid": "bdb6cdfc-8008-4a1d-95df-8e789732ad32",
"name": "Eric Employee",
"createdOn": "2016-04-14T11:50:28.590+02:00",
"lastModified": "2024-06-10T00:05:00.700+02:00",
"lastExtractionDate": "2024-05-10T14:11:00+02:00",
"hasPlannedChange": "1",
"firstName": "Eric",
"lastName": "Employee",
"legalEntity": "Company Inc",
"field": [
{
"name": "Country",
"lastModified": "2024-05-17T12:46:31.460+02:00",
"dataValidFrom": "2024-05-17",
"type": "SCALE",
"typeId": "8",
"data": {
"guid": "630dd5a0-aa2a-4ab7-9977-c1e9d48cb379",
"value": "Denmark",
"alternativeExportValue": "DK"
},
"visible": "1"
},
{
"name": "E-mail",
"lastModified": "2024-06-10T00:05:00.700+02:00",
"dataValidFrom": "2024-06-10",
"type": "TEXT",
"typeId": "1",
"data": {
"value": "eric_employee_2@company.com"
},
"visible": "1",
"plannedChange": {
"lastModified": "2024-06-10T13:39:30.923+02:00",
"dataValidFrom": "2024-06-11",
"dataValidTo": "",
"status": "1",
"data": {
"value": "eric.employee@company.com"
}
}
},
{
"name": "Manager",
"lastModified": "2024-06-06T10:38:42.333+02:00",
"dataValidFrom": "2024-06-06",
"type": "PERSON",
"typeId": "10",
"data": {
"guid": "a4666edc-d1ab-456a-ae06-8625eb933c06",
"value": "Manager Boss",
"username": "man.boss@company.com",
"employeeId": "200384"
},
"visible": "1"
}
]
}
The output should look something like this:
{
"guid": "bdb6cdfc-8008-4a1d-95df-8e789732ad32",
"name": "Eric Employee",
"lastModified": "2024-06-10T00:05:00.700+02:00",
"lastExtractionDate": "2024-05-10T14:11:00+02:00",
"hasPlannedChange": "1",
"firstName": "Eric",
"lastName": "Employee",
"legalEntity": "Company Inc",
"changedFields": [
{
"name": "Country",
"value": "Denmark",
"dataValidFrom": "2024-05-17"
},
{
"name": "E-mail",
"value": "eric.employee@company.com"
"isPlannedChange": "1",
"dataValidFrom": "2024-06-11",
"dataValidTo": "2025-06-11", // Exclude if value is empty string.
},
{
"name": "Manager",
"value": "Manager Boss",
"dataValidFrom": "2024-06-06",
}
]
}
Maybe a bit messy explanation, but I think you get the gist of it.
Very grateful for any help solving this.
Best regards
Teddie
