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

junaedsa — Wed, 06 Mar 2024 16:58:54 GMT

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?

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

ITWhisperer — Wed, 06 Mar 2024 17:20:35 GMT

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

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

yuanliu — Wed, 06 Mar 2024 17:27:33 GMT

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 ```

topic How to get the value of a field inside a matching object of a multivalue field in Splunk Search

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

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

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