Splunk Search

How to extract values from nested json?

New Member

I have the below json for which I want to extract all the values of FIELDNAME.

"MY_DETAILS": [
        {
            "ACTION": "Add",
            "OLD_VALUES": "",
            "FIELDNAME": "ABCD",
            "TIME": "17-Feb-2016 1:08 PM",
            "NAME": "Jane Doe",
            "NEW_VALUEs": "",
            "NAME": "Jane1234"
        },
       {
            "ACTION": "Add",
            "OLD_VALUES": "",
            "FIELDNAME": "XYZ",
            "TIME": "17-Feb-2016 1:08 PM",
            "NAME": "Jane Doe 2",
            "NEW_VALUEs": "",
            "NAME": "Jane1234"
        },.................

I want to extract values FIELDNAME. In this case, the search should return ABCD and XYZ

0 Karma

Motivator

try like:

...| spath input=json|rename FIELDNAME as "your_FIELDNAME" |...|fields your_FIELDNAME
0 Karma

Influencer

If you have a syntactically correct and complete JSON object (your example is missing an opening {, closing ], and closing }). If you're sending the data in using a sourcetype with props.conf configuration that has INDEXED_EXTRACTIONS=json or AUTO_KV_JSON=true or KV_MODE=json (like the built-in sourcetypes like _json and json_no_timestamp ) then that field is automatically extracted as MY_DETAILS{}.FIELDNAME (indexed extractions -> index time extraction, the others are search time extractions).

Additionally / alternatively, if you're pulling the json object from a field, you can use the spath command or spath eval function to extract the same values. (these give you the added benefit, of say you want to extract only the first FIELDNAME value (e.g. "ABCD"), you could actually specify indices like | eval fieldname=spath(_raw,"MY_DETAILS{0}.FIELDNAME")

0 Karma

Loves-to-Learn

Hello @acharlieh , but why does the following not work(to get the first child of MY_DETAILS)?

 

MY_DETAILS{0}.FIELDNAME

 

 

0 Karma

New Member

I am not sending with props.conf configuration that has INDEXED_EXTRACTIONS=json or AUTO_KV_JSON=true or KV_MODE=json.

The json which i have posted, it part of the whole json which has different KVP as well.

I tried this | eval fieldname=spath(_raw,"MY_DETAILS{0}.FIELDNAME") but it din work.

0 Karma

Influencer

Didn't work how? If this is nested further then you need to include those levels when running spath. Without sharing the complete event I couldn't tell you exactly what that is, only make a best guess based on what you shared. Is the entire event json (in which case you'd use _raw) or just a part of it (in which case you'd extract the entire json object to a different field then run spath)? You need a complete json object (no extra characters, no missing characters), to leverage either form of spath effectively.

You could also potentially use rex, with max_match=0 to match multiple times but you'd have to know the limits of your data to build that regular expression.

0 Karma
State of Splunk Careers

Access the Splunk Careers Report to see real data that shows how Splunk mastery increases your value and job satisfaction.

Find out what your skills are worth!