Extract data from JSON array

[karlbosanquet]

I have some data which is along the following format;

{"event":
     {
     "Timestamp":"2019-01-16 22:20:26.123"
     "Stream":"123456",
     "Id":"789",
     "Data": [
         {
        "name":"0",
            "rank":"1",
            "score":"34.56787"
         },
         {
        "name":"1",
            "rank":"3",
            "score":"4.74287"
         },
         {
        "name":"2",
            "rank":"2",
            "score":"12.54863"
         },
         {
        "name":"3",
            "rank":"4",
            "score":"1.78916"
         },

     ]
     }
}

We get around 800,000 of these per day and have around 50 data elements in each one.

I am trying to find the best way to return the top 2 rank name and score for each event, e.g.;

1_name = 0
1_score = 34.56787
2_name = 2
2_score = 12.54863

And another search to timechart all scores by name

[mayurr98 · Accepted solution]

Hi Can you try this

| makeresults 
| eval _raw = "{\"event\":
      {
      \"Timestamp\":\"2019-01-16 22:20:26.123\",
      \"Stream\":\"123456\",
      \"Id\":\"789\",
      \"Data\": [
          {
         \"name\":\"0\",
             \"rank\":\"1\",
             \"score\":\"34.56787\"
                  },
          {
         \"name\":\"1\",
             \"rank\":\"3\",
             \"score\":\"4.74287\"
                  },
          {
         \"name\":\"2\",
             \"rank\":\"2\",
             \"score\":\"12.54863\"
                  },
          {
         \"name\":\"3\",
             \"rank\":\"4\",
             \"score\":\"1.78916\"
                  },

      ]
      }
 }" 
| spath 
| rename event.Data{}.* as * event.Timestamp as Timestamp event.Id as Id event.Stream as Stream 
| eval data=mvzip(mvzip(name,rank),score) 
| mvexpand data 
| rex field=data "(?<name>[^\,]+)\,(?<rank>[^\,]+)\,(?<score>.*)" 
| table Timestamp Stream Id name rank score

Also try this for top 2 scores:

|stats values(score) as score by rank name 
|  sort 2 -score

let me know if this helps!

[karlbosanquet]

Expected output examples;

• Search 1 (table timestamp Stream Id 1_name 1_value 2_name 2_value)

||Timestamp||Stream||Id||1_name||1_score||2_name||2_score||
|2019-01-16 22:20:26.123|123456|789|0|34.56787|2|12.54863|
|2019-01-16 22:21:28.567|123456|790|12|54.78797|46|34.82632|

• Search 2 (timechart values(score) by name)

||Timestamp||0||1||2||3||4||5||
|2019-01-16 22:20:26.123|34.56787|4.74287|12.54863|1.78916|3.35732|
|2019-01-15 12:21:34.244|2.34689|12.45211|21.24852|14.72412|2.28342|