title: Read JSON array in MYSQL
body: <p>I'm unable to extract determinate data in a JSON.</p>
  
  <p>I have this JSON:</p>
  
  <pre><code>[{"id":1, "type":2}, {"id":2, "type":1}]
  </code></pre>
  
  <p>I want to recover all the ids in a variable in my stored procedure.</p>
  
  <pre><code>CREATE DEFINER=`root`@`%` PROCEDURE `new_procedure`(a JSON)
  BEGIN
     SELECT JSON_TYPE(a);
     -- return ARRAY
  
     SELECT a,JSON_EXTRACT(a,'$.id');
  END
  </code></pre>
  
  <p><code>a</code> return the JSON, but <code>JSON_EXTRACT</code> is empty</p>
  
  <p>Even I prove to save the JSON in a temporary table</p>
  
  <pre><code>CREATE DEFINER=`root`@`%` PROCEDURE `new_procedure`(a JSON)
  BEGIN
  
     SELECT JSON_TYPE(a);
     DROP TEMPORARY TABLE IF EXISTS jsonTemporary;
  
     CREATE TEMPORARY TABLE jsonTemporary SELECT a;
  
     SELECT *,a-&gt;'$.id',a-&gt;&gt;'$.id',JSON_EXTRACT(a,'$.id') FROM jsonTemporary;
  END
  </code></pre>
  
  <p>But the result is the same only the first column return something.</p>