topic How to transform nested json into separate rows? in Splunk Search

How to transform nested json into separate rows?

nvwls — Wed, 04 May 2022 04:49:09 GMT

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?

Re: Transforming nested json into separate rows

bowesmana — Wed, 04 May 2022 03:05:03 GMT

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

Re: Transforming nested json into separate rows

bowesmana — Wed, 04 May 2022 03:06:57 GMT

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