How to extract a JSON value based on a condition

[charan986]

I have payload as below and I need the StartTime and EndTime values where the payload has the first IsAvailable is equal to true

 "StatusList": [
     {
       "date": "2020-03-13T00:00:00Z",
       "status": [
         {
           "StartTime": "2020-03-13T06:30:00Z",
           "EndTime": "2020-03-13T08:30:00Z",
           "IsAvailable": false,
           "score": 91.05
         },
         {
           "StartTime": "2020-03-13T08:30:00Z",
           "EndTime": "2020-03-13T10:30:00Z",
           "IsAvailable": false,
           "score": 94.29
         },
         {
           "StartTime": "2020-03-13T10:30:00Z",
           "EndTime": "2020-03-13T12:30:00Z",
           "IsAvailable": **true**,
           "score": 100
         },
         {
           "StartTime": "2020-03-13T12:30:00Z",
           "EndTime": "2020-03-13T14:30:00Z",
           "IsAvailable": true,
           "score": 96.1
         },
         {
           "StartTime": "2020-03-13T14:30:00Z",
           "EndTime": "2020-03-13T16:30:00Z",
           "IsAvailable": true,
           "score": 90.39
         },
         {
           "StartTime": "2020-03-13T16:30:00Z",
           "EndTime": "2020-03-13T18:30:00Z",
           "IsAvailable": false,
           "score": 0
         }
       ],

How can I achieve this?

[woodcock]

Your stuff is not valid JSON so first fix that. Once that is true, just do this:

| makeresults 
| eval _raw="{
    \"StatusList\": [{
        \"date\": \"2020-03-13T00:00:00Z\",
        \"status\": [{
                \"StartTime\": \"2020-03-13T06:30:00Z\",
                \"EndTime\": \"2020-03-13T08:30:00Z\",
                \"IsAvailable\": false,
                \"score\": 91.05
            },
            {
                \"StartTime\": \"2020-03-13T08:30:00Z\",
                \"EndTime\": \"2020-03-13T10:30:00Z\",
                \"IsAvailable\": false,
                \"score\": 94.29
            },
            {
                \"StartTime\": \"2020-03-13T10:30:00Z\",
                \"EndTime\": \"2020-03-13T12:30:00Z\",
                \"IsAvailable\": true,
                \"score\": 100
            },
            {
                \"StartTime\": \"2020-03-13T12:30:00Z\",
                \"EndTime\": \"2020-03-13T14:30:00Z\",
                \"IsAvailable\": true,
                \"score\": 96.1
            },
            {
                \"StartTime\": \"2020-03-13T14:30:00Z\",
                \"EndTime\": \"2020-03-13T16:30:00Z\",
                \"IsAvailable\": true,
                \"score\": 90.39
            },
            {
                \"StartTime\": \"2020-03-13T16:30:00Z\",
                \"EndTime\": \"2020-03-13T18:30:00Z\",
                \"IsAvailable\": false,
                \"score\": 0
            }
        ]
    }]
}"
| rename COMMENT AS "I have payload as below and I need the StartTime and EndTime values where the payload has the first IsAvailable is equal to true" 
| kv
| eval FirstTruePos = mvfind('StatusList{}.status{}.IsAvailable', "true")
| eval StartTime = mvindex('StatusList{}.status{}.StartTime', FirstTruePos)
| eval EndTime = mvindex('StatusList{}.status{}.EndTime', FirstTruePos)
| eval MvField = "1 2 3 4 5 6 7"
| makemv MvField
| eval MvField = mvindex(MvField, FirstTruePos)

[to4kawa]

