Getting Data In

How to parse JSON array using spath or any other option

swatijha
New Member

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?

Tags (2)
0 Karma

koshyk
Super Champion

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

woodcock
Esteemed Legend

Like this:

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

swatijha
New Member

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.

0 Karma

woodcock
Esteemed Legend

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.

Get Updates on the Splunk Community!

Enhance Your Splunk App Development: New Tools & Support

UCC FrameworkAdd-on Builder has been around for quite some time. It helps build Splunk apps faster, but it ...

Prove Your Splunk Prowess at .conf25—No Prereqs Required!

Your Next Big Security Credential: No Prerequisites Needed We know you’ve got the skills, and now, earning the ...

Splunk Observability Cloud's AI Assistant in Action Series: Observability as Code

This is the sixth post in the Splunk Observability Cloud’s AI Assistant in Action series that digs into how to ...