Create Table from json data

[chris]

Hi

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

[DalJeanis · Accepted solution]

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

[niketn]

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

[chris]

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.

[DalJeanis]

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

[niketn]

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

[chris]

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

[DalJeanis]

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

[niketn]

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

[chris]

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.

[cmerriman]

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