topic Re: How to parse JSON array using spath or any other option in Getting Data In

How to parse JSON array using spath or any other option

swatijha — Fri, 03 Jul 2015 10:27:23 GMT

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?

Re: How to parse JSON array using spath or any other option

woodcock — Fri, 03 Jul 2015 14:20:57 GMT

Like this:

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

Re: How to parse JSON array using spath or any other option

swatijha — Mon, 06 Jul 2015 09:22:05 GMT

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.

Re: How to parse JSON array using spath or any other option

koshyk — Mon, 06 Jul 2015 11:22:46 GMT

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 = (^{)

Re: How to parse JSON array using spath or any other option

woodcock — Tue, 07 Jul 2015 04:35:17 GMT

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.