title: Indexing JSON column in MySQL 8
body: <p>So I'm experimenting with json column. Mysql 8.0.17 supposed to work with multi value JSON indexes, like this:</p>
  
  <p><code>CREATE INDEX data__nbr_idx ON a1( (CAST(data-&gt;'$.nbr' AS UNSIGNED ARRAY)) )</code></p>
  
  <p>I have column categories with JSON like this ["books", "clothes"].
  I need to get all products from "books" category. I can use "json_contains" or new "member of".</p>
  
  <pre><code>SELECT * FROM products WHERE JSON_CONTAINS(categories, '\"books\"')
  SELECT * FROM products WHERE "books" MEMBER OF(categories)
  </code></pre>
  
  <p>And it works. The problem is that of course EXPLAIN will reveal that there queries are making full table scan, and because of that it is slow.</p>
  
  <p>So I need some index.</p>
  
  <p>I changed index example by replacing "unsigned" type with "char(32) since my categories are strings and not numbers. I cannot find any example for this in google so I assumed that char() will be fine, but not.</p>
  
  <p>This is my index query:</p>
  
  <pre><code>CREATE INDEX categories_index ON products((CAST(categories AS CHAR(32) ARRAY)))
  </code></pre>
  
  <p>also tried</p>
  
  <pre><code>CREATE INDEX categories_index ON products((CAST(categories-&gt;'$' AS CHAR(32) ARRAY)))
  </code></pre>
  
  <p>but selects are still making full table scan. What I'm doing wrong?
  How to correctly index json column without using virtual columns?</p>