Using MVZip and MVExpand on MultiValue fields where array sometimes doesnt exists

[rajkumarsowmy]

{
"timestamp": "2019-04-11T16:44:45.497462",
"payload": {
"KEY_CHK_DCN_NBR": "19054",
"recommendations": [
{
"modelName": "abc",
"description": "30",
"actionCode": "0261109614",
"actionValue": 0.027422948195084923
},
{
"modelName": "abc",
"description": "30",
"actionCode": "0261109614",
"actionValue": 0.027422948195084923
}
],
"respCd": "700",
}

I have a api logging this information in splunk.
I need to extract
timestamp, payload{}.KEY_CHK_DCN_NBR, payload{}.recommendations.actionCode and payload{}.recommendations.actionvalue

i tried below,

|spath output="DCN Number" path=payload.KEY_CHK_DCN_NBR
|spath output=Timestamp path=timestamp
|spath path=payload.recommendations{} output=r
|mvexpand r
|rename r as _raw
|kv
|rename actionCode ,actionValue
|table "DCN Number" actionCode actionValue Timestamp
| search "DCN Number"!=null

what happens is, in some of the request recommendation array may not be coming, still i need to capture KEY_CHK_DCN_NBR and timestamp and empty value for actioncode and actionvalue.

with my try im able to get all the non-null value.

can anyone help here?

[somesoni2 · Accepted solution]

Give this a try

your base search | spath | table payload* timestamp | rename payload.recommendations{}.* as * payload.* as *  | fields - description modelName respCd | eval temp=coalesce(mvzip(actionCode,actionValue,"##"), "") | mvexpand temp | rex field=temp "(?<actionCode>.+)##(?<actionValue>.+)" | fields - temp

See this runanywhere search with sample data

| gentimes start=-1 | eval _raw="{
 \"timestamp\": \"2019-04-11T16:44:45.497462\",
\"payload\": {
 \"KEY_CHK_DCN_NBR\": \"19054\",
 \"recommendations\": [
 {
 \"modelName\": \"abc\",
 \"description\": \"30\",
 \"actionCode\": \"0261109614\",
 \"actionValue\": 0.027422948195084923
 },
 {
 \"modelName\": \"abc\",
 \"description\": \"30\",
 \"actionCode\": \"0261109614\",
 \"actionValue\": 0.027422948195084923
 }
 ],
 \"respCd\": \"700\",
}" | table _raw  | append [| gentimes start=-1 | eval _raw="{
 \"timestamp\": \"2019-04-11T16:44:45.497462\",
\"payload\": {
 \"KEY_CHK_DCN_NBR\": \"19054\",
 \"respCd\": \"700\",
}" | table _raw ] | spath | table payload* timestamp | rename payload.recommendations{}.* as * payload.* as *  | fields - description modelName respCd | eval temp=coalesce(mvzip(actionCode,actionValue,"##"), "") | mvexpand temp | rex field=temp "(?<actionCode>.+)##(?<actionValue>.+)" | fields - temp

[rajkumarsowmy]

Thank you very much @somesoni2 this resolved my issues!!