Try this:
| spath path=Key2.Manifest{}
| mvexpand Key2.Manifest{}
| spath input=Key2.Manifest{}
| foreach City Inventory.* code
[eval <<FIELD>>_origin = if(flight == "start", '<<FIELD>>', null()), <<FIELD>>_end = if(flight == "end", '<<FIELD>>', null())]
| stats values(*_*) as *_*
Your sample data gives
City_end | City_origin | Inventory.snacks_end | Inventory.snacks_origin | Inventory.status_end | Inventory.status_origin | code_end | code_origin |
Las Vegas | Los Angeles | 56 | 300 | near empty | full | 7470 | 7870 |
Here is a data emulation you can play with and compare with real data
| makeresults
| eval _raw = "{
\"Key1\": \"Value1\",
\"Key2\": {
\"subKey2_1\": \"sub value1 for key2\",
\"Manifest\": [
{
\"flight\": \"start\",
\"City\": \"Los Angeles\",
\"code\": 7870,
\"Inventory\": {
\"snacks\": 300,
\"status\": \"full\"
}
},
{
\"flight\": \"end\",
\"City\": \"Las Vegas\",
\"code\": 7470,
\"Inventory\": {
\"snacks\": 56,
\"status\": \"near empty\"
}
}
],
\"subKey2_3\": \"sub value3 for key2\"
},
\"Key3\": \"Value3\",
\"Key4\": \"Value4\"
}"
``` data emulation above ```
Try this:
| spath path=Key2.Manifest{}
| mvexpand Key2.Manifest{}
| spath input=Key2.Manifest{}
| foreach City Inventory.* code
[eval <<FIELD>>_origin = if(flight == "start", '<<FIELD>>', null()), <<FIELD>>_end = if(flight == "end", '<<FIELD>>', null())]
| stats values(*_*) as *_*
Your sample data gives
City_end | City_origin | Inventory.snacks_end | Inventory.snacks_origin | Inventory.status_end | Inventory.status_origin | code_end | code_origin |
Las Vegas | Los Angeles | 56 | 300 | near empty | full | 7470 | 7870 |
Here is a data emulation you can play with and compare with real data
| makeresults
| eval _raw = "{
\"Key1\": \"Value1\",
\"Key2\": {
\"subKey2_1\": \"sub value1 for key2\",
\"Manifest\": [
{
\"flight\": \"start\",
\"City\": \"Los Angeles\",
\"code\": 7870,
\"Inventory\": {
\"snacks\": 300,
\"status\": \"full\"
}
},
{
\"flight\": \"end\",
\"City\": \"Las Vegas\",
\"code\": 7470,
\"Inventory\": {
\"snacks\": 56,
\"status\": \"near empty\"
}
}
],
\"subKey2_3\": \"sub value3 for key2\"
},
\"Key3\": \"Value3\",
\"Key4\": \"Value4\"
}"
``` data emulation above ```
This was perfect! Thanks!
Sample picture of the goal output