| makeresults 
| eval _raw="{ \"appointment\": {
\"appointmentId\": 2,
\"key\": \"\",
\"location\": {
\"latitude\": 30.7086735,
\"longitude\": -96.42541609999999
},
\"jobDuration\": 60,
\"Type\": \"Medium\",
\"timeSlotStartTime\": \"0001-01-01T00:00:00Z\",
\"timeSlotEndTime\": \"0001-01-01T00:00:00Z\",
\"isVehicleOnly\": false,
\"softId\": 12331 }, \"StatusList\": [
{
\"date\": \"2020-03-19T00:00:00Z\",
\"status\": [
{
\"StartTime\": \"2020-03-19T06:30:00Z\",
\"EndTime\": \"2020-03-19T08:30:00Z\",
\"IsAvailable\": false,
\"score\": 0
},
{
\"StartTime\": \"2020-03-19T08:30:00Z\",
\"EndTime\": \"2020-03-19T10:30:00Z\",
\"IsAvailable\": false,
\"score\": 0
},
{
\"StartTime\": \"2020-03-19T10:30:00Z\",
\"EndTime\": \"2020-03-19T12:30:00Z\",
\"IsAvailable\": true,
\"score\": 100
},
{
\"StartTime\": \"2020-03-19T12:30:00Z\",
\"EndTime\": \"2020-03-19T14:30:00Z\",
\"IsAvailable\": false,
\"score\": 0
},
{
\"StartTime\": \"2020-03-19T14:30:00Z\",
\"EndTime\": \"2020-03-19T16:30:00Z\",
\"IsAvailable\": true,
\"score\": 0
},
{
\"StartTime\": \"2020-03-19T16:30:00Z\",
\"EndTime\": \"2020-03-19T18:30:00Z\",
\"IsAvailable\": true,
\"score\": 0
},
{
\"StartTime\": \"2020-03-19T18:30:00Z\",
\"EndTime\": \"2020-03-19T20:30:00Z\",
\"IsAvailable\": false,
\"score\": 0
}
],
\"error\": {
\"message\": \"\"
    }
} ] }"
| spath path=StatusList{}.status{} output=status
| spath path=StatusList{}.date 
| spath path=StatusList{}.error.message output=error_message
| spath
| fields - _* StatusList*
| stats values(*) as * by status
| spath input=status
| fields - status
| rename appointment.* as *
| table StartTime EndTime IsAvailable score *
| search IsAvailable="true"

[charan986]

Hi @to4kawa thanks for responding, may I know how can I generate a table with only one row with StartTime EndTime values for first IsAvailable = true

[to4kawa]

for first IsAvailable = true

In my answer sample, '"StartTime": "2020-03-19T10:30:00Z"` right?

....
| spath path=StatusList{}.status{} output=status
| fields - _*
| mvexpand status
| spath input=status
| table StartTime EndTime IsAvailable score
| search IsAvailable="true"
| head 1

[to4kawa]

....
| spath path=StatusList{}.status{} output=status
| fields - _*
| stats count by status
| spath input=status
| fields - status count
| search IsAvailable="true"

If you only want the fields StartTime EndTime IsAvailable score , this query is better.

[manjunathmeti]

hi @charan986, If this is a valid json then it should parsed before indexing. But if it is not then try this query:

| makeresults 
| eval _raw="\"StatusList\": [
{
\"date\": \"2020-03-13T00:00:00Z\",
\"status\": [
{
\"StartTime\": \"2020-03-13T06:30:00Z\",
\"EndTime\": \"2020-03-13T08:30:00Z\",
\"IsAvailable\": false,
\"score\": 91.05
},
{
\"StartTime\": \"2020-03-13T08:30:00Z\",
\"EndTime\": \"2020-03-13T10:30:00Z\",
\"IsAvailable\": false,
\"score\": 94.29
},
{
\"StartTime\": \"2020-03-13T10:30:00Z\",
\"EndTime\": \"2020-03-13T12:30:00Z\",
\"IsAvailable\": true,
\"score\": 100
},
{
\"StartTime\": \"2020-03-13T12:30:00Z\",
\"EndTime\": \"2020-03-13T14:30:00Z\",
\"IsAvailable\": true,
\"score\": 96.1
},
{
\"StartTime\": \"2020-03-13T14:30:00Z\",
\"EndTime\": \"2020-03-13T16:30:00Z\",
\"IsAvailable\": true,
\"score\": 90.39
},
{
\"StartTime\": \"2020-03-13T16:30:00Z\",
\"EndTime\": \"2020-03-13T18:30:00Z\",
\"IsAvailable\": false,
\"score\": 0
}
]," 
| eval _raw=replace(replace(_raw, "\"StatusList\": \[", ""), "\],", "") 
| spath output=IsAvailable status{}.IsAvailable 
| spath output=StartTime status{}.StartTime 
| spath output=EndTime status{}.EndTime
| eval zipped=mvzip(IsAvailable,mvzip(StartTime, EndTime)) 
| mvexpand zipped 
| where match(zipped, "true") 
| eval zipped=split(zipped, ",") 
| eval StartTime=mvindex(zipped,0), EndTime=mvindex(zipped,1), IsAvailable=mvindex(zipped,2) | fields - _raw, _time, zipped

