Getting Data In

How would you create a table from json array with a nested a array in each object

topherbirth
Explorer

I have tried quite a few different ways to capture data within a json object and return it as separate events, but my break-down always occurs when attempting to access the data in the nested arrays.

{
    "Items": [
        {
            "CN": "AccountName",
            "CV": "AccountOne",
            "Props": [
                {
                    "PN": "PropOne",
                    "PV": "5"
                },
                {
                    "PN": "PropTwo",
                    "PV": "3"
                }
            ]
        },
        {
            "CN": "AccountName",
            "CV": "AccountOne",
            "Props": [
                {
                    "PN": "PropOne",
                    "PV": "5"
                }
            ]
        },
        {
            "CN": "AccountName",
            "CV": "AccountTwo",
            "Props": [
                {
                    "PN": "PropOne",
                    "PV": "5"
                },
                {
                    "PN": "PropThree",
                    "PV": "8"
                }
            ]
        },
        {
            "CN": "PersonName",
            "CV": "Bob",
            "Props": [
                {
                    "PN": "PropOne",
                    "PV": "5"
                },
                {
                    "PN": "PropThree",
                    "PV": "8"
                }
            ]
        },
        {
            "CN": "PersonName",
            "CV": "Bob",
            "Props": [
                {
                    "PN": "PropThree",
                    "PV": "8"
                }
            ]
        }
    ]
}

Prepared version for testing:

{ \"Items\": [ { \"CN\": \"AccountName\", \"CV\": \"AccountOne\", \"Props\": [ { \"PN\": \"PropOne\", \"PV\": \"5\" }, { \"PN\": \"PropTwo\", \"PV\": \"3\" } ] }, { \"CN\": \"AccountName\", \"CV\": \"AccountOne\", \"Props\": [ { \"PN\": \"PropOne\", \"PV\": \"5\" } ] }, { \"CN\": \"AccountName\", \"CV\": \"AccountTwo\", \"Props\": [ { \"PN\": \"PropOne\", \"PV\": \"5\" }, { \"PN\": \"PropThree\", \"PV\": \"8\" } ] }, { \"CN\": \"PersonName\", \"CV\": \"Bob\", \"Props\": [ { \"PN\": \"PropOne\", \"PV\": \"5\" }, { \"PN\": \"PropThree\", \"PV\": \"8\" } ] }, { \"CN\": \"PersonName\", \"CV\": \"Bob\", \"Props\": [ { \"PN\": \"PropThree\", \"PV\": \"8\" } ] } ] }

I would want this to result in something like:
alt text

Thanks!

0 Karma
1 Solution

to4kawa
Ultra Champion
| makeresults
| eval _raw="{ \"Items\": [ { \"CN\": \"AccountName\", \"CV\": \"AccountOne\", \"Props\": [ { \"PN\": \"PropOne\", \"PV\": \"5\" }, { \"PN\": \"PropTwo\", \"PV\": \"3\" } ] }, { \"CN\": \"AccountName\", \"CV\": \"AccountOne\", \"Props\": [ { \"PN\": \"PropOne\", \"PV\": \"5\" } ] }, { \"CN\": \"AccountName\", \"CV\": \"AccountTwo\", \"Props\": [ { \"PN\": \"PropOne\", \"PV\": \"5\" }, { \"PN\": \"PropThree\", \"PV\": \"8\" } ] }, { \"CN\": \"PersonName\", \"CV\": \"Bob\", \"Props\": [ { \"PN\": \"PropOne\", \"PV\": \"5\" }, { \"PN\": \"PropThree\", \"PV\": \"8\" } ] }, { \"CN\": \"PersonName\", \"CV\": \"Bob\", \"Props\": [ { \"PN\": \"PropThree\", \"PV\": \"8\" } ] } ] }"
| spath path=Items{} output=Items
| stats count by Items
| spath input=Items path=Props{} output=Props
| mvexpand Props
| spath input=Props
| spath input=Items
| fields - Items count Props*
| eval names=CN."_".CV
| stats sum(PV) as PV by names PN
| xyseries names PN PV
| rex field=names "AccountName_(?<AccountName>\w+)|PersonName_(?<PersonName>\w+)"
| table PersonName AccountName PropOne PropTwo PropThree

View solution in original post

to4kawa
Ultra Champion
| makeresults
| eval _raw="{ \"Items\": [ { \"CN\": \"AccountName\", \"CV\": \"AccountOne\", \"Props\": [ { \"PN\": \"PropOne\", \"PV\": \"5\" }, { \"PN\": \"PropTwo\", \"PV\": \"3\" } ] }, { \"CN\": \"AccountName\", \"CV\": \"AccountOne\", \"Props\": [ { \"PN\": \"PropOne\", \"PV\": \"5\" } ] }, { \"CN\": \"AccountName\", \"CV\": \"AccountTwo\", \"Props\": [ { \"PN\": \"PropOne\", \"PV\": \"5\" }, { \"PN\": \"PropThree\", \"PV\": \"8\" } ] }, { \"CN\": \"PersonName\", \"CV\": \"Bob\", \"Props\": [ { \"PN\": \"PropOne\", \"PV\": \"5\" }, { \"PN\": \"PropThree\", \"PV\": \"8\" } ] }, { \"CN\": \"PersonName\", \"CV\": \"Bob\", \"Props\": [ { \"PN\": \"PropThree\", \"PV\": \"8\" } ] } ] }"
| spath path=Items{} output=Items
| stats count by Items
| spath input=Items path=Props{} output=Props
| mvexpand Props
| spath input=Props
| spath input=Items
| fields - Items count Props*
| eval names=CN."_".CV
| stats sum(PV) as PV by names PN
| xyseries names PN PV
| rex field=names "AccountName_(?<AccountName>\w+)|PersonName_(?<PersonName>\w+)"
| table PersonName AccountName PropOne PropTwo PropThree

topherbirth
Explorer

That works. Thanks!

Any chance we could get an explanation for how it works? I would like to learn from it, but it's hard for me to tell exactly whats happening.

0 Karma

to4kawa
Ultra Champion

Try line by line and check result.

spath session:

  • see reference: spath
  • first stats count by is to divide 5 Items
  • mvexpand is to divide Props

xyseries session:

  • see reference: xyseries
  • xyseries can have many args. but it is hard to understand and handle over 4 args.
  • so, let' make only three fields(args). | eval names=CN."_".CV this is trick.
  • aggregate by | stats sum(PV) as PV by names PN
  • rex is to extract fields by REGEX.

Is this enough? happy Splunking.

0 Karma

topherbirth
Explorer

Good deal! Thanks for the info and help.

0 Karma
Got questions? Get answers!

Join the Splunk Community Slack to learn, troubleshoot, and make connections with fellow Splunk practitioners in real time!

Meet up IRL or virtually!

Join Splunk User Groups to connect and learn in-person by region or remotely by topic or industry.

Get Updates on the Splunk Community!

Accelerate Splunk App Development with AI

  Learn how to automate the repetitive parts of your build cycle.   Watch On-Demand   Replay the Accelerate ...

Splunk Agent Observability: Measuring AI Value with Splunk Tokenomics on AWS

The AI landscape is moving faster than ever, and our latest 2026 State of Observability report provides the ...

Enterprise Security Watch Party: What’s New in 8.8 - Recap

Splunk Community HQ hosted a Splunk Enterprise Security Watch Party: What’s New In ES 8.8 last Thursday. I had ...