Nested stats and json keys grouping

[mkiran18]

Hi ,
I have a json structure like this : 

{
  "zip": 67452,
  "location": "NY",
  "author": {
    "book1": {
      "price": 12
    },
    "book2": {
      "price": 11
    },
    "book3": {
      "price": 124
    },
    "book4": {
      "price": 122
    }
  }
}

I am trying to group and stats it for nested structure to get the avg price based on the `book*` as key 

was able to get the counts accurately using 

|  spath output=bp path=author.book1.price | stats avg(bp) as avgPrice by location zip | stats list(zip) list(avgPrice) by location

Now the next level grouping is by book* so that the query need not be run multiple times with different book* keys. i tried the rex but the field name is still constant 😞 . Any insights will be really helpful.

Also is there a way to get the table lines in the stats result where it is not grouped for better readability? 

Update: Desired result

BookName	location	zip	avg(price)
Book1	Newyork	64673	3433
		53421	8678
	NewJersey	35362	4435
		34235	2425
Book2	Newyork	64673	3433
		53421	8678
	NewJersey	35362	4435
		42352	2425
	Arizona	25252	2525

I am able to get the table till location using nested stats, but the `book1` being a "key" rather than a value extraction is difficult

[kamlesh_vaghela]

@mkiran18 

Can you please try this?

| makeresults 
| eval event="{\"zip\": 67452,\"location\": \"NY\",\"author\": {\"book1\": {\"price\": 12},\"book2\": {\"price\": 11},\"book3\": {\"price\": 124},\"book4\": {\"price\": 122}}}|{\"zip\": 67453,\"location\": \"NY\",\"author\": {\"book1\": {\"price\": 12},\"book2\": {\"price\": 11},\"book3\": {\"price\": 124},\"book4\": {\"price\": 122}}}|{\"zip\": 67454,\"location\": \"CA\",\"author\": {\"book1\": {\"price\": 12},\"book3\": {\"price\": 124},\"book5\": {\"price\": 124}}}" 
| eval event=split(event,"|") 
| mvexpand event 
| rename event as _raw 
| kv 
| eval BookName_val="" 
| foreach author.*.price 
    [ eval BookName_val= BookName_val+","+if(isnull('<<FIELD>>'),"","<<MATCHSTR>>|"+'<<FIELD>>'+"|"+zip+"|"+location)] 
| fields BookName_val 
| makemv delim="," BookName_val 
| mvexpand BookName_val | eval BookName = mvindex(split(BookName_val,"|"),0), price= mvindex(split(BookName_val,"|"),1), zip= mvindex(split(BookName_val,"|"),2), location= mvindex(split(BookName_val,"|"),3)
| stats avg(price) as avg_price by BookName location zip
| streamstats window=2 first(BookName) as pre_BookName count first(location) as pre_location
| table count BookName pre_BookName location zip avg_price pre_location
| eval Book= case(count==1,BookName,count>1 and BookName!=pre_BookName,BookName,1=1,"")
| eval Loc= case(Book!="" OR count==1,location,count>1 and location!=pre_location,location,1=1,"")
| table Book Loc  zip avg_price

[manjunathmeti]

hi @mkiran18 ,

Using below query book and price values for each location and zip are extracted.

| rex "\"zip\": (?<zip>\w+)" 
| rex "\"location\": \"(?<location>\w+)\"" 
| rex max_match=0 "\"(?<book>\w+)\":\s{\s*\"price\":\s(?<price>\d+)" 
| eval bookprice=mvzip(book, price) 
| mvexpand bookprice 
| eval bookprice=split(bookprice, ","), book=mvindex(bookprice, 0), price=mvindex(bookprice, 1)
| table location, zip, book, price

Add below query to the above to get average price of all books per location and zip.

| stats avg(price) as avgPrice by location, zip

If this reply helps you, an upvote/like would be appreciated.

[ITWhisperer]

I am not sure if this is what you are after, but ...

| makeresults | eval _raw="{
  \"zip\": 67452,
  \"location\": \"NY\",
  \"author\": {
    \"book1\": {
      \"price\": 12
    },
    \"book2\": {
      \"price\": 11
    },
    \"book3\": {
      \"price\": 124
    },
    \"book4\": {
      \"price\": 122
    }
  }
}|{
  \"zip\": 12345,
  \"location\": \"ZZ\",
  \"author\": {
    \"book4\": {
      \"price\": 12
    },
    \"book5\": {
      \"price\": 11
    },
    \"book6\": {
      \"price\": 124
    },
    \"book7\": {
      \"price\": 122
    }
  }
}"
| eval event=split(_raw,"|")
| mvexpand event
| rename event as _raw
| spath
| fields - _*
| transpose 0
| eval column=if(like(column,"author%"),mvindex(split(column,"."),1),column)
| transpose 0 header_field=column
| fields - column
| foreach book*
[ eval bookprice=mvappend(bookprice,"<<FIELD>>"."|".<<FIELD>>) ]
| fields location zip bookprice
| mvexpand bookprice
| eval bookprice=split(bookprice,"|")
| eval book=mvindex(bookprice,0)
| eval price=mvindex(bookprice,1)
| fields - bookprice
| stats avg(price) by book, location, zip

[mkiran18]

This is the end result that i am looking for , am going to try the two suggestions listed above.  

BookName	location	zip	avg(price)
Book1	Newyork	64673	3433
		53421	8678
	NewJersey	35362	4435
		34235	2425
Book2	Newyork	64673	3433
		53421	8678
	NewJersey	35362	4435
		42352	2425
	Arizona	25252	2525