How to parse JSON array using spath or any other option

[swatijha]

Below is the log:

qCode="SOME_CODE",
qValue="[{"id":null,"dayStart":"08:00","dayEnd":"18:00","dayOfWeek":"2","day":"Monday"},{"id":null,"dayStart":"08:00","dayEnd":"18:00","dayOfWeek":"3","day":"Tuesday"},{"id":null,"dayStart":"08:00","dayEnd":"18:00","dayOfWeek":"4","day":"Wednesday"},{"id":null,"dayStart":"08:00","dayEnd":"18:00","dayOfWeek":"5","day":"Thursday"},{"id":null,"dayStart":"08:00","dayEnd":"18:00","dayOfWeek":"6","day":"Friday"},{"id":null,"dayStart":"09:00","dayEnd":"17:00","dayOfWeek":"7","day":"Saturday"},{"id":null,"dayStart":null,"dayEnd":null,"dayOfWeek":"1","day":"Sunday"}]"

from which I have to show dayStart and dayEnd values. I have tried following query:

index=myindex | spath | rename
{}.{}.dayStart as value | table value

but this is not working. Is there a way that I can get values form JSON array?

[koshyk]

I would classify any JSON or KeyValue data could be done
- Before Indexing
- After Indexing

I prefer before indexing, as JSON is KV and when you display the data you get in "Interesting field section" automatically. Inorder to do that, just put in props.conf something like below

# props.conf   
[SPECIAL_EVENT]
NO_BINARY_CHECK = 1
TIME_PREFIX = "timestamp"  # or identify the tag within your JSON data
pulldown_type = 1
KV_MODE = JSON
BREAK_ONLY_BEFORE = (^{)

[woodcock]

Like this:

index=myindex | spath input=qValue | rename {}.* AS * | table dayStart dayEnd

[swatijha]

Thank you so much for your comment.
Your solution works if field value is without double quotes. Like:
qValue=[{"id":null,"dayStart":"08:00","dayEnd":"18:00","dayOfWeek":"2","day":"Monday"},{"id":null,"dayStart":"08:00","dayEnd":"18:00","dayOfWeek":"3","day":"Tuesday"},{"id":null,"dayStart":"08:00","dayEnd":"18:00","dayOfWeek":"4","day":"Wednesday"},{"id":null,"dayStart":"08:00","dayEnd":"18:00","dayOfWeek":"5","day":"Thursday"},{"id":null,"dayStart":"08:00","dayEnd":"18:00","dayOfWeek":"6","day":"Friday"},{"id":null,"dayStart":"09:00","dayEnd":"17:00","dayOfWeek":"7","day":"Saturday"},{"id":null,"dayStart":null,"dayEnd":null,"dayOfWeek":"1","day":"Sunday"}]
Not sure if there is a solution when we have json in double quotes.

Also we have few logs where field value starts with [[{ instead of [{. In that case given solution does not work. Would really appreciated you can suggest some pointers for that as well.

[woodcock]

You can easily remove the double-quotes like this:

 index=myindex | rex field=qValue mode=sed "s/\"//g" | spath input=qValue | rename {}.* AS * | table dayStart dayEnd

If that works, please "Accept" the answer and if there is another question, then please ask a new question.