Reformat JSON arrays in MySQL database table using MySQL SELECT query (Wordpress)
<p>I have a Wordpress build that uses CalderaWP forms. Unfortunately, the plugin's author has decided to store all checkbox entries as JSON arrays in the WP database and custom field values show up as "Array" instead of the actual text values.  </p>

<p>I'd like to run a cron job that reformats these entries, but the first part is trying to figure out how to find and replace all values that are JSON objects.</p>

<p>I've written a function to get values from a specific table in the database, specifically the "wp_cf_form_entry_values" table.  This function uses a regular expression in the SELECT query to find all values that begin with the "{" character.  This is what I came up with to find JSON objects in the value field, but maybe there is a better way.  Anyway, the function gets these values and reformats them from a JSON object to a list of individual items (i.e. various values that were checked in the submitted form). </p>

<p>Here is my current function:</p>

<pre><code>function reformat_my_data() {
    global $wpdb;
    $data = $wpdb-&gt;get_results("SELECT value 
                                FROM `wp_cf_form_entry_values` 
                                WHERE `value` REGEXP '^[{].*$'
                                ");

    foreach($data as $key =&gt; $field) {

        foreach($field as $val) {
            if( is_json($val) ) {
                $val = json_decode($val, true);

                foreach($val as $checked) {
                    echo ' - ' . $checked . '&lt;br/&gt;';
                }
            }
        }

    }

}
reformat_my_data();
</code></pre>

<p>I also have another function to determine if the value is a JSON object:<br/>
<code>function is_json($string) {
    json_decode($string);
    return (json_last_error() == JSON_ERROR_NONE);
}</code></p>

<p>This works and outputs a list like this:<br/>
 - checked #1<br/>
 - checked #2<br/>
 - checked #3<br/></p>

<p><strong>My question is: How do I get this newly formatted value back into the database?  In other words, how do I do a mass replace of JSON type objects in the WP database?</strong></p>