title: How to create an index on a JSON array using MySQL 8
body: <p>I have a JSON field in mysql 8 that looks like this:</p>
  <pre><code>[{&quot;key&quot;: &quot;apples&quot;, &quot;string_values&quot;: [&quot;red&quot;]}, {&quot;key&quot;: &quot;oranges&quot;, &quot;string_values&quot;: [&quot;orange&quot;]}]
  </code></pre>
  <p>Is there a way to create an index on string values? I was hoping I could do something like this:</p>
  <pre><code>ALTER TABLE mytable ADD INDEX myindex( 
      (CAST(metadata-&gt;'$[*].string_values' AS UNSIGNED ARRAY)) 
  );
  </code></pre>
  <p>However that returns the following error: Cannot store an array or an object in a scalar key part of the index</p>
  <p>Fiddle for example: <a href="https://dbfiddle.uk/?rdbms=mysql_8.0&amp;fiddle=ae36a5169094ae67a290febf09f49f0c" rel="nofollow noreferrer">https://dbfiddle.uk/?rdbms=mysql_8.0&amp;fiddle=ae36a5169094ae67a290febf09f49f0c</a></p>
  <p>EDIT:</p>
  <p>I'm starting to think that the only way I can do this is with a generated column:</p>
  <p>The following select will hit the index now.</p>
  <pre><code>ALTER TABLE mytable ADD string_values JSON AS (JSON_EXTRACT(metadata, '$[*].string_values[0]'));
  
  ALTER TABLE mytable ADD INDEX idx_string_values( 
      (CAST(string_values-&gt;'$' AS CHAR(100) ARRAY))
  );
  
  SELECT * FROM mytable WHERE 'red' MEMBER OF(string_values-&gt;'$');
  </code></pre>