topic Re: How to parse my json in Splunk Search

How to parse my json

jerrytao — Thu, 18 Jul 2024 08:54:21 GMT

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
...	...

Re: How to parse my json

ITWhisperer — Thu, 18 Jul 2024 08:58:57 GMT

| 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)))

Re: How to parse my json

jerrytao — Thu, 18 Jul 2024 09:44:45 GMT

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

Re: How to parse my json

ITWhisperer — Thu, 18 Jul 2024 10:07:07 GMT

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.

Re: How to parse my json

jerrytao — Thu, 18 Jul 2024 10:27:05 GMT

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

Re: How to parse my json

ITWhisperer — Thu, 18 Jul 2024 10:55:42 GMT

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)))

Re: How to parse my json

jerrytao — Thu, 18 Jul 2024 11:06:04 GMT

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

Re: How to parse my json

ITWhisperer — Thu, 18 Jul 2024 11:30:06 GMT

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?

Re: How to parse my json

yuanliu — Thu, 18 Jul 2024 18:56:45 GMT

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 ```

Re: How to parse my json

jerrytao — Fri, 19 Jul 2024 03:19:10 GMT

Thank you, I figure it out.