How to parse my json

[jerrytao]

With specific query, I can get below value for one field:

{

    "key1": {

        "field1": x

    },

    "key2": {

        "field2": xx

    },

    "key3": {

        "field3": xxx

    }

}

Every time, the string of key1,2,3 are different, and the string of field1,2,3 are also different, even the number of key is different for each query, it may eixst key4, key5...

Now I want to get below table, could someone help on this? Thanks.

Name A	Name B
key1	field1
key2	field2
key3	field3
...	...

[yuanliu]

An easier, and perhaps more semantic method is to use JSON functions introduced in 8.1 to restructure data. (As we have seen before, you have developers who overload JSON's key name to convey data, which is never a good thing.  If you have any influence on them, maybe ask them to change structure before it reaches data consumer.)

With the data you illustrated, Splunk would have given you fields like key1.field1, key2.field2.  Iterate over them using foreach.

| foreach *.*
    [eval temp = json_object(), temp = json_set(temp, "Name A", "<<MATCHSEG1>>", "Name B", "<<MATCHSEG2>>", "Value", '<<FIELD>>'),
    reformat = mvappend(reformat, temp)]
| mvexpand reformat
| spath input=reformat
| fields - _* key* temp

 Your example results in

Name A	Name B	Value	reformat
key1	field1	x	{"Name A":"key1","Name B":"field1","Value":"x"}
key2	field2	xx	{"Name A":"key2","Name B":"field2","Value":"xx"}
key3	field3	xxx	{"Name A":"key3","Name B":"field3","Value":"xxx"}

Here is an emulation you can play with and compare with real data

| makeresults
| eval _raw="{
    \"key1\": {
        \"field1\": \"x\"
    },
    \"key2\": {
        \"field2\": \"xx\"
    },
    \"key3\": {
        \"field3\": \"xxx\"
    }
}"
| spath
``` data emulation above ```

[ITWhisperer]

| eval Name_A=json_array_to_mv(json_keys(field))
| mvexpand Name_A
| eval Name_B=json_array_to_mv(json_keys(json_extract(field,Name_A)))

[jerrytao]

Thanks, I can get the Name_A, but Name_B is null...

[ITWhisperer]

OK the method works with some data as shown by this runanywhere example based on your sample data:

| makeresults format=json data="[{
    \"key1\": {
        \"field1\": \"x\"
    },
    \"key2\": {
        \"field2\": \"xx\"
    },
    \"key3\": {
        \"field3\": \"xxx\"
    }
}]"
| rename _raw as field
| table field
| eval Name_A=json_array_to_mv(json_keys(field))
| mvexpand Name_A
| eval Name_B=json_array_to_mv(json_keys(json_extract(field,Name_A)))

This would seem to suggest that it is something to do with your actual data, which you have hidden. Please provide some anonymised representative samples so we can progress the solution.

[jerrytao]

Below is the sample for the field "NotExportedParameters".

The hidden part are not fixed string, it can be different every time. 

What I want to get is as below:

hidden part	reason
part1	ValueOutOfRange
part2	ValueOutOfRange
...	..
part 6	SchemaValidation

[ITWhisperer]

Your field names possibly have dots in them? Try this way

| eval Name_B=json_array_to_mv(json_keys(json_extract_exact(field,Name_A)))

[jerrytao]

It still doesn't work.

Yes, It has dot, the pattern for Name_A is like below:

a.b.c:d-x.0.0

x is number 1/2/3

[ITWhisperer · Accepted solution]

Here is a runanywhere example showing it working

| makeresults format=json data="[{
    \"key 1\": {
        \"field1\": \"x\"
    },
    \"a.b.c:d-1.0.0\": {
        \"field2\": \"xx\"
    },
    \"key3\": {
        \"field3\": \"xxx\"
    }
}]"
| rename _raw as field
| table field
| eval Name_A=json_array_to_mv(json_keys(field))
| mvexpand Name_A
| eval Name_B=json_array_to_mv(json_keys(json_extract_exact(field,Name_A)))

What else can you tell us about the key names?

[jerrytao]

Thank you, I figure it out.