Splunk Search

How to get the value of a field inside a matching object of a multivalue field

junaedsa
Engager

I have a json that looks like this:

{

"Field1" : [

{

"id": 1234

"name": "John"

},

{

"id": 5678

"name": "Mary"

"occupation": {

"title": "lawyer",

"employer": "law firm"

}

}

]

}

I want to extract the value of the "name" field from the object that contains an occupation field (could be any). In this case I want to get "Mary" and store it inside a variable. How would I do this using splunk search language?

Labels (5)
0 Karma
1 Solution

ITWhisperer
SplunkTrust
SplunkTrust
| spath Field1{} output=Field1
| mvexpand Field1
| spath input=Field1 occupation
| where isnotnull(occupation)
| spath input=Field1 name
| table name

View solution in original post

yuanliu
SplunkTrust
SplunkTrust

Thank you for illustrating input in text format.  But please make sure JSON is conformant when doing mockups.

Speaking of JSON, I always say do not treat structured data as text.  regex is not a suitable tool for structured data in most cases.  Splunk's robust, QA tested tool will save you countless hours down the road.  Traditional tool for this is spath.  Since 9.0, Splunk also added fromjson that can simplify this work.  I'll begin with the simpler one.  You didn't say which field the JSON is in, so I'll assume that's _raw in the following.

 

| fromjson _raw
| mvexpand Field1
| fromjson Field1

 

 This gives you

Field1idnameoccupation
{"id":1234,"name":"John"}1234John 
{"id":5678,"name":"Mary","occupation":{"title":"lawyer","employer":"law firm"}}5678Mary{"title":"lawyer","employer":"law firm"}

The spath alternative is - again assuming JSON is in _raw

 

| spath path=Field1{}
| mvexpand Field1{}
| spath input=Field1{}

 

This gives

Field1{}idnameoccupation.employeroccupation.title
{ "id": 1234, "name": "John" }1234John  
{ "id": 5678, "name": "Mary", "occupation": { "title": "lawyer", "employer": "law firm" } }5678Marylaw firmlawyer

There  can be many variants in between.  But the essence is to extract elements of the JSON array, then handle the array as a multivalue field as a whole.  If, for example, there are too many elements and you worry about RAM, you can use mvfilter to get data about Mary as you are not interested in other entries:

 

| fromjson _raw
| eval of_interest = mvfilter(json_extract(Field1, "name") == "Mary")

 

(Note you need 8.0 to use json_extract.) You get

Field1
of_interest
{"id":1234,"name":"John"}
{"id":5678,"name":"Mary","occupation":{"title":"lawyer","employer":"law firm"}}
{"id":5678,"name":"Mary","occupation":{"title":"lawyer","employer":"law firm"}}

Hope this helps.

By the way, the conformant form of your mock data is

 

{

"Field1" : [

{

"id": 1234,

"name": "John"

},

{

"id": 5678,

"name": "Mary",

"occupation": {

"title": "lawyer",

"employer": "law firm"

}

}

]

}

 

You can play with the following emulation and compare with real data

 

| makeresults
| eval _raw = "{

\"Field1\" : [

{

\"id\": 1234,

\"name\": \"John\"

},

{

\"id\": 5678,

\"name\": \"Mary\",

\"occupation\": {

\"title\": \"lawyer\",

\"employer\": \"law firm\"

}

}

]

}"
``` data emulation above ```

 

 

Tags (2)

ITWhisperer
SplunkTrust
SplunkTrust
| spath Field1{} output=Field1
| mvexpand Field1
| spath input=Field1 occupation
| where isnotnull(occupation)
| spath input=Field1 name
| table name
Get Updates on the Splunk Community!

Detecting Remote Code Executions With the Splunk Threat Research Team

WATCH NOWRemote code execution (RCE) vulnerabilities pose a significant risk to organizations. If exploited, ...

Enter the Splunk Community Dashboard Challenge for Your Chance to Win!

The Splunk Community Dashboard Challenge is underway! This is your chance to showcase your skills in creating ...

.conf24 | Session Scheduler is Live!!

.conf24 is happening June 11 - 14 in Las Vegas, and we are thrilled to announce that the conference catalog ...