Json Multi array extraction with changing/missing field name

[psable]

Hi,
I am trying to extract the json fields where one of the fields name can change between "stringValue" or "doubleValie" and this list could be quite long , in below example I have given only two elements.

"Diagnostic": {
"Version": 2,
"diagnostic_list": [
{
"name": "FUND",
"stringValue": "One year"
},
{
"name": "RATE",
"doubleValue": 10
},
{
"name": "DATE",
"stringValue": "2016-01-01"
}

}

My json is similar to this:

rename Diagnostic{}.* as * | eval values=mvzip(name, stringValue) | mvexpand values | eval values = split(values,",") | eval name=mvindex(values,0) | eval name=mvindex(values,1) | where name=="FUND"

As you can see the problem is stringValue above could be doubleValue, so in my results I see this when i write above fields in table, the second column i.e. stringValue sometimes doesnt align with the corresponding "name" field in table.

How can I switch between stringValue and doubleValue at the run time?

Thanks,
Prashant

[psable]

Thank you !

[woodcock · Accepted solution]

Like this:

... | rex field=_raw mode=sed "s/\"(doubleValue)\"/\"stringValue\"/g"
| rex field=_raw mode=sed "s/\"score\": {[\r\n\s]*\"scorevalue\": (\d*),[\r\n\s]*\"valueType\": \"(\w*)\"[\r\n\s]*},[\r\n\s]*\"stringValue\": \"(\w*)\",/\"stringValue\": \"\3 \1 \2\"/g"
| spath | rename diagnostic{}.* AS *
| eval nameValue=mvzip(name,stringValue, "=")
| mvexpand nameValue | fields - name stringValue