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?
| spath Field1{} output=Field1
| mvexpand Field1
| spath input=Field1 occupation
| where isnotnull(occupation)
| spath input=Field1 name
| table name
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
Field1 | id | name | occupation |
{"id":1234,"name":"John"} | 1234 | John | |
{"id":5678,"name":"Mary","occupation":{"title":"lawyer","employer":"law firm"}} | 5678 | Mary | {"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{} | id | name | occupation.employer | occupation.title |
{ "id": 1234, "name": "John" } | 1234 | John | ||
{ "id": 5678, "name": "Mary", "occupation": { "title": "lawyer", "employer": "law firm" } } | 5678 | Mary | law firm | lawyer |
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 ```
| spath Field1{} output=Field1
| mvexpand Field1
| spath input=Field1 occupation
| where isnotnull(occupation)
| spath input=Field1 name
| table name