topic Re: How to extract a JSON value based on a condition in Getting Data In

How to extract a JSON value based on a condition

charan986 — Fri, 06 Mar 2020 17:35:20 GMT

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?

Re: How to extract a JSON value based on a condition

manjunathmeti — Fri, 06 Mar 2020 18:36:31 GMT

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

Re: How to extract a JSON value based on a condition

charan986 — Fri, 06 Mar 2020 21:49:14 GMT

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": ""
}
} ] }

Re: How to extract a JSON value based on a condition

to4kawa — Fri, 06 Mar 2020 23:22:26 GMT

| 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"

Re: How to extract a JSON value based on a condition

to4kawa — Fri, 06 Mar 2020 23:29:25 GMT

....
| 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.

Re: How to extract a JSON value based on a condition

charan986 — Sat, 07 Mar 2020 01:14:05 GMT

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

Re: How to extract a JSON value based on a condition

to4kawa — Sat, 07 Mar 2020 02:41:44 GMT

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

Re: How to extract a JSON value based on a condition

charan986 — Tue, 10 Mar 2020 23:15:23 GMT

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": ""
}
} ] }

Re: How to extract a JSON value based on a condition

woodcock — Wed, 11 Mar 2020 03:06:19 GMT

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)

Re: How to extract a JSON value based on a condition

manjunathmeti — Wed, 11 Mar 2020 06:57:29 GMT

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

Re: How to extract a JSON value based on a condition

charan986 — Wed, 11 Mar 2020 18:23:11 GMT

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