topic Re: How to extract values from nested json? in Splunk Search

How to extract values from nested json?

arnabsen1234 — Fri, 19 Feb 2016 01:17:50 GMT

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

Re: How to extract values from nested json?

acharlieh — Fri, 19 Feb 2016 01:46:04 GMT

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

Re: How to extract values from nested json?

arnabsen1234 — Tue, 29 Sep 2020 08:52:16 GMT

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.

Re: How to extract values from nested json?

acharlieh — Fri, 19 Feb 2016 05:37:53 GMT

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.

Re: How to extract values from nested json?

fdi01 — Fri, 19 Feb 2016 10:05:37 GMT

try like:

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

Re: How to extract values from nested json?

chibulcu — Mon, 16 Nov 2020 09:17:32 GMT

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

MY_DETAILS{0}.FIELDNAME