Spath only extracts the first sublevel in Json, how to extract additional sublevels

[jorjiana88]

Hi,

How can I extract the fields from Properties.Response ? With spath I only get the whole value of Properties.Response but I found no way to extract Id, Reference, Isfallback, Registrationdare, etc.

{
"Timestamp": "2019-01-15T16:35:31.5431887+00:00",
"Level": "Information",
"Properties": {
"correlationId": "49892ff0-5c61",
"Response": "{\"Id\":\"164455245211\",\"Reference\":\"45554522-37\",\"IsFallback\":false,\"RegistrationDate\":\"2019-01-15T16:35:31.251+00:00\",\"ErrorResult\":null}",
"status": 200,
"SourceContext": "application"
}
}

[kamlesh_vaghela · Accepted solution]

@ jorjiana88

Can you please try this?

YOUR SEARCH
| kv | table Level "Properties.SourceContext" "Properties.correlationId" "Properties.status" "Properties.Response" | eval _raw='Properties.Response' | kv

I have use kv for field extraction from JSON.

My Sample Search:

| makeresults | eval _raw="{
\"Timestamp\": \"2019-01-15T16:35:31.5431887+00:00\",
\"Level\": \"Information\",
\"Properties\": {
\"correlationId\": \"49892ff0-5c61\",
\"Response\": \"{\\\"Id\\\":\\\"164455245211\\\",\\\"Reference\\\":\\\"45554522-37\\\",\\\"IsFallback\\\":false,\\\"RegistrationDate\\\":\\\"2019-01-15T16:35:31.251+00:00\\\",\\\"ErrorResult\\\":null}\",
\"status\": 200,
\"SourceContext\": \"application\"
}
}" | kv | table Level "Properties.SourceContext" "Properties.correlationId" "Properties.status" "Properties.Response" | eval _raw='Properties.Response' | kv

Thanks

[jorjiana88]

works great, and no need for regex.. thanks!

[saurabhkharkar]

Try adding this regex to your search 

| rex field=Properties.Response "\{\"Id\":\"(?<Id>\d+)\",\"Reference\":\"(?<Reference>[^\"]+)\",\"IsFallback\":(?<IsFallback>[^\,]+)\,\"RegistrationDate\":\"(?<RegistrationDate>[^\"]+)\",\"ErrorResult\":(?<ErrorResult>[^\}]+)"
| table Properties.Response Id Reference IsFallback RegistrationDate ErrorResult

[jorjiana88]

Thanks a lot, it works, but is there any solution without regex ? Since we have a lot to work with json logs, it would be nice to have a more user friendly solution that I can show to my colleagues also.