Grab json value with dynamic key from splunk

[darrfang]

Hi everyone,

I have a json data payload as below:

{
    location: US
    all_results: {
      serial_a: {
        result: PASS,
        version: 123,
        data:[
          data1,
          data2,
          data3
        ]
      },
      serial_b: {
        result: PASS,
        version: 456,
        data:[
          data4,
          data5
        ]
      },
      serial_c: {
        result: FAIL,
        version: 789,
        data:[
          data6,
          data7
        ]
      }
    }
  }

and I would like to use splunk query and make a table as:

serial_number	result	version	data
serial_a	PASS	123	data1, data2, data3
serial_b	PASS	456	data4, data5,
serial_c	fail	789	data6, data7

how to use splunk query to organize the result?
I know I'm able to grab the data by:
| spath path=all_results output=all_results
| eval all_results=json_extract(all_results)

The difficult part is at the serial_number. They have some common prefix serial, but it's dynamic. Therefore , when I try to grab the data inside serial_number, for example version, I'm not able to use query like:
| spath  output=version path=all_result.serial*.version

Could you give me some idea to do that? thank you!

[yuanliu]

First, thank you for using text to illustrate data, and clearly present desired result.  But next time make sure you preserve valid JSON syntax.  Your illustrated text is missing quotation marks required by JSON.  Correcting for syntax, I assume that the original data would look like

{
    "location": "US",
    "all_results": {
      "serial_a": {
        "result": "PASS",
        "version": "123",
        "data":[
          "data1",
          "data2",
          "data3"
        ]
      },
      "serial_b": {
        "result": "PASS",
        "version": "456",
        "data":[
          "data4",
          "data5"
        ]
      },
      "serial_c": {
        "result": "FAIL",
        "version": "789",
        "data":[
          "data6",
          "data7"
        ]
      }
    }
  }

This same ask has come several times recently, and there are several ways to do this.  This time, I'll try something new, and less roundabout in logic.

| fields location
| spath path=all_results
| fields - _*
| eval serial_number = json_array_to_mv(json_keys(all_results))
| mvexpand serial_number
| eval all_results = json_extract(all_results, serial_number)
| spath input=all_results
| fields - all_results
| rename data{} as data
| eval data = mvjoin(data, ",")

You data gives

location	data	result	serial_number	version
US	data1,data2,data3	PASS	serial_a	123
US	data4,data5	PASS	serial_b	456
US	data6,data7	FAIL	serial_c	789

This is an emulation for you to play around and compare with real data

| makeresults
| eval _raw = "{
    \"location\": \"US\",
    \"all_results\": {
      \"serial_a\": {
        \"result\": \"PASS\",
        \"version\": \"123\",
        \"data\":[
          \"data1\",
          \"data2\",
          \"data3\"
        ]
      },
      \"serial_b\": {
        \"result\": \"PASS\",
        \"version\": \"456\",
        \"data\":[
          \"data4\",
          \"data5\"
        ]
      },
      \"serial_c\": {
        \"result\": \"FAIL\",
        \"version\": \"789\",
        \"data\":[
          \"data6\",
          \"data7\"
        ]
      }
    }
  }"
| spath
``` data emulation above ```