title: mysql - loop through all rows and update all objects in json array
body: <p>For mysql 5.7, I have a table <code>copy_jobs</code> with a column of json type <code>job_configs</code>.</p>
  <p><code>job_configs</code> has a property <code>configurations</code> which is an array of objects.</p>
  <p>Example of data in <code>job_configs</code>:</p>
  <pre class="lang-js prettyprint-override"><code>{&quot;configurations&quot;: [
      {
          &quot;role&quot;: &quot;Introduction&quot;, &quot;role_id&quot;: 1, &quot;employee_id&quot;: 1, 
          &quot;first_payment&quot;: &quot;15.63&quot;, &quot;second_payment&quot;: &quot;46.88&quot;, 
          &quot;employee_number&quot;: &quot;00001&quot;, &quot;employee_last_name&quot;: &quot;Mendes&quot;, 
          &quot;first_payment_made&quot;: false, &quot;employee_first_name&quot;: &quot;Mario&quot;, 
          &quot;second_payment_made&quot;: false, &quot;total_bonus_percentage&quot;: 25
      }, {
          &quot;role&quot;: &quot;P &amp; D&quot;, &quot;role_id&quot;: 2, &quot;employee_id&quot;: 2, 
          &quot;first_payment&quot;: &quot;12.50&quot;, &quot;second_payment&quot;: &quot;37.50&quot;, 
          &quot;employee_number&quot;: &quot;00002&quot;, &quot;employee_last_name&quot;: &quot;Parker&quot;, 
          &quot;first_payment_made&quot;: false, &quot;employee_first_name&quot;: &quot;Shaun&quot;, 
          &quot;second_payment_made&quot;: false, &quot;total_bonus_percentage&quot;: 20
      }
  ]}
  
  </code></pre>
  <p>I need to run a migration that will add the properties <code>&quot;fumbled&quot;: false</code> and <code>fumblesCaught: []</code> to each object in the <code>configurations</code> array of every row for this table.</p>
  <p>I thought that</p>
  <pre><code>UPDATE copy_jobs 
  SET job_configs = JSON_SET(job_configs, '$.configurations[*].fumbled', false);
  </code></pre>
  <p>would work at least to add <code>&quot;fumbled&quot;: false</code>, but this returns the error message: <code>In this situation, path expressions may not contain the * and ** tokens.</code></p>
  <p>Is there a way to run a single command in which it will add both new properties to every object in the <code>configurations</code> array?</p>
  <p>Or would I need to create a procedure I could then call, that would get all rows and for each row loop through the <code>configurations</code> object and one by one add the two new properties to the objects?</p>
  <p>EDIT:</p>
  <p>After the comment from @wchiquito I created a procedure thinking this would work.</p>
  <pre><code>CREATE PROCEDURE updateRoleConfig()
  BEGIN
  DECLARE counter Int DEFAULT 0;
  DECLARE totalRows Int;
  SELECT COUNT(*) INTO totalRows FROM copy_jobs;
  SET totalRows = totalRows + 1;
  
  WHILE counter &lt; totalRows DO
      SET @offset = counter;
      PREPARE getRowSql FROM 'SELECT job_configs INTO @jsonConfigs FROM copy_jobs LIMIT 1 OFFSET ?';
      EXECUTE getRowSql USING @offset;
      SELECT concat('selected row is ', @jsonConfigs);
  
      UPDATE copy_jobs SET job_configs = JSON_SET(job_configs, '$.configurations', CAST(
          REPLACE(
              JSON_EXTRACT(@jsonConfigs, '$.configurations'),
              '}',
              ', &quot;fumbled&quot;: false, &quot;fumblesCaught&quot;: []}') AS JSON
          )
      );
      SET counter = counter + 1;
  END WHILE;
  END
  </code></pre>
  <p>However this results in overwriting all of the rows <code>configurations</code> arrays with a single object, resulting in the same for all rows.</p>
  <p>The output of <code>@jsonConfigs</code> is also strangely always the value of the object overwriting all others, which is coincidentally the value of the <code>configurations</code> array found in the first row of the table.</p>
  <pre class="lang-js prettyprint-override"><code>{&quot;configurations&quot;: [
      {
          &quot;role&quot;: &quot;Introduction&quot;, &quot;role_id&quot;: 1, &quot;employee_id&quot;: 1, 
          &quot;first_payment&quot;: &quot;15.63&quot;, &quot;second_payment&quot;: &quot;46.88&quot;, 
          &quot;employee_number&quot;: &quot;00001&quot;, &quot;employee_last_name&quot;: &quot;Mendes&quot;, 
          &quot;first_payment_made&quot;: false, &quot;employee_first_name&quot;: &quot;Mario&quot;, 
          &quot;second_payment_made&quot;: false, &quot;total_bonus_percentage&quot;: 25}
      ]
  }
  
  </code></pre>
  <p>Why is this happening?</p>