topic Re: Create Table from json data in Getting Data In

Create Table from json data

chris — Thu, 14 Sep 2017 05:31:01 GMT

I have a json input that has the following format:
{
"body": {
"1": {
"dataId": 1,
"first": "Mihail",
"address": "Street b"
},
"3": {
"dataId": 3,
"first": "Mickey",
"address": "Street f"
},
"5": {
"dataId": 5,
"first": "Adam",
"address": "Street g"
},
"9": {
"dataId": 1,
"first": "John",
"address": "Street b"
}
}
}

I d like to create a Table from that data as follows (sorry for the ugly Table representation):
| dataID | first | address | |------------|------------|----------| | 1 | Mihail | street b | | 3 | Mickey | street f |
The field names are variable because of the json structure and contain the changing id

Is there a way to make multivalue fields and use mvexpand somehow.

Regards
Chris

Re: Create Table from json data

niketn — Thu, 14 Sep 2017 05:45:12 GMT

@chris can you explain what you mean by making multivalue fields. If you can add an example of the output you need that would be quite helpful for us to assist.

Re: Create Table from json data

chris — Thu, 14 Sep 2017 07:24:20 GMT

Hi, i just want to get a table with the following headers: dataId,first,address (I did not manage to render a proper table using markdown or html). I thought that I will probably have multivalu fields at some point either containing dataId,first and address or one for each column of the table. I do not know how to parse the json so i do not end up having individual fields extracted. If there is a way to avoid mv fields I'm happy with that solution.

Re: Create Table from json data

cmerriman — Thu, 14 Sep 2017 11:50:34 GMT

have you tried using |spath to format this data?
http://docs.splunk.com/Documentation/SplunkCloud/6.6.1/SearchReference/Spath

Re: Create Table from json data

niketn — Thu, 14 Sep 2017 12:58:46 GMT

Try the following run anywhere search

| makeresults
| eval _raw="{
\"body\": {
\"1\": {
\"dataId\": 1,
\"first\": \"Mihail\",
\"address\": \"Street b\"
},
\"3\": {
\"dataId\": 3,
\"first\": \"Mickey\",
\"address\": \"Street f\"
},
\"5\": {
\"dataId\": 5,
\"first\": \"Adam\",
\"address\": \"Street g\"
},
\"9\": {
\"dataId\": 1,
\"first\": \"John\",
\"address\": \"Street b\"
}
}
}"
| spath
| table body*
| transpose column_name="field"
| rename "row 1" as "value"
| eval field=split(field,".")
| eval counter=mvindex(field,1)
| eval field=mvindex(field,2)
| table counter value
| stats list(value) as value by counter
| fields - counter
| eval address=mvindex(value,0)
| eval dataId=mvindex(value,1)
| eval first=mvindex(value,2)
| fields - value

Based on the screenshot, you already have query till | table body*, so try commands after that starting with transpose. Try this out and confirm. Also wait for others to answer as there might be a better way!

Edit by chris:

This is another possibility:

| makeresults
| eval _raw="{
\"body\": {
\"1\": {
\"dataId\": 1,
\"first\": \"Mihail\",
\"address\": \"Street b\"
},
\"3\": {
\"dataId\": 3,
\"first\": \"Mickey\",
\"address\": \"Street f\"
},
\"5\": {
\"dataId\": 5,
\"first\": \"Adam\",
\"address\": \"Street g\"
},
\"9\": {
\"dataId\": 1,
\"first\": \"John\",
\"address\": \"Street b\"
}
}
}"
| spath
| table body* 
| foreach body.*.* [eval <<MATCHSEG2>>=mvappend(<<MATCHSEG2>>,'<<FIELD>>')]
| eval line=mvzip(mvzip(first,address,":"),dataId,":") 
| mvexpand line 
|  eval x = split(line,":") 
|  eval first=mvindex(x,0) 
|  eval address=mvindex(x,1) 
|  eval dataId=mvindex(x,2) 
|  table first,address,dataId

Re: Create Table from json data

chris — Thu, 14 Sep 2017 13:49:52 GMT

Thanks a lot, I'll leave the question open for a bit, to see if other solutions pop up.

Re: Create Table from json data

DalJeanis — Thu, 14 Sep 2017 14:12:26 GMT

Here's another way.

| makeresults
 | eval _raw="{
 \"body\": {
 \"1\": {
 \"dataId\": 1,
 \"first\": \"Mihail\",
 \"address\": \"Street b\"
 },
 \"3\": {
 \"dataId\": 3,
 \"first\": \"Mickey\",
 \"address\": \"Street f\"
 },
 \"5\": {
 \"dataId\": 5,
 \"first\": \"Adam\",
 \"address\": \"Street g\"
 },
 \"9\": {
 \"dataId\": 9,
 \"first\": \"John\",
 \"address\": \"Street b\"
 }
 }
 }"
 | spath
 | table body*
 | eval recno=1
 | untable recno name value
 | rex field=name "^body\.(?<temp>\d+)\.(?<fieldname>.*)"
 | xyseries temp fieldname value
 | table dataId first address

Re: Create Table from json data

niketn — Thu, 14 Sep 2017 14:57:54 GMT

@DalJeanis, I was waiting for your answer 🙂
@chris please try out this answer and confirm.

Re: Create Table from json data

chris — Thu, 14 Sep 2017 15:09:34 GMT

Great this is the best solution so far. It think if you replace the last table command with "| fields - temp" the names of the final columns are not needed in the search language and the search is more flexibel if any of the columns change in the json. Then of course if only a subset of the columns is required table will help.

Re: Create Table from json data

DalJeanis — Thu, 14 Sep 2017 15:21:32 GMT

@niketnilay - heh. Mine was different enough from your two that I thought it worth posting.

@chris - glad you like it. I wasn't able to quickly come up with a way that would work for multiple JSON files at one time that had .... oh, there it is...

  | table body*
  | streamstats count as recno
  | untable recno name value
  | rex field=name "^body\.(?<temp>\d+)\.(?<fieldname>.*)"
  | eval recno=recno."!!!!".temp 
  | xyseries recno fieldname value
  | eval recno=mvindex(split(recno,"!!!!"),0)

Something like that would work.

Re: Create Table from json data

DalJeanis — Thu, 14 Sep 2017 16:43:41 GMT

Here's another way to use the foreach, that doesn't hard code the field names ...

 | spath
 | foreach body.*.* [eval data<<MATCHSEG1>>=coalesce(data<<MATCHSEG1>>,"")."!!!!<<MATCHSEG2>>=\"".'<<FIELD>>'."\""]
 | foreach data* [eval alldata=mvappend(alldata,substr(<<FIELD>>,5,len(<<FIELD>>)-4))]
 | table alldata 
 | streamstats count as recno
 | mvexpand alldata
 | streamstats count as recno2 
 | makemv delim="!!!!" alldata
 | mvexpand alldata
 | rex field=alldata "^(?<fieldname>[^=]+)=(?<fieldvalue>.+)"
 | eval {fieldname}=fieldvalue
 | fields - alldata fieldname fieldvalue
 | stats values(*) as * by recno recno2