Getting Data In

How to do bucketing on json data?

ksrujana
New Member

I have a json data similar to the example given below

{
"name":"srini",
"date":"20160801",
"distribution": { "20":1, "10":2, "10":1, "15":2, 
                            "10":3, "15":4, "20":3, "30":4 }
}
{
"name":"srini2",
"date":"20160802",
"distribution": { "3":1, "1":2, "4":1, "1":2, "1":1
                            "3":3, "1":4, "4":3, "1":4 }
}

I want to do bucketing based on the keys in the distribution field. Buckets should be like (1,2) and (3,4) and sum up all the values in each bucket. Expected result is like following:

date        bucket1    bucket2
20160801      55         75
20160802      10         10 
Tags (2)
0 Karma

javiergn
SplunkTrust
SplunkTrust

Hi,

Assuming your field is named json and also assuming the only buckets there are the ones you mentioned (bucket1 = 1,2 and bucket2 = 3,4), give the following a go and see if that helps:

your base search
| spath input=json path=name
| spath input=json path=date
| spath input=json path=distribution
| rex field=distribution max_match=0 "\"(?<bucket1>\d+)\":[12]"
| rex field=distribution max_match=0 "\"(?<bucket2>\d+)\":[34]"
| stats sum(*) as * by date, name

Example:

| makeresults | fields - _time
| eval json = "
{
\"name\":\"srini\",
\"date\":\"20160801\",
\"distribution\": { \"20\":1, \"10\":2, \"10\":1, \"15\":2, 
                            \"10\":3, \"15\":4, \"20\":3, \"30\":4 }
};
{
\"name\":\"srini2\",
\"date\":\"20160802\",
\"distribution\": { \"3\":1, \"1\":2, \"4\":1, \"1\":2, \"1\":1,
                            \"3\":3, \"1\":4, \"4\":3, \"1\":4 }
}
"
| eval json = split(json, ";") 
| mvexpand json
| spath input=json path=name
| spath input=json path=date
| spath input=json path=distribution
| rex field=distribution max_match=0 "\"(?<bucket1>\d+)\":[12]"
| rex field=distribution max_match=0 "\"(?<bucket2>\d+)\":[34]"
| stats sum(*) as * by date, name

Output:

alt text

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...