topic Re: Fix JSON format by replacing single quote and put into new field in Splunk Search

Fix JSON format by replacing single quote and put into new field

LearningGuy — Wed, 17 Jan 2024 22:08:41 GMT

Hello,
| dbxquery connection=test query="select employee_data from company"
The following employee_data is not in proper JSON format, so I can't use spath.
How do I replace single quote (') with double quote ("), replace None with "None" and put it on a new field?
Thank you for your help.

employee_data

[{company':'company A','name': 'employee A1','position': None}, {company': 'company A','name': 'employee A2','position': None}]

[{company':'company B','name': 'employee B1','position': None}, {company': 'company B','name': 'employee B2','position': None}]

[{company':'company C','name': 'employee C1','position': None}, {company': 'company C','name': 'employee C2','position': None}]

Re: Fix JSON format by replacing single quote and put into new field

dtburrows3 — Wed, 17 Jan 2024 23:06:42 GMT

Maybe something like this?

<base_search> | eval employee_data=replace(replace(employee_data, "\'", "\""), "None", "\"None\"") | spath input=employee_data

Testing on my local instance looks like it worked out.

Re: Fix JSON format by replacing single quote and put into new field

LearningGuy — Wed, 17 Jan 2024 23:21:07 GMT

Hello,
Which part of your search that put the into a new field "formatted_data"?
I don't see "formatted_data" in the search.
Can you paste the whole search including how you put simulated data?

Thank you for your help.

Thanks

Re: Fix JSON format by replacing single quote and put into new field

dtburrows3 — Wed, 17 Jan 2024 23:24:44 GMT

More of just an additional screenshot for context and a fieldname with a description of the eval that was done to it... but I see that is has caused some confusion.

Here is full search of the local emulation.

| makeresults | eval employee_data="[{'company':'company A','name': 'employee A1','position': None}, {'company': 'company A','name': 'employee A2','position': None}]" | append [ | makeresults | eval employee_data="[{'company':'company B','name': 'employee B1','position': None}, {'company': 'company B','name': 'employee B2','position': None}]" ] | append [ | makeresults | eval employee_data="[{'company':'company C','name': 'employee C1','position': None}, {'company': 'company C','name': 'employee C2','position': None}]" ] | eval formatted_data=replace(replace(employee_data, "\'", "\""), "None", "\"None\"") | spath input=formatted_data

Re: Fix JSON format by replacing single quote and put into new field

LearningGuy — Thu, 18 Jan 2024 15:25:06 GMT

Hello,

I tested your solution and it worked. Thank you for your help.
1) if I put eval on employee_data, will it change the original data?
2) I apply the solution on the real data and I got the following error. How do I fix this without increasing memory limit?
command.mvexpand: output will be truncated at 1000 results due to excessive memory usage. Memory threshold of 500MB as configured in limits.conf / [mvexpand] / max_mem_usage_mb has been reached

Re: Fix JSON format by replacing single quote and put into new field

dural_yyz — Thu, 18 Jan 2024 15:40:12 GMT

It sounds like the original data has too many lines/events. You may want to implement EventBreaker or LineBreak in your props.conf on the ingest before you need to apply any search time eval or mvexpand.