Extract the fields from JSON in search time and write a search

[vasanthmss]

_raw = {"studentsmarks":{"subject":"science","university":"university1","examdate":"10-12-14"},"students":[{"college":"college1","studentname":"name1","mark":"98"},{"college":"college1","studentname":"name2","mark":"99"},{"college":"college2","studentname":"name21","mark":"80"},{"college":"college2","studentname":"name22","mark":"100"}]}

JSON Format

{
  "studentsmarks": {
    "subject": "science",
    "university": "university1",
    "examdate": "10-12-14"
  },
  "students": [
    {
      "college": "college1",
      "studentname": "name1",
      "mark": "98"
    },
    {
      "college": "college1",
      "studentname": "name2",
      "mark": "99"
    },
    {
      "college": "college2",
      "studentname": "name21",
      "mark": "80"
    },
    {
      "college": "college2",
      "studentname": "name22",
      "mark": "100"
    }
  ]
}

Expected results,

college studentname mark subject university examdate
college1    name1   98  science university1 10-12-14
college1    name2   99  science university1 10-12-14
college2    name21  80  science university1 10-12-14
college2    name22  100 science university1 10-12-14

[somesoni2 · Accepted solution]

Here you go (runanywhere sample)

| gentimes start=-1 | eval temp="{\"studentsmarks\":{\"subject\":\"science\",\"university\":\"university1\",\"examdate\":\"10-12-14\"},\"students\":[{\"college\":\"college1\",\"studentname\":\"name1\",\"mark\":\"98\"},{\"college\":\"college1\",\"studentname\":\"name2\",\"mark\":\"99\"},{\"college\":\"college2\",\"studentname\":\"name21\",\"mark\":\"80\"},{\"college\":\"college2\",\"studentname\":\"name22\",\"mark\":\"100\"}]}" | table temp | rename temp as _raw | spath | rename students{}.* as * | eval temp=mvzip(college,mvzip(mark,studentname,"#"),"#") | mvexpand temp | rex field=temp "(?<college>.*)#(?<mark>.*)#(?<studentname>.*)" | fields - temp,_raw