topic How to use foreach to extract value from another json object? in Splunk Search

How to use foreach to extract value from another json object?

GaryZ — Tue, 02 May 2023 21:25:20 GMT

I am trying to get the values from one json object using the keys from another json array.

1) How do I capture the limits into the new_data_limits array?

2) If there's multiple events similar to 'limits', how do I get the average of similar process? (i.e "process1", "process2")

TIA....

Re: How to use foreach to extract value from another json object?

abi2023 — Tue, 02 May 2023 22:25:04 GMT

| makeresults
| eval limits=json_object("process1", json_array(123), "process2", json_array(234), "process3", json_array(0.12)), total=0
| eval processes = json_array("process1", "process2")
| eval new_data_limits = json_object()
| foreach processes
[ | eval key = tostring(<<FIELD>>), value = json_extract(limits, key), new_data_limits = if(isnull(new_data_limits), json_object(key, value), json_set(new_data_limits, key, value)) ]

get average

base search | stats avg(*) as * by process1, process2

Re: How to use foreach to extract value from another json object?

GaryZ — Tue, 02 May 2023 22:59:59 GMT

@abi2023

I understand your code, and I believe that it should work. however when I run the search, I get the following.

I don't see new_data_limits capturing the limit values. I've tried rerunning the search, and have also stepped through it without the foreach loop. I do see the results. However when it's used in the foreach loop, the limits values aren't in the new_data_limits variable.

Re: How to use foreach to extract value from another json object?

yuanliu — Wed, 03 May 2023 06:35:53 GMT

By default, foreach uses multifield mode. What you need is its json_array mode. (If you have Splunk 9, that is. Before Splunk 9, foreach only has multifield mode.)

| makeresults | eval limits=json_object("process1", json_array(123), "process2", json_array(234), "process3", json_array(0.12)), total=0 | eval processes = json_array("process1", "process2") | eval new_data_limits = json_object() | foreach processes mode=json_array [ | eval new_data_limits = json_set(new_data_limits, <<ITEM>>, json_extract(limits, <<ITEM>>))]

To get average, on the other hand, I would lose that JSON array for processes because you need separate columns.

| makeresults | eval limits=mvappend(json_object("process1", json_array(123), "process2", json_array(234), "process3", json_array(0.12)), json_object("process1", json_array(345), "process2", json_array(678), "process3", json_array(0.12))), total=0 | mvexpand limits ```data emulation above``` | foreach process1 process2 [ eval new_<<FIELD>>_limit = json_array_to_mv(json_extract(limits, "<<FIELD>>"))] | stats sum(total) as total avg(*) as *