topic Re: How do you extract information from an array of key values in a column with multiple keys? in Getting Data In

How do you extract information from an array of key values in a column with multiple keys?

chalbersma — Wed, 06 Feb 2019 19:16:07 GMT

So I've got an event that has an array of key values like so in a column called associated :

 associates: [
     {
       type: a
       person: person1
     },
     {
       type: b
       person: person2
     },
     {
       type: b
       person: person3
     },
     {
       type: c
       person: person3
     }...]

Now I can pull out all of the people associated with an issue doing the following:

| rename associated{}.person as all_associates

And pull out the "first" associate person like so

| eval dathuman=mvindex(all_assoicates, 0)

But, what I want to do is pull out just the associates of a particular type. So, something that get's me all the associates of type "b" only.

What's the best way to do that?

Re: How do you extract information from an array of key values in a column with multiple keys?

chrisyounger — Wed, 06 Feb 2019 20:09:49 GMT

Working with MV fields is always a challenge.

Try this:

| makeresults 
| eval _raw = "{\"associates\":[{\"type\":\"a\",\"person\":\"person1\"},{\"type\":\"b\",\"person\":\"person2\"},{\"type\":\"b\",\"person\":\"person3\"},{\"type\":\"c\", \"person\": \"person3\"      }]}" 
| spath 
| rename associates{}.person as person associates{}.type as type 
| eval both=mvzip(person, type, "#####") 
| fields both 
| mvexpand both 
| makemv both delim="#####" 
| eval person=mvindex(both, 0) 
| eval type=mvindex(both, 1)
| search type = "b"
| table person

Re: How do you extract information from an array of key values in a column with multiple keys?

chalbersma — Thu, 07 Feb 2019 19:06:32 GMT

We ended up solving this on the import of data instead of in the query. But this does indeed work. Thanks!