topic Re: How do I write a search query for a hierachial JSON? in Getting Data In

How do I write a search query for a hierachial JSON?

kjubie — Fri, 10 Aug 2018 11:34:25 GMT

(I have no experience in Splunk searches)
The question is simple:

I have a JSON like this:

{
    "name": "Testdata",
    "children": [
        {
            "name": "A",
            "children": [
            {
                "name": "A1",   
                "value": 436
            },
            {
                "name": "A2",
                "value": 546
            },
            {
                "name": "A3",
                "value": 223
            },
            {
                "name": "A4",
                "value": 132
            },
            {
                "name": "A5",
                "value": 115
            },
            {
                "name": "A6",
                "value": 96
            }]
        },
        {
            "name": "B",
            "children": [
            {
                "name": "B1",
                "value": 453
            },
            {
                "name": "B2",
                "value": 344
            },
            {
                "name": "B3",
                "value": 35
            },
            {
                "name": "B4",
                "value": 65
            },
            {
                "name": "B5",
                "value": 789
            },
            {
                "name": "B6",
                "value": 648
            },
            {
                "name": "B7",
                "value": 147
            }]
        }
      ]
}

and I want a table like this:

parent    |name   |value
A         |A1     | 436
A         |A2     | 546
...       |..     |..
B         |B1     |443

(Sorry for the bad drawn table)

Thanks for the answers!

Re: How do I write a search query for a hierachial JSON?

renjith_nair — Fri, 10 Aug 2018 13:52:01 GMT

Use spath to parse the json. Here is an example

| makeresults |eval json="{
     \"name\": \"Testdata\",
     \"children\": [
         {
             \"name\": \"A\",
             \"children\": [
             {
                 \"name\": \"A1\",    
                 \"value\": 436
             },
             {
                 \"name\": \"A2\",
                 \"value\": 546
             },
             {
                 \"name\": \"A3\",
                 \"value\": 223
             },
             {
                 \"name\": \"A4\",
                 \"value\": 132
             },
             {
                 \"name\": \"A5\",
                 \"value\": 115
             },
             {
                 \"name\": \"A6\",
                 \"value\": 96
             }]
         },
         {
             \"name\": \"B\",
             \"children\": [
             {
                 \"name\": \"B1\",
                 \"value\": 453
             },
             {
                 \"name\": \"B2\",
                 \"value\": 344
             },
             {
                 \"name\": \"B3\",
                 \"value\": 35
             },
             {
                 \"name\": \"B4\",
                 \"value\": 65
             },
             {
                 \"name\": \"B5\",
                 \"value\": 789
             },
             {
                 \"name\": \"B6\",
                 \"value\": 648
             },
             {
                 \"name\": \"B7\",
                 \"value\": 147
             }]
         }
       ]
 }"
 |spath input=json|table children{}.name,children{}.children{}.name,children{}.children{}.value
 |rename children{}.children{}.name as grand_child_name,children{}.name as child_name,children{}.children{}.value as grand_child_value
 |eval zipped=mvzip(grand_child_name,grand_child_value)|table child_name,zipped|mvexpand zipped
 |mvexpand child_name|eval x=split(zipped,",")|eval grand_child_name=mvindex(x,0),grand_child_value=mvindex(x,1)
 |table child_name,grand_child_name,grand_child_value|eval match=substr(grand_child_name,0,1)|where child_name==match|fields - match

Re: How do I write a search query for a hierachial JSON?

DalJeanis — Sat, 11 Aug 2018 05:36:41 GMT

Converted this to an answer because it answers the question with an excellent run-anywhere example.

Re: How do I write a search query for a hierachial JSON?

renjith_nair — Sat, 11 Aug 2018 05:46:30 GMT

Thanks @DalJeanis !

Re: How do I write a search query for a hierachial JSON?

kjubie — Mon, 13 Aug 2018 07:08:57 GMT

Thanks for your help! Works perfectly!