title: MySQL 5.7 JSON_REMOVE multiple values from array in one query
body: <p>I have a MySQL 5.7 database with a JSON column containing an array of strings.</p>
  <p>I need to remove a varying number of those strings from the array, by value, in one query.</p>
  <p>Example JSON:</p>
  <pre><code>[&quot;1-1-2&quot;, &quot;1-1-3&quot;, &quot;1-2-2&quot;, &quot;1-2-3&quot;, &quot;1-1-16&quot;, &quot;1-1-17&quot;]
  </code></pre>
  <p>I may need to remove &quot;1-1-16&quot; and &quot;1-1-17&quot; so I end up with:</p>
  <pre><code>[&quot;1-1-2&quot;, &quot;1-1-3&quot;, &quot;1-2-2&quot;, &quot;1-2-3&quot;]
  </code></pre>
  <p><em>At other times, I may need to remove just one value, or several more, in one query.</em></p>
  <p><code>JSON_REMOVE()</code> can accept multiple <em><strong>path</strong></em> arguments, but the problem is that when multiple paths are specified, the result of <code>JSON_REMOVE()</code> is passed sequentially left to right on each path, which makes it very difficult to use the result of <code>JSON_SEARCH()</code> for each passed path.</p>
  <p>For example, this does not work, because the 2nd JSON_SEARCH will return the incorrect index for <code>'1-1-17'</code> after <code>'1-1-16'</code> has been removed:</p>
  <pre><code>UPDATE json_meta
                  SET document = 
                  JSON_REMOVE( document, 
                      JSON_UNQUOTE(JSON_SEARCH(document, 'one', '1-1-16')),               
                      JSON_UNQUOTE(JSON_SEARCH(document, 'one', '1-1-17')),               
                  )
  WHERE id=10
  </code></pre>
  <p>You need to do this instead:</p>
  <pre><code>UPDATE json_meta
                  SET document = 
                  JSON_REMOVE( document, 
                      JSON_UNQUOTE(JSON_SEARCH(document, 'one', '1-1-16')),               
                      JSON_UNQUOTE(JSON_SEARCH(JSON_REMOVE( document, 
                      JSON_UNQUOTE(JSON_SEARCH(document, 'one', '1-1-16'))), 'one', '1-1-17'))
                  )
  WHERE id=10
  
  </code></pre>
  <p>The query grows exponentially complex with each additional string that needs to be removed.</p>
  <p>I'm wondering if the best solution would just be to use a chained <code>REPLACE()</code> with all permutations of commas on each string (i.e. each string with a comma before, a comma after, a comma both before and after).</p>
  <p>Final note: I found another question with the exact same issue described <a href="https://stackoverflow.com/questions/55744311/how-to-remove-multiple-values-from-mysql-json">here</a>. However, that question doesn't have an accepted answer, and the one answer there is very complex. That answer indicates that MySQL 5.6 doesn't have much JSON support; I am wondering, since I'm using MySQL 5.7, is there a simpler solution possible?</p>