How to extract multivalue fields from json based on specific multivalue json field to a table?

[krishnar]

So I have json in this format:

{
   "data":{
      "details":[
         {
            "id":"1111",
            "admin":{
               "isLocked":false
            }
         },
         {
            "id":"2222",
            "admin":{
               "due":"3796.10",
               "date":"24 Sep 18"
            }
         },
         {
            "id":"3333",
            "admin":{
               "isLocked":false
            }
         },
         {
            "id":"4444",
            "admin":{
               "isLocked":false
            }
         },
         {
            "id":"5555",
            "admin":{
               "due":"1000"
            }
         }
      ]
   }
}

I'm looking to get all ids with isLocked values.

1. id              isLocked 
2. 1111          false 
3. 3333          false
4. 4444          false

I've tried and tried in vain:

| spath input=jsonData output=locked path=data.details{}.admin{}.isLocked 
| spath input=jsonData output=smthg path=data.details{}.id  
| rename locked as loc 
| rename smthg as newId 
| eval idLock=mvzip(newId, loc, "--")
| mxexpand idLock
| eval id = mvindex(idLock, 0)
| eval isLocked = mvindex(idLock, 1)
| table id isLocked 

This gives me output:

     id                          isLocked 
 1. 1111--false 

Only one row is returned (sometimes its 2222 or 3333) and no value for isLocked column.
I know I have to add more evals somewhere, not able to figure out where (and possibly foreach)
I think loc and newId are returned as arrays as when I do mvcount on them, I get 3 and 5 respectively.
How do I correlate them though?

Any suggestions are highly appreciated.

[jawaharas]

Probably we can do with a custom sourcetype.

--props.conf

[_json_mod1]
BREAK_ONLY_BEFORE = "id
KV_MODE = none
NO_BINARY_CHECK = true
category = Structured
disabled = false
pulldown_type = true

--Query

 sourcetype="_json_mod1" 
| rex field=_raw "id\":\"(?<id>.+)\""
| rex field=_raw "isLocked\":(?<isLocked>.+)"
| fillnull value="n/a" isLocked
| table id, isLocked

--Output

id  isLocked
1111    false
2222    n/a
3333    false
4444    false
5555    n/a

[krishnar]

Thanks J 🙂 Need to study more on props.conf usage.

[kamlesh_vaghela · Accepted solution]

@krishnar

Can you please try below search?

YOUR_SEARCH | spath path=data.details{} output=details | mvexpand details | eval _raw=details | kv | search admin.isLocked=* | rename admin.isLocked as isLocked | table id isLocked

My Sample Search:

| makeresults 
| eval _raw="{\"data\":{\"details\":[{\"id\":\"1111\",\"admin\":{\"isLocked\":false}},{\"id\":\"2222\",\"admin\":{\"due\":\"3796.10\",\"date\":\"24 Sep 18\"}},{\"id\":\"3333\",\"admin\":{\"isLocked\":false}},{\"id\":\"4444\",\"admin\":{\"isLocked\":false}},{\"id\":\"5555\",\"admin\":{\"due\":\"1000\"}}]}}
    " 
| spath path=data.details{} output=details | mvexpand details | eval _raw=details | kv | search admin.isLocked=* | rename admin.isLocked as isLocked | table id isLocked

Thanks

[krishnar]

This works for me (as well the rex thingy mentioned by @jawaharas ). Thank you @kamlesh_vaghela!

[jawaharas]

'kv' command is a good choice. Below search works for me.

|BASE QUERY
| spath path=data.details{} output=details 
| mvexpand details 
| eval _raw=details 
| kv 
| search "isLocked":false 
| rex field=_raw "id\":\"(?<id>.+)\"" 
| rex field=_raw "isLocked\":(?<isLocked>.+)" 
| fillnull value="n/a" isLocked 
| table id, isLocked