title: how to extract json array as table from mysql text column when number of json objects in array is unknown?
body: <p>Is there a way to extract data from a text column containing json arrays with a varying number of json objects into a table?</p>
  
  <p>For example if I...</p>
  
  <pre><code>CREATE TABLE tableWithJsonStr (location TEXT, jsonStr TEXT);
  
  INSERT INTO tableWithJsonStr VALUES 
  ('Home', '[{"animalId":"1","type":"dog", "color":"white","isPet":"1"},{"animalId":"2","type":"cat", "color":"brown","isPet":"1"}]'),
  ('Farm', '[{"animalId":"8","type":"cow", "color":"brown","isPet":"0"}, {"animalId":"33","type":"pig", "color":"pink","isPet":"0"}, {"animalId":"22","type":"horse", "color":"black","isPet":"1"}]'),
  ('Zoo', '[{"animalId":"5","type":"tiger", "color":"stripes","isPet":"0"}]');
  </code></pre>
  
  <p>and</p>
  
  <pre><code>CREATE TABLE animal (
    location TEXT,
    idx INT,
    animalId INT,
    type TEXT,
    color TEXT,
    isPet BOOLEAN
  );
  </code></pre>
  
  <p>I am able to extract tableWithJsonStr.jsonStr by running:</p>
  
  <pre><code>INSERT INTO animal
  SELECT location,
         idx AS id,
         TRIM(BOTH'"' FROM JSON_EXTRACT(jsonStr, CONCAT('$[', idx, '].animalId'))) AS animalId,
         TRIM(BOTH'"' FROM JSON_EXTRACT(jsonStr, CONCAT('$[', idx, '].type'))) AS type,
         TRIM(BOTH'"' FROM JSON_EXTRACT(jsonStr, CONCAT('$[', idx, '].color'))) AS color,
         TRIM(BOTH'"' FROM JSON_EXTRACT(jsonStr, CONCAT('$[', idx, '].isPet'))) AS isPet
  FROM tableWithJsonStr
  JOIN(
    SELECT 0 AS idx UNION
           SELECT 1 AS idx UNION
           SELECT 2 AS idx UNION
           SELECT 3 AS idx
    ) AS indexes
  WHERE JSON_EXTRACT(jsonStr, CONCAT('$[', idx, ']')) IS NOT NULL;
  </code></pre>
  
  <p>The result of the animal table is:</p>
  
  <pre><code>| location | idx | animalId | type  | color   | isPet |
  |==========|=====|==========|=======|=========|=======|
  | Farm     |   0 |        8 |  cow  |   brown |     0 |
  | Farm     |   1 |       33 |  pig  |    pink |     0 |
  | Farm     |   2 |       22 | horse |   black |     1 |
  | Home     |   0 |        1 |   dog |   white |     1 |
  | Home     |   1 |        2 |   cat |   brown |     1 |
  | Zoo      |   0 |        5 | tiger | stripes |     0 |
  </code></pre>
  
  <p>While the solution works, it is not extensible. If I have more than 3 objects in my json array, they will not be accounted for unless I add another SELECT 4 AS idx in my JOIN. Is there a better way to iterate over the objects in the array that doesn't require foreknowledge of the max number of objects that might be in each array? </p>