How to deal with nested JSON containing variable numbers of objects

[tableau_merch]

I'm trying to work with the aws:description events to track changes to security groups.  The events are in a nested JSON format and there can be arbitrary numbers of to/from port combinations as well as the number of subnets for each to/from combination.  The JSON looks like:

"rules": [[{"from_port": "80", "ip_protocol": "tcp", "to_port": "80",  "grants": [{"name": null, "group_id": null, "owner_id": null, "cidr_ip": "10.0.0.0/24"},{"name": null, "group_id": null, "owner_id": null, "cidr_ip": "10.0.1.0/24"}], "groups": ""}]]

If I do:
| spath rules{}{}.from_port output=from_port  | eval from_port_count=mvcount(from_port) | eval from_port_count=from_port_count-1

That would put all of the from_port into a multivalued field, count the values in that field, and then subtract one so I have the range of index values in that multivalued field.

I would then need (I think) some kind of "foreach" command that would iterate through rule{}{0}, rule{}{1}, rule{}{2}... to apply the same logic for finding the index range as above to the rule{}{}.grants{}.cidr_ip field.

Can the foreach command be used in the subsearch of another foreach command?

I can use mvzip to stitch the data back together.  Ultimately, I'm looking for an output of something like:

from_port|to_port|ip_protocol|cidr_ip
80|80|tcp|10.0.0.0/24,10.0.1.0/24

I just don't know how to iterate through two levels of nested JSON where each level contains an arbitrary number of objects.

Somebody's going to earn their SplunkTrust badge on this one...

[to4kawa]

index=_internal | head 1 | fields _raw _time | eval _raw="{\"rules\": [[{\"from_port\": \"80\", \"ip_protocol\": \"tcp\", \"to_port\": \"80\",  \"grants\": [{\"name\": null, \"group_id\": null, \"owner_id\": null, \"cidr_ip\": \"10.0.0.0/24\"},{\"name\": null, \"group_id\": null, \"owner_id\": null, \"cidr_ip\": \"10.0.1.0/24\"}], \"groups\": \"\"}]]}"

| spath rules{}{}.grants{}.cidr_ip output=cidr_ip
| eval cidr_ip=mvjoin(cidr_ip,",")
| spath rules{}{} output=root
| spath input=root
| fields - grants* root groups
| table from_port to_port ip_protocol cidr_ip