topic How to extract field by different field values in nested JSON? in Splunk Search

How to extract field by different field values in nested JSON?

letmein — Wed, 29 Mar 2023 16:00:49 GMT

I have some JSON (raw event) like below, this is one event:

{

"place": "bar",

"stock": [

{

"brand": keith

"type": drink

"owner": Tom

}

{

"brand": qfarm

"type": food

"owner": Mike

}

{

"brand": blue

"type": drink

"owner": Jerry

}

{

"brand": redriver

"type": food

"owner": Don

}

]

}

System already extracted field “place”, “brand”, “type”, “owner”. What I would like is to extract “brand” into new field “brand_drink” or “brand_food” depends on “type” is drink or food. And do the same for “owner”. In this example, there’s 4 items under “stock”, there’s other events have more or less which might have to use loop.

Been struggling with this. Can someone help please?

Re: How to extract field by different field values in nested JSON

gcusello — Wed, 29 Mar 2023 14:40:19 GMT

Hi @letmein,

did you trued to use the spath command (https://docs.splunk.com/Documentation/Splunk/9.0.4/SearchReference/Spath), it extract all fields with the correct grouping.

Ciao.

Giuseppe

Re: How to extract field by different field values in nested JSON

letmein — Wed, 29 Mar 2023 14:50:01 GMT

@gcusello thanks for you reply. spath will group all values into one field, such as "stock{}.brank" field contain "keith", "qfarm", "blue", "redriver". This has been done by system automatically due to JSON source type. What I need is if stock{}.type=drink, put stock{}.brand value into new field "brand_drink"; if stock{}.type=food, put stock{}.brand value into new field "brand_food".

Re: How to extract field by different field values in nested JSON

gcusello — Wed, 29 Mar 2023 14:54:08 GMT

Hi @letmein,

I'd use spath and some calculated field (using eval) like the following:

<your_search> | spath | eval brand_drink=if(stock{}.type="drink",stock{}.brand,""), brand_food=if(stock{}.type="drink",stock{}.brand,"") | ...

Ciao.

Giuseppe

Re: How to extract field by different field values in nested JSON

letmein — Wed, 29 Mar 2023 15:05:21 GMT

This returns nothing, as stock{}.type has multivalve "drink" and "food". stock{}.type doesn't equal drink or food.

Re: How to extract field by different field values in nested JSON?

yuanliu — Wed, 29 Mar 2023 18:24:37 GMT

When you anonymize data, please make sure the data format is intact. Specifically, the illustrated data is not conformant to JSON. This makes it very difficult for volunteers to help. If original data is conformant, it should look like this instead:

{

    "place": "bar",

    "stock": [

                     {

                       "brand": "keith",

                       "type": "drink",

                       "owner": "Tom"

                      },

                      {

                       "brand": "qfarm",

                       "type": "food",

                       "owner": "Mike"

                      },

                     {

                       "brand": "blue",

                       "type": "drink",

                       "owner": "Jerry"

                      },

                      {

                       "brand": "redriver",

                       "type": "food",

                       "owner": "Don"

                      }

                   ]

}

With this out of the way, you can use path option in spath. You said that the system already flattened JSON nodes. But what you need is in the vector (array) node of stock{}. So, extract this node into its own field, then use mvexpand to make the field single-valued, then extract from this field.

| spath path=stock{} | mvexpand stock{} | spath input=stock{}

After this, your sample data gives

brand	owner	type
keith	Tom	drink
qfarm	Mike	food
blue	Jerry	drink
redriver	Don	food

This is the most semantic to your dataset if not to your described output, and to me, the most meaningful way to meet your requirement.

Re: How to extract field by different field values in nested JSON?

letmein — Fri, 31 Mar 2023 21:49:35 GMT

Sorry for the data format issue. I don't have proper JSON editor install on my computer.

So without any changes, if I search "index=test | table stock{}.*", I got this:

If I try the search you suggested, I got this. What should I do next to extract brand and owner by type?

Re: How to extract field by different field values in nested JSON?

yuanliu — Sat, 01 Apr 2023 07:08:18 GMT

Note my search does not contain a table command that restricts output to stock{} and stock{}.* as your screenshot shows. If you must use table at this time, use this instead:

| table place brand owner type

Alternatively, you can examine all available fields by using | table *. (Usually you only want to use table when you are displaying your final results.)

Another suggestion: Show the commands you tried in text. Screenshot is good at illustrating some effects, but bad at conveying code change.