Hi I've two different payloads returned from my search and I need to create a table from values extracted from the payloads, please find the find the payloads below
{ "appointment": {
"appointmentId": 0,
"key": "",
"Durations": 60,
"JType": "Medium",
"StartTime": "0001-01-01T00:00:00Z",
"EndTime": "0001-01-01T00:00:00Z",
"isDegOnly": false,
"softId": 112892 },
"commonID": "REDRF3243",
"slotStatusList": [
{
"date": "2020-03-24T00:00:00Z",
"status": [
{
"StartTime": "2020-03-24T06:30:00Z",
"EndTime": "2020-03-24T08:30:00Z",
"IsAvailable": false,
"score": 0
},
{
"StartTime": "2020-03-24T08:30:00Z",
"EndTime": "2020-03-24T10:30:00Z",
"IsAvailable": false,
"score": 0
},
{
"StartTime": "2020-03-24T10:30:00Z",
"EndTime": "2020-03-24T12:30:00Z",
"IsAvailable": true,
"score": 100
}
],
"error": {
"message": ""
}
},
{
"date": "2020-03-25T00:00:00Z",
"status": [
{
"StartTime": "2020-03-25T06:30:00Z",
"EndTime": "2020-03-25T08:30:00Z",
"IsAvailable": false,
"score": 0
},
{
"StartTime": "2020-03-25T08:30:00Z",
"EndTime": "2020-03-25T10:30:00Z",
"IsAvailable": true,
"score": 92.44
},
{
"StartTime": "2020-03-25T10:30:00Z",
"EndTime": "2020-03-25T12:30:00Z",
"IsAvailable": false,
"score": 0
}
],
"error": {
"message": ""
}
} ] }
{
"Appointment": {
"Durations": 60,
"TimeSlotStartTime": "0001-01-01T00:00:00+00:00",
"TimeSlotEndTime": "0001-01-01T00:00:00+00:00",
"IsDefOnly": false,
"Job": "Medium",
"SoftId": 113291
},
"commonID": "REDRF3243",
"GraceMinutes": 0,
"BufferMinutes": 0,
"RouteRequestList": [
{
"Date": "2020-04-02T00:00:00+00:00",
"JBID": 11936
},
{
"Date": "2020-04-03T00:00:00+00:00",
"JBID": 11936
}
]
}
I need a table created like below where the Jtype, StartTime, EndTime comes from payload1 and JBID comes from Payload2 and the results needs to be merged based on the COMMONID. and there is logic where I need only the first encountered "IsAvailable": true start and end time values
Please advise how can I achieve this
1. JBID JType START_TIME END_TIME COMMONID
2. 11936 Medium 2020-03-24T10:30:00 2020-03-24T12:30:00 REDRF3243
Hello @charan986,
Assuming that the start and end times you want are always in the first IsAvailable=true
status in the slotStatusList array and the first JBID in the RouteRequestList array, lines 6 and 7 in the below query will return the table that you are looking for:
| makeresults
| eval data="{ \"appointment\": {\"appointmentId\": 0,\"key\": \"\",\"Durations\": 60,\"JType\": \"Medium\",\"StartTime\": \"0001-01-01T00:00:00Z\",\"EndTime\": \"0001-01-01T00:00:00Z\",\"isDegOnly\": false,\"softId\": 112892 },\"commonID\": \"REDRF3243\",\"slotStatusList\": [{\"date\": \"2020-03-24T00:00:00Z\",\"status\": [{\"StartTime\": \"2020-03-24T06:30:00Z\",\"EndTime\": \"2020-03-24T08:30:00Z\",\"IsAvailable\": false,\"score\": 0},{\"StartTime\": \"2020-03-24T08:30:00Z\",\"EndTime\": \"2020-03-24T10:30:00Z\",\"IsAvailable\": false,\"score\": 0},{\"StartTime\": \"2020-03-24T10:30:00Z\",\"EndTime\": \"2020-03-24T12:30:00Z\",\"IsAvailable\": true,\"score\": 100}],\"error\": {\"message\": \"\"}},{\"date\": \"2020-03-25T00:00:00Z\",\"status\": [{\"StartTime\": \"2020-03-25T06:30:00Z\",\"EndTime\": \"2020-03-25T08:30:00Z\",\"IsAvailable\": false,\"score\": 0},{\"StartTime\": \"2020-03-25T08:30:00Z\",\"EndTime\": \"2020-03-25T10:30:00Z\",\"IsAvailable\": true,\"score\": 92.44},{\"StartTime\": \"2020-03-25T10:30:00Z\",\"EndTime\": \"2020-03-25T12:30:00Z\",\"IsAvailable\": false,\"score\": 0}],\"error\": {\"message\": \"\"}} ] }
{\"Appointment\": {\"Durations\": 60,\"TimeSlotStartTime\": \"0001-01-01T00:00:00+00:00\",\"TimeSlotEndTime\": \"0001-01-01T00:00:00+00:00\",\"IsDefOnly\": false,\"Job\": \"Medium\",\"SoftId\": 113291},\"commonID\": \"REDRF3243\",\"GraceMinutes\": 0,\"BufferMinutes\": 0,\"RouteRequestList\": [{\"Date\": \"2020-04-02T00:00:00+00:00\",\"JBID\": 11936},{\"Date\": \"2020-04-03T00:00:00+00:00\",\"JBID\": 11936}]}"
| rex field=data max_match=0 "(?<data>[^\n]+)"
| mvexpand data
| eval statuses=spath(data, "slotStatusList{}.status{}"), commonID=spath(data, "commonID"), firstAvailable=mvindex(mvfilter(match(statuses, "\"IsAvailable\": true")),0), StartTime=spath(firstAvailable, "StartTime"), EndTime=spath(firstAvailable, "EndTime"), JBID=mvindex(spath(data, "RouteRequestList{}.JBID"),0), JType=spath(data, "appointment.JType")
| stats values(JBID) as JBID values(JType) as JType values(StartTime) as StartTime values(EndTime) as EndTime by commonID
Dealing with multivalued fields in json is always a pain, but can be managed a bit by managing the parent array using mvfilter and mvindex and then using spath on the resulting output, which is what I am doing with statuses=spath(data, "slotStatusList{}.status{}")
that creates a multivalued list of the different json payloads of the status array in the slotStatusList array and firstAvailable=mvindex(mvfilter(match(statuses, "\"IsAvailable\": true")),0)
which returns the first status that contains "IsAvailable": true
. Let me know if anything doesn't make sense.
what's index and sourcetype both?
Hi @to4kawa, thanks for responding
these payloads are returned from the same index and source type, if thats what you're asking
I tried using the query from manjunathmeti but its returns empty JBID, So I wanted to post both the payloads and the complete requirement
I see, you can do it.
@to4kawa Please find my query below, this results in the table with empty JBID
1. JBID JType START_TIME END_TIME COMMONID
2. Medium 2020-03-24T10:30:00 2020-03-24T12:30:00 REDRF3243
My search -- | spath body output=body
| spath commonID output= commonID
| eval _raw=body | spath appointment output=appointment
| eval _raw=appointment | spath JType output= JType
| eval _raw=body | spath slotStatusList{} output=slotStatusList
| eval _raw=slotStatusList | spath status output=status
| spath output=IsAvailable status{}.IsAvailable
| spath output=StartTime status{}.StartTime
| spath output=EndTime status{}.EndTime
| where match(IsAvailable, "true") | eval zipped=mvzip(IsAvailable,mvzip(StartTime, EndTime))
| mvexpand zipped
| where match(zipped, "true")
| eval zipped=split(zipped, ",")
| eval IsAvailable=mvindex(zipped,0), StartTime=mvindex(zipped,1), EndTime=mvindex(zipped,2)
| spath body output=body
| eval _raw=body | spath RouteRequestList{} output=RouteRequestList
| eval _raw=RouteRequestList | spath JBID output= JBID
| stats first(*) as * by commonID
| table JBID JType StartTime EndTime commonID