Splunk Search

Working with periods in spath command

Rjbeckwith
Explorer

I have a lot of json data that contains periods in the keys. I want to be able to expand one of the arrays in the data with the spath command.  It does not seem to work with a period in the json data in the simple example below:

| makeresults | eval _raw="
{
\"content\":{
\"jvm.memory\": [{\"num\":1.0},{\"num\":2.0}]
}
}"
| spath | spath path=content.jvm.memory{} output=event_data | mvexpand event_data | eval _raw=event_data | kv



The following query does work with an underscore in the key name.

| makeresults | eval _raw="
{
\"content\":{
\"jvm_memory\": [{\"num\":1.0},{\"num\":2.0}]
}
}"
| spath | spath path=content.jvm_memory{} output=event_data | mvexpand event_data | eval _raw=event_data | kv


Are there any ways to work around the periods in the keys? Maybe some sort of mass replace of the periods in the key names only (not the values) or some sort of way to escape the periods in the spath command?

Labels (2)
0 Karma
1 Solution

manjunathmeti
Champion

Then try this:

| makeresults 
| eval _raw="
{
\"misc_field\": 0,
\"content\":{
\"jvm.memory\": [{\"num\":1.0, \"data\": {\"test\":2.4, \"test2\": 2.3}},{\"num\":2.0, \"data\":{\"test\":3, \"test2\": 2.3}}],
\"field_test2\": 3
}
}" 
| spath path=content output=content 
| eval content=replace(content, "jvm\.memory", "jvm_memory") 
| spath input=content path=jvm_memory{} output=event_data 
| mvexpand event_data 
| spath input=event_data

 

If this reply helps you, an upvote/like would be appreciated.

View solution in original post

manjunathmeti
Champion

You can rename the field with dots in name.

| makeresults 
| eval _raw="
{
\"content\":{
\"jvm.memory\": [{\"num\":1.0},{\"num\":2.0}]
}
}" 
| spath 
| rename content.jvm.memory{}.* as * 
| mvexpand num

If this reply helps you, an upvote/like would be appreciated.

0 Karma

Rjbeckwith
Explorer

Thank you for the response. That did work for that simple example. Unfortunately my data has a lot of other fields underneath the array so I cannot specify the specific field to mvexpand. 

In this slightly more complicated example I am not able to unroll by just specifying one field like in the solution you provided. (My dataset has many keys underneath the jvm_memory field so naming them manually won't work)

 

| makeresults | eval _raw="
{
\"misc_field\": 0,
\"content\":{
\"jvm_memory\": [{\"num\":1.0, \"data\": {\"test\":2.4, \"test2\": 2.3}},{\"num\":2.0, \"data\":{\"test\":3, \"test2\": 2.3}}],
\"field_test2\": 3
}
}"
| spath | spath path=content.jvm_memory{} output=event_data | mvexpand event_data | eval _raw=event_data | kv

 

 This above yields what I want (The Json array elements have been split into separate rows and the keys have become columns)

0 Karma

manjunathmeti
Champion

Then try this:

| makeresults 
| eval _raw="
{
\"misc_field\": 0,
\"content\":{
\"jvm.memory\": [{\"num\":1.0, \"data\": {\"test\":2.4, \"test2\": 2.3}},{\"num\":2.0, \"data\":{\"test\":3, \"test2\": 2.3}}],
\"field_test2\": 3
}
}" 
| spath path=content output=content 
| eval content=replace(content, "jvm\.memory", "jvm_memory") 
| spath input=content path=jvm_memory{} output=event_data 
| mvexpand event_data 
| spath input=event_data

 

If this reply helps you, an upvote/like would be appreciated.

Rjbeckwith
Explorer

That worked thank you!

0 Karma
Get Updates on the Splunk Community!

2024 Splunk Career Impact Survey | Earn a $20 gift card for participating!

Hear ye, hear ye! The time has come again for Splunk's annual Career Impact Survey!  We need your help by ...

Optimize Cloud Monitoring

  TECH TALKS Optimize Cloud Monitoring Tuesday, August 13, 2024  |  11:00AM–12:00PM PST   Register to ...

What's New in Splunk Cloud Platform 9.2.2403?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.2.2403! Analysts can ...