How to transform nested json into separate rows?

[nvwls]

Given json with hashes

| makeresults 
| eval _raw="{\"yes\":true,\"no\":false,\"a\":{\"x\":0,\"y\":0,\"z\":0},\"c\":{\"x\":1,\"y\":2,\"z\":3},\"d\":{\"x\":1,\"y\":4,\"z\":9}}"
| spath

"a", "c", and "d" are nested hashes. There are other fields, "yes" and "no" that are not hashes.

What I am trying to do filter out non-hashes and then split into multiple row.

Name	x	y	z
a	0	0	0
c	1	2	3
d	1	4	9

The tricky part is that the top level field names, "yes", "no", "a", "c", "d" are not constant. However the sub fields "x", "y", "z" are.

Thoughts?

[bowesmana]

First pass...

This works

| makeresults 
| eval _raw="{\"yes\":true,\"no\":false,\"a\":{\"x\":0,\"y\":0,\"z\":0},\"c\":{\"x\":1,\"y\":2,\"z\":3},\"d\":{\"x\":1,\"y\":4,\"z\":9}}"
| spath
| fields - _time _raw
| fields *.*
| foreach *.* [ eval name=mvappend("<<MATCHSEG1>>", name), name=mvdedup(name) ]
| mvexpand name
| foreach *.* [ | eval <<MATCHSEG2>>=if("<<MATCHSEG1>>"=name, '<<FIELD>>', '<<MATCHSEG2>>') ]
| fields - *.*

Bit clunky, there may be a better way...

[bowesmana]

fields *.* only leaves the n.n fields so removes top level fields.

the first foreach collects the unique top level names and then expands them to create the rows

Second foreach collects the x, y, z values