Getting Data In

How to compute size of array field in JSON using spath

relango
Explorer

I want to calculate the raw size of an array field in JSON. len() command works fine to calculate size of JSON object field, but len() command doesn't work for array field. Here is the query e.g:

| makeresults | eval raw="{\"arrayField\":[{\"a\":\"b\"}, {\"c\":\"d\"}], \"objField\":{\"e\":\"f\"}}" | spath input=raw path=objField | eval objFieldSize = len(objField)| spath input=raw path=arrayField{} output=arrayField | eval arraySize=len(arrayField) | table objFieldSize, objField, arraySize,  arrayField

It produces output:

alt text

It shows the correct value for objFieldSize, but arrayFieldSize is empty. Am I missing something..? How can I compute arrayFieldSize corectly?

Tags (4)
0 Karma
1 Solution

relango
Explorer

Using mvjoin did the trick. Here is the working query for anyone looking for answer.

| makeresults | eval raw="{\"arrayField\":[{\"a\":\"b\"}, {\"c\":\"d\"}], \"objField\":{\"e\":\"f\"}}" | spath input=raw path=objField | eval objFieldSize = len(objField)| spath input=raw path=arrayField{} output=arrayField | eval arraySize=len(mvjoin(arrayField,"")) | table objFieldSize, objField, arraySize,  arrayField

View solution in original post

relango
Explorer

Using mvjoin did the trick. Here is the working query for anyone looking for answer.

| makeresults | eval raw="{\"arrayField\":[{\"a\":\"b\"}, {\"c\":\"d\"}], \"objField\":{\"e\":\"f\"}}" | spath input=raw path=objField | eval objFieldSize = len(objField)| spath input=raw path=arrayField{} output=arrayField | eval arraySize=len(mvjoin(arrayField,"")) | table objFieldSize, objField, arraySize,  arrayField

niketn
Legend

@relango if you want to count the multi-valued field values and keep the field as multi-valued, you should use mvcount() instead of mvjoin which makes it is single value field.

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

Sukisen1981
Champion

@relango - Glad to see you figured this out yourself, i knew a few permutations of the different mv functions will sort this out, upvoted your comment and please accept your own answer as it resolved the issue.
Many apologies , I was stuck with office work and could not get back to you earlier on this, but then again I did not need to since you figured it out yourself 🙂 🙂

0 Karma

relango
Explorer

Sure, Thanks for your hint which helped me figure it out.

0 Karma

niketn
Legend

@relango try the mvcount() evaluation function.

| eval arraySize=mvcount(arrayField)
____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

Sukisen1981
Champion

That is because the arrayField in multivalued , try this:
| makeresults | eval raw="{\"arrayField\":[{\"a\":\"b\"}, {\"c\":\"d\"}], \"objField\":{\"e\":\"f\"}}" | spath input=raw path=objField | eval objFieldSize = len(objField)| spath input=raw path=arrayField{} output=arrayField | table objFieldSize, objField, arraySize, arrayField
| mvexpand arrayField
| eval arraySize=len(arrayField)
| mvcombine arrayField

0 Karma

relango
Explorer

Thanks, Sukisen. The query works but now computed arraySize value is for the only first element in the array and not for the complete array.

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...