topic Re: How can I extract the JSON data as key value pair? in Getting Data In

How can I extract the JSON data as key value pair?

soumyacharya91 — Fri, 20 Apr 2018 15:01:46 GMT

Hi,

I have extracted the JSON data. After data indexed I found that one field contains another format of JSON data which is indexed as a string. Please help me in extracting the data. Please find the log details below which I had received from indexer after the indexing.

{"field1": "value1", "field2": "value2", "field3": "value3", "field4": "{\"subfield\":\"value\",\"subfield\":\"value\"}", "field": "value"}

Please help me in extracting the data as key value pair which is present in the field4 . Rest fields are able to parse the data correctly.

Thanks,
Sam

Re: How can I extract the JSON data as key value pair?

somesoni2 — Fri, 20 Apr 2018 16:42:26 GMT

Give this a try (first two lines are to generate sample data)

| gentimes start=-1 | eval _raw="{\"field1\": \"value1\", \"field2\": \"value2\", \"field3\": \"value3\", \"field4\": \"{\\\"subfield1\\\":\\\"value\\\",\\\"subfield2\\\":\\\"value\\\"}\", \"field\": \"value\"}" | table _raw 
| rex field=_raw mode=sed "s/\\\\"/"/g s/\"\{/[{/ s/\}\"/}]/"| spath

Re: How can I extract the JSON data as key value pair?

woodcock — Sun, 22 Apr 2018 20:00:57 GMT

I am not sure that I get exactly what you need but try this:

| makeresults 
| eval _raw="{\"field1\": \"value1\", \"field2\": \"value2\", \"field3\": \"value3\", \"field4\": \"{\\\"subfield\\\":\\\"value1\\\",\\\"subfield\\\":\\\"value2\\\"}\", \"field\": \"value\"}"

| rename COMMENT AS "Everything above generates sample event data; everything below is your solution"

| spath field4
| rex field=field4 max_match=0 "\"subfield\":\s*\"(?<field4>[^\"]+)"

Re: How can I extract the JSON data as key value pair?

soumyacharya91 — Mon, 23 Apr 2018 09:05:50 GMT

Hi woodcock,

I have tried the solution but it is not working.

I tried below query along with my base search and checked it is properly extracting the data but I don't know how to apply this in splunk backend files. Is there any way we can apply this solution to props.conf / transforms.conf

|spath input = field4

Thanks,
Sam

Re: How can I extract the JSON data as key value pair?

TISKAR — Mon, 23 Apr 2018 09:14:43 GMT

@soumyacharya91, can you try this:

   | makeresults 
       | eval _raw="{\"field1\": \"value1\", \"field2\": \"value2\", \"field3\": \"value3\", \"field4\": \"{\\\"subfield\\\":\\\"value1\\\",\\\"subfield\\\":\\\"value2\\\"}\", \"field\": \"value\"}"
       | extract 
       | rex field=field4 "\"subfield\":\s*\"(?<subfield1>[^\"]+)\",\"subfield\":\s*\"(?<subfield2>[^\"]+)"

Re: How can I extract the JSON data as key value pair?

soumyacharya91 — Mon, 23 Apr 2018 09:15:52 GMT

Hi,

It is not working.

Re: How can I extract the JSON data as key value pair?

soumyacharya91 — Mon, 23 Apr 2018 09:22:19 GMT

Hi,

This is not working.

Re: How can I extract the JSON data as key value pair?

TISKAR — Mon, 23 Apr 2018 09:27:41 GMT

So try this

| makeresults 
          | eval _raw="{\"field1\": \"value1\", \"field2\": \"value2\", \"field3\": \"value3\", \"field4\": \"{\\\"subfield\\\":\\\"value1\\\",\\\"subfield\\\":\\\"value2\\\"}\", \"field\": \"value\"}"
          | extract 
          | rex field=field4 "\"subfield\":\s*\"(?<subfield1>[^\"]+)\",\"subfield\":\s*\"(?<subfield2>[^\"]+)"

Re: How can I extract the JSON data as key value pair?

vsai0718 — Tue, 27 Aug 2019 16:58:59 GMT

| rename _raw AS _temp field4 AS _raw | extract pairdelim="?&" kvdelim="=" | rename _raw AS field4 _temp AS _raw

You can try this, it extracts all the nested key, value pairs at search time

Re: How can I extract the JSON data as key value pair?

vsai0718 — Tue, 27 Aug 2019 17:00:58 GMT

You can try this one
| rename _raw AS _temp field4 AS _raw | extract pairdelim="?&" kvdelim="=" | rename _raw AS field4 _temp AS _raw

Re: How can I extract the JSON data as key value pair?

woodcock — Sat, 26 Oct 2019 02:35:52 GMT

Try this:

| makeresults 
| eval _raw="{\"field1\": \"value1\", \"field2\": \"value2\", \"field3\": \"value3\", \"field4\": \"{\\\"subfield\\\":\\\"value1\\\",\\\"subfield\\\":\\\"value2\\\"}\", \"field\": \"value\"}" 
| rename COMMENT AS "Everything above generates sample event data; everything below is your solution" 
| rex max_match=0 "\\\\\"subfield\\\\\":\s*\\\\\"(?<field4>[^\\\\\"]+)"

This RegEx string is not dependent on the spath so it can be used in props.conf directly.

Re: How can I extract the JSON data as key value pair?

woodcock — Sat, 26 Oct 2019 02:36:13 GMT

See my other answer.