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!

Index This | A sphere has three, a circle has two, and a point has zero. What is it?

September 2023 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

Build Scalable Security While Moving to Cloud - Guide From Clayton Homes

 Clayton Homes faced the increased challenge of strengthening their security posture as they went through ...

Mission Control | Explore the latest release of Splunk Mission Control (2.3)

We’re happy to announce the release of Mission Control 2.3 which includes several new and exciting features ...