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

[LearningGuy]

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

[LearningGuy]

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

[dtburrows3 · Accepted solution]

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

[LearningGuy]

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

[dural_yyz]

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.

[dtburrows3]

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.