cancel
Showing results for 
Search instead for 
Did you mean: 

Expression to extract values in nested JSON objects

tliljekvist
New Contributor

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

1 REPLY 1

koryknick
Employee
Employee

@tliljekvist - Attached is a sample pipeline that I believe does what you want with some help of the Mapper snap's  "Mapping root" property to easily access the array sub-elements for initial formatting, then the Array.map() and Object.filter() methods to cleanup the empty values.