How can I calculate column differences when column names are unknown?

[c_wsleem]

My datasource is a json structure which will include the following on each record:

{

"metrics":
[
{"name":"MetricName1", "value":"1", "units": "s"},
{"name":"MetricName2", "value":"1", "units": "s"},
{"name":"MetricName3", "value":"2", "units": "s"}
]

}

The initial search will produce 2 records with different timestamps
My SPL is expanding the metrics field, then recombining the data to give the metric names as rows, the timestamps as columns so that I can chart how the vlaues change between the two events.
SPL:-

| eval name=strftime('_time',"%d/%m/%Y %H:%M:%S") 
|spath path=metrics{} output=X
|  mvexpand X
| fields name, X
|fields - _raw, _time 
| spath input=X  
| fields - X, units 
| rename type as Metric
| eval {Metric}='value'
| fields name Metric value 
| xyseries Metric, name, value

Output:-

| Metric                  | Event1Date | Event2Date|
| MetricName1     | 1                    | 2                  |
| MetricName2     | 2                    | 4                  |
| MetricName3     | 2                    | 5                  |

I would like to add a 3rd column containing the differences between the two values:

| Metric                  | Event1Date | Event2Date| Difference |
| MetricName1     | 1                    | 2                  | 1                 |
| MetricName2     | 2                    | 4                  | 2                 |
| MetricName3     | 2                    | 5                  | 3                 |

But I do not know what the column names "Event1Date" and "Event2Date" will be
Can I access the fields by an index number?
eg eval diff = columns[2] - columns[1]
or similar
NB The actual Metric Names are also unknown (ie they could be anything)

[somesoni2 · Accepted solution]

Give this a try (note that format of timestamp has been changed)

| eval name=strftime('_time',"%Y/%m/%d %H:%M:%S") 
 |spath path=metrics{} output=X
 |  mvexpand X
 | fields name, X
 |fields - _raw, _time 
 | spath input=X  
 | fields - X, units 
 | rename type as Metric
 | eval {Metric}='value'
 | fields name Metric value 
 | xyseries Metric, name, value
| eval Difference=0 | foreach 2* [| eval Difference=if(Difference=0,'<<FIELD>>','<<FIELD>>"-Difference)]

[c_wsleem]

Perfect - Thank you

[elliotproebstel]

Hey, that's clever!