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

[topherbirth]

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:

Thanks!

[to4kawa · Accepted solution]

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

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.

[to4kawa]

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.

[topherbirth]

Good deal! Thanks for the info and help.