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}] |
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
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
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
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
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.
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.