json array searching

[Maurice]

Hi, 

I am trying to return results if an item in the array has both values set to specific values.

ie bu = "blob" and disp="enforce" on the one array item

However,  my search seems to happen across items.

|makeresults
|eval _raw ="{
\"sp_v\":[
{\"bu\":\"blob\",\"disp\":\"enforce\"},
{\"bu\":\"inline\",\"disp\":\"report\"}
]
}"
| spath
| search sp_v{}.bu=blob AND sp_v{}.disp=report

This is returning result as the first item has 'blob' and the second has 'report'.

I would not expect any results in this search

Would appreciate any help,

Kind Regards,

Maurice

[kamlesh_vaghela]

@Maurice 

Can you please try this?

YOUR_SEARCH
| spath
| rename sp_v{}.* as *
| eval t = mvzip(bu,disp) | mvexpand t| eval bu=mvindex(split(t,","),0),disp=mvindex(split(t,","),1)
| where bu="blob" AND disp="report"

My Sample Search :

|makeresults
|eval _raw ="{
\"sp_v\":[
{\"bu\":\"blob\",\"disp\":\"enforce\"},
{\"bu\":\"inline\",\"disp\":\"report\"}
]
}"
| spath
| rename sp_v{}.* as *
| eval t = mvzip(bu,disp) | mvexpand t| eval bu=mvindex(split(t,","),0),disp=mvindex(split(t,","),1)
| where bu="blob" AND disp="report"

Thanks
KV
▄︻̷̿┻̿═━一

If any of my reply helps you to solve the problem Or gain knowledge, an upvote would be appreciated.

[Maurice]

Thanks KV,

 That works great.

 My only issue is that in my dashboard I am building up this query using inputs (for most properties on the array).

So there could be up to 7 or 8 properties to search on.

I notice from the docs that mvzip only works with 2 properties by default. so I tried with 3 which worked(see below):

|makeresults
|eval _raw ="{
\"sp_v\":[
{\"bu\":\"blob\",\"disp\":\"enforce\", \"an\":\"test\"},
{\"bu\":\"inline\",\"disp\":\"report\", \"an\":\"another\"}
]
}"
| spath
| rename sp_v{}.* as *
| eval t = mvzip(mvzip(bu,disp), an)
| mvexpand t
| eval bu=mvindex(split(t,","),0),disp=mvindex(split(t,","),1), an=mvindex(split(t,","),2)
| where bu="blob" AND disp="enforce" AND an="test"

I'd imagine the code would become hard to read as I have to nest mvzip inside itself and also change the index

Do you know of a more readable way it accomplish this with  more properties?

Kind regards,

Maurice

[kamlesh_vaghela · Accepted solution]

@Maurice 

If you have multiple fields then I suggest this solution.

|makeresults
|eval _raw ="{
\"sp_v\":[
{\"bu\":\"blob\",\"disp\":\"enforce\", \"an\":\"test\"},
{\"bu\":\"inline\",\"disp\":\"report\", \"an\":\"another\"}
]
}"
| spath path=sp_v{} output=data | stats count by data | rename data as _raw | extract 
| where bu="blob" AND disp="enforce" AND an="test"

Thanks
KV
▄︻̷̿┻̿═━一

If any of my reply helps you to solve the problem Or gain knowledge, an upvote would be appreciated.

[Maurice]

Thanks KV,

That looks like much more maintainable code. 😀

 One last think, I wanted to create a timechart also off the data but it fails once i use a real index instead of make results.

I am presuming it has something to do with _time not being in the result set:

index=myIndex source=mySource  spath path=sp_v{} output=data | stats count by data | rename data as _raw | extract | timechart span=1d count(bu)  useother=f usenull=f

Any ideas?

Kind Regards,

Maurice

[kamlesh_vaghela]

@Maurice 

For timechart try this.

index=myIndex source=mySource 
| spath path=sp_v{} output=data 
| stats count by _time data 
| rename data as _raw | extract 
| timechart span=1d count(bu)  useother=f usenull=f

🙂