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

[GaryZ]

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

| 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 new_data_limits = json_set(new_data_limits, <<FIELD>>, json_extract(limits, <<FIELD>>))]

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....

[yuanliu]

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 *

[GaryZ]

@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. 

[abi2023]

| 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