Fetch data from json string[]

[vickycoder27]

I have a curl response which is json string[], I am able to fetch the data using split(), mvexpand() and then substring. But the problem with substring is, if the sequence of value for a key changes then result is not correct. Tried mvindex on the manipulated data but it doesn't work or rex, not sure if I am doing any thing wrong.

Below is my log and I am interested in "Response []" from which I want to pull userId i.e. 9401850890, 9801850840, 9801850841.

APIName=TestApi, HTTP_STATUS=200, totalTime=2346, Response=[{"id":11168715,"state":"Open","title":"TESTS NOTIFICATION - SPIKE IN USEAGE userId : 9401850890"},{"id":11168716,"state":"Closed","title":"TESTS NOTIFICATION - SPIKE IN USEAGE userId : 9801850840"},{"id":21172617,"state":"Verify","title":"TESTS NOTIFICATION - SPIKE IN USEAGE userId : 9801850841"}]

Query that I tried :

index=api_stats source=apistats earliest=-10m@m latest=-0m@m | eval x=ltrim(Response,“[”) | eval n=rtrim(x, “]”) | eval temp=split(n,“}”)| mvexpand temp| eval y=ltrim(temp,“,”) | eval testData=mvindex(y,-1) | eval testId=substr(testData, 7, 9)| eval apiCallerID=substr(testData, 92, 10) | table testData,testId,apiCallerID

[to4kawa]

index=_internal |head 1 | table _raw _time | eval _raw="APIName=TestApi, HTTP_STATUS=200, totalTime=2346, Response=[{\"id\":11168715,\"state\":\"Open\",\"title\":\"TESTS NOTIFICATION - SPIKE IN USEAGE userId : 9401850890\"},{\"id\":11168716,\"state\":\"Closed\",\"title\":\"TESTS NOTIFICATION - SPIKE IN USEAGE userId : 9801850840\"},{\"id\":21172617,\"state\":\"Verify\",\"title\":\"TESTS NOTIFICATION - SPIKE IN USEAGE userId : 9801850841\"}]"
| kv
| spath input=Response {} output=response
| stats count by response
| spath input=response
| fields - count response

how about this?

[vickycoder27]

@to4kawa Much cleaner approach, I will try to explore more of spath().

[richgalloway · Accepted solution]

Perhaps this will help.

| makeresults 
| eval Response="[{\"id\":11168715,\"state\":\"Open\",\"title\":\"TESTS NOTIFICATION - SPIKE IN USEAGE userId : 9401850890\"},{\"id\":11168716,\"state\":\"Closed\",\"title\":\"TESTS NOTIFICATION - SPIKE IN USEAGE userId : 9801850840\"},{\"id\":21172617,\"state\":\"Verify\",\"title\":\"TESTS NOTIFICATION - SPIKE IN USEAGE userId : 9801850841\"}]"
```Above just creates test data```
| eval x=ltrim(Response,"[") 
| eval n=rtrim(x, "]") 
| eval temp=split(n,"}") 
| mvexpand temp 
| eval y=ltrim(temp,",") 
| eval testData=mvindex(y,-1) 
| rex field=testData "id\":(?<testId>\d+)"
| rex field=testData "userId\s:\s(?<apiCallerID>\d+)"
| table testData,testId,apiCallerID