topic json array searching in Getting Data In

json array searching

Maurice — Thu, 15 Jul 2021 13:59:01 GMT

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

Re: json array searching

kamlesh_vaghela — Thu, 15 Jul 2021 14:48:37 GMT

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

Re: json array searching

Maurice — Thu, 15 Jul 2021 15:36:17 GMT

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

Re: json array searching

kamlesh_vaghela — Thu, 15 Jul 2021 15:40:16 GMT

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

Re: json array searching

Maurice — Fri, 16 Jul 2021 11:00:21 GMT

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

Re: json array searching

kamlesh_vaghela — Fri, 16 Jul 2021 11:03:33 GMT

@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

🙂