How to fill null values in JSon field

[nzamorano123]

Spoiler

How to fill null values in JSon field

hello community, good afternoon
I am trapped in a challenge which I cannot achieve how to obtain the expected result.

Currently I have a log that contains a field in JSon format:

{
"data": {
"steps": [
{
"code": "11501",
"index": "0",
"counter": "1",
"stepType": "Req & Resp",
"optional": "False",
"parallel": "False",
"parents": "-1",
"children": "1",
"begin": "2021-03-24T10:00:02.597-03:00",
"serviceOut": "2021-03-24T10:00:02.598-03:00",
"serviceIn": "2021-03-24T10:00:02.857-03:00",
"end": "2021-03-24T10:00:02.859-03:00",
"timedout": "False",
"lateResp": "False",
"status": "C",
"return": ""
},
{
"code": "11502",
"index": "1",
"counter": "2",
"stepType": "Req & Resp",
"optional": "False",
"parallel": "False",
"parents": "0",
"children": "2",
"begin": "2021-03-24T10:00:02.860-03:00",
"serviceOut": "2021-03-24T10:00:02.886-03:00",
"serviceIn": "2021-03-24T10:00:03.238-03:00",
"end": "2021-03-24T10:00:03.243-03:00",
"timedout": "False",
"lateResp": "False",
"status": "C",
"return": "03546"
},
{
"code": "11505",
"index": "4",
"counter": "3",
"stepType": "Req & Resp",
"optional": "False",
"parallel": "False",
"parents": "3",
"children": "5",
"begin": "2021-03-24T10:00:03.246-03:00",
"serviceOut": "2021-03-24T10:00:03.250-03:00",
"serviceIn": "2021-03-24T10:00:03.293-03:00",
"end": "2021-03-24T10:00:03.294-03:00",
"timedout": "False",
"lateResp": "False",
"status": "C",
"return": "03546"
}
]
}
}
Query Splunk:

index="pagos_moviles" source="/veritran/vt-net/log/MP_TRANSAC_LOG_BECP_ND*.log" trxl_resp!="000"

| spath input=trxl_tech_detail | fields _time trxl_tech_detail data.steps*
| fillnull value="-" data.steps*
|rename data.steps{}.begin AS begin, data.steps{}.children AS step, data.steps{}.code AS code, data.steps{}.counter AS counter, data.steps{}.end AS end, data.steps{}.index AS index, data.steps{}.lateResp AS lateResp, data.steps{}.optional AS optional, data.steps{}.parallel AS parallel, data.steps{}.parents AS parents, data.steps{}.return AS return, data.steps{}.serviceIn AS serviceIn, data.steps{}.serviceOut AS serviceOut, data.steps{}.status AS status, data.steps{}.stepType AS stepType, data.steps{}.timedout AS timedout| eval campos=mvzip(begin,mvzip(step,mvzip(code,mvzip(end,mvzip(index,mvzip(status,mvzip(return,timedout)))))))
| mvexpand campos | eval campos=split(campos,",")|eval begin =mvindex(campos,0)
|eval step=mvindex(campos,1)
|eval code =mvindex(campos,2)
|eval end =mvindex(campos,3)
|eval index =mvindex(campos,4)
|eval status=mvindex(campos,5)
|eval return=mvindex(campos,6)
|eval timedout =mvindex(campos,7)| table _time trxl_tech_detail campos Servicio Operacion begin step code counter end index return status timedout trxl_resp Detalle Tipo_Error trxl_transaction_code
| fillnull value="-" return

Result: 

As you can see in the photo, it only expands 2 rows and they should be 3. I also noticed that the return value does not always exist and I think this is what finally causes it not to expand all the json.

Is there a way to fill the null values in the json with some character?

In advance, thank you very much and excuse me for my English but it is not my native language.

[ITWhisperer · Accepted solution]

Yes, the issue is with the null values for return (although in your example, return is an empty string not null) - try extracting the array, mvexpand, then extract the fields - this saves on doing the mvzip and split as well.

index="pagos_moviles" source="/veritran/vt-net/log/MP_TRANSAC_LOG_BECP_ND*.log" trxl_resp!="000"

| spath input=trxl_tech_detail path=data.steps{}
| mvexpand data.steps{}
| spath input=data.steps{}
| table _time trxl_tech_detail campos Servicio Operacion begin step code counter end index return status timedout trxl_resp Detalle Tipo_Error trxl_transaction_code
| fillnull value="-" return

[nzamorano123]

 This worked perfectly. Thank you very much for your help.

I understand that I was skipping the step of specifying to spath the data that I wanted to expand.

Again, thank you very much!