[charan986]

Hi @manjunathmeti, please find the valid payload where I need to extract the values of StartTime and EndTime where the payload encounters its first isAvailable = true
Using your query the table generates 3 rows where isAvailable =true, whereas I need only one row where the first isAvailable is true is encountered. can you pls let me know what needs to be changed

{ "appointment": {
"appointmentId": 2,
"key": "",
"location": {
"latitude": 30.7086735,
"longitude": -96.42541609999999
},
"jobDuration": 60,
"Type": "Medium",
"timeSlotStartTime": "0001-01-01T00:00:00Z",
"timeSlotEndTime": "0001-01-01T00:00:00Z",
"isVehicleOnly": false,
"softId": 12331 }, "StatusList": [
{
"date": "2020-03-19T00:00:00Z",
"status": [
{
"StartTime": "2020-03-19T06:30:00Z",
"EndTime": "2020-03-19T08:30:00Z",
"IsAvailable": false,
"score": 0
},
{
"StartTime": "2020-03-19T08:30:00Z",
"EndTime": "2020-03-19T10:30:00Z",
"IsAvailable": false,
"score": 0
},
{
"StartTime": "2020-03-19T10:30:00Z",
"EndTime": "2020-03-19T12:30:00Z",
"IsAvailable": true,
"score": 100
},
{
"StartTime": "2020-03-19T12:30:00Z",
"EndTime": "2020-03-19T14:30:00Z",
"IsAvailable": false,
"score": 0
},
{
"StartTime": "2020-03-19T14:30:00Z",
"EndTime": "2020-03-19T16:30:00Z",
"IsAvailable": true,
"score": 0
},
{
"StartTime": "2020-03-19T16:30:00Z",
"EndTime": "2020-03-19T18:30:00Z",
"IsAvailable": true,
"score": 0
},
{
"StartTime": "2020-03-19T18:30:00Z",
"EndTime": "2020-03-19T20:30:00Z",
"IsAvailable": false,
"score": 0
}
],
"error": {
"message": ""
}
} ] }

[charan986]

Hi @manjunathmeti , I have not put the whole payload where I have an array of status , can you let me know how can i extract all the status values from below payload
{ "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": ""
}
} ] }

[manjunathmeti]

Hi @charan986,

Try this:

| makeresults 
| eval _raw="{ \"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\": \"\"
    }
} ] }" 
| spath path=slotStatusList{} output=slotStatusList 
| mvexpand slotStatusList 
| spath input=slotStatusList path=status{} output=status 
| mvexpand status 
| spath input=status 
| spath input=slotStatusList 
| spath 
| table StartTime, EndTime, IsAvailable, score, appointment*, error*, commonID

[charan986]

Thanks for responding @manjunathmeti , using the above query i'm able to get only the first status of SlotStatusList array, As show in the picture from the URL below, I need all the status list values from the SlotStausList.
and also can you pls let me know how can get the value of start and end time values where there is a isAvailable = true in the whole payload