How to parse a path request string in json format?

[EricMonkeyKing]

Hi all,

For this sort of json string, how can I extract KeyA, KeyB, KeyC? 

{ "KeyA": [ { "path": "/attibuteA", "op": "replace", "value": "hello" }, { "path": "/attibuteB", "op": "replace", "value": "hi" } ], "KeyB": [ { "path": "/attibuteA", "op": "replace", "value": "" }, { "path": "/attibuteC", "op": "replace", "value": "hey" }, { "path": "/attibuteD", "op": "replace", "value": "hello" } ], "KeyC": [ { "path": "/attibuteE", "op": "replace", "value": "" } ] }

My ideal output would look like：

Key	path	op	value
KeyA	attibuteA	replace	hello
KeyA	attibuteB	replace	hi
KeyB	attibuteA	replace
KeyB	attibuteC	replace	hey
KeyB	attibuteD	replace	hello
Keyc	attibuteE	replace

Many thanks^

[dtburrows3 · Accepted solution]

Think I was able to get the table in the output you are wanting. 

dtburrows3_0-1702480839496.png

I was able to achieve this using this SPL.

| makeresults
    | eval
        json_object="{ \"KeyA\": [ { \"path\": \"/attibuteA\", \"op\": \"replace\", \"value\": \"hello\" }, { \"path\": \"/attibuteB\", \"op\": \"replace\", \"value\": \"hi\" } ], \"KeyB\": [ { \"path\": \"/attibuteA\", \"op\": \"replace\", \"value\": \"\" }, { \"path\": \"/attibuteC\", \"op\": \"replace\", \"value\": \"hey\" }, { \"path\": \"/attibuteD\", \"op\": \"replace\", \"value\": \"hello\" } ], \"KeyC\": [ { \"path\": \"/attibuteE\", \"op\": \"replace\", \"value\": \"\" } ] }"
    | fields - _time
    | fromjson json_object
    | fields - json_object
    ``` Assuming that your fieldset at this point is only the list of fields needed for your final output ```
    ``` If there are other fields present that are not apart of the json object you are trying to parse then you should set up a naming convention for fields youy want to loop through ```
    | foreach *
        [
            | eval
                all_objects=mvappend(
                    'all_objects',
                    case(
                        isnull('<<FIELD>>'), null(),
                        mvcount('<<FIELD>>')==1, json_set('<<FIELD>>', "Key", "<<FIELD>>"),
                        mvcount('<<FIELD>>')>1, mvmap('<<FIELD>>', json_set('<<FIELD>>', "Key", "<<FIELD>>"))
                        )
                    )
            ]
    | fields + all_objects
    | mvexpand all_objects
    | spath input=all_objects
    | fields - all_objects
    | fields + Key, path, op, value

[EricMonkeyKing]

Much appreciated! It works!

[gcusello]

Hi @EricMonkeyKing ,

good for you, see next time!

Ciao and happy splunking

Giuseppe

P.S.: Karma Points are appreciated by all the contributors 😉

[gcusello]

Hi @EricMonkeyKing,

did you configured INDEXED_EXTRACTIONS = json for that sourcetype or used the spath command (https://docs.splunk.com/Documentation/Splunk/9.1.2/SearchReference/Spath) ?

Ciao.

Giuseppe

[EricMonkeyKing]

Thanks. I tried but the scenario is a little bit complex ^^