How to map a multivalued JSON Field Value(X) to its respective Field(Y) while writing datamodel Searches?

[bhargavnariyani]

Hi,
I Have a Sample Event as Follows:

     A: [
        {   [-]
         a1: xxx
         b1:xxxx    
        }   
        {   [-]
              a1: yyy
              b1:yyyy
        }   
    ]   

Am Mapping the fields in Datamodel and Getting Values a1:xxx,yyy
b1:xxxx,yyyy in the field.
When The Query is written For a table What should be done to map the value xxx of a1 to xxxx of b1 and yyy of a1 to yyyy of b1 respectively from a datamodel.

[kamlesh_vaghela · Accepted solution]

A:
    [
    {    [-]
            a1: xxx
            b1:xxxx    
         }    
         {    [-]
               a1: yyy
               b1:yyyy
         }    
       ]

As I understand your question, you need below result from above event using DataModel.

a1    b1
--------------
xxx    xxxx
yyy    yyyy

I would like to share my views with Data model and Non- Data model approach.

With DataModel Approach

With DataModel approach, we have to use tstats search and for the getting a1 and b1 columns we can write search like below

| tstats values(datamodelName.node.a1) as a1,values(datamodelName.node.b1) as b1 
from datamodel=datamodelName 
where nodename=datamodelName.node 
by _time,YOUR_UNIQUE_ID_IF_ANY

But this will also return multivalued in a1 and b1, with loosing mapping.

I have tried to store node "A" (full JSON string ) in data model by using spath configuration in props.conf and tried with below search and I got expected result.

| tstats count
from datamodel=datamodelName 
where nodename=datamodelName.node 
by _time,YOUR_UNIQUE_ID_IF_ANY,datamodelName.node.A
| eval _raw=datamodelName.node.A 
| extract pairdelim="\"{,}" kvdelim=":" 
| table a1 b1

But it is very difficult and complex approach and I'm NOT SUGGESTING. Because DataModel is not for storing JSON object.
It is good to keep data model as simple as possible.

Non-Data model Approach

I'm suggesting Non-Data model Approach:

The Non-data model approach, we have to write a plain search and extract multiple rows and columns from a single event by using mvzip, mvindex and split.

See below search.

YOUR SEARCH 
| eval tempField=mvzip(a1,b1) 
| stats count by _time,YOUR_UNIQUE_ID_IF_ANY,tempField 
| eval a1=mvindex(split(tempField,","),0), b1=mvindex(split(tempField,","),1) 

As per the JSON, node "A" contains multiple JSON objects this search will extract column a1 and b1. You can add more columns by using mvzip, splits

Well, now we are replacing Data model to plain search so performance issue can be raised. So I suggest to put this search in savedsearch.conf and accelerate it. It will give you expected output and performance both.

I hope this will help you.

Thanks
Kamlesh

[bhargavnariyani]

Used the Non DataModel Approach, It Worked Well...

[woodcock]

I am sure that I do not understand the full context here but very generally you need mvzip, mvindex, and possibly mvfilter and mvjoin..