Getting Data In

Create Table from json data

chris
Motivator

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

alt text

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

Regards
Chris

Tags (1)
0 Karma
1 Solution

DalJeanis
SplunkTrust
SplunkTrust

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

View solution in original post

DalJeanis
SplunkTrust
SplunkTrust

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
Legend

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

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"

chris
Motivator

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.

0 Karma

DalJeanis
SplunkTrust
SplunkTrust

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

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
____________________________________________
| makeresults | eval message= "Happy Splunking!!!"

chris
Motivator

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

0 Karma

DalJeanis
SplunkTrust
SplunkTrust

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
Legend

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

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

chris
Motivator

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.

0 Karma

cmerriman
Super Champion

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

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...