Convert JSON array in MySQL to rows
<p><strong>UPDATE:</strong> This is now possible in MySQL 8 via the JSON_TABLE function: <a href="https://dev.mysql.com/doc/refman/8.0/en/json-table-functions.html" rel="noreferrer">https://dev.mysql.com/doc/refman/8.0/en/json-table-functions.html</a></p>

<p>I'm loving the new JSON functions in MySQL 5.7, but running into a block trying to merge values from JSON into a normal table structure.</p>

<p>Grabbing JSON, manipulating and extracting arrays from it etc. is simple. JSON_EXTRACT all the way. But what about the inverse, going from a JSON array to rows? Perhaps I am dense on the existing MySQL JSON functionality, but I haven't been able to figure that one out.</p>

<p>For example, say I have a JSON array and want to insert a row for each element in the array with its value? The only way I have found is to write a bunch of JSON_EXTRACT(... '$[0]') JSON_EXTRACT(... '$[1]') etc and union them together.</p>

<p>Or, say I have a JSON array and want to GROUP_CONCAT() it to a single comma separated string?</p>

<p>In other words, I know I can do this:</p>

<pre><code>SET @j = '[1, 2, 3]';
SELECT GROUP_CONCAT(JSON_EXTRACT(@j, CONCAT('$[', x.n, ']'))) AS val
  FROM   
  (    
    SELECT 0 AS n    
    UNION    
    SELECT 1 AS n    
    UNION    
    SELECT 2 AS n    
    UNION    
    SELECT 3 AS n    
    UNION    
    SELECT 4 AS n    
    UNION    
    SELECT 5 AS n    
  ) x
WHERE x.n &lt; JSON_LENGTH(@j);
</code></pre>

<p>But that hurts my eyes. And my heart.</p>

<p>How can I do something like:</p>

<pre><code>SET @j = '[1, 2, 3]';
SELECT GROUP_CONCAT(JSON_EXTRACT(@j, '$[ * ]'))
</code></pre>

<p>... and have it concatenate together the values in the array vs. the JSON array itself?</p>

<p>I guess what I'm looking for here is some sort of JSON_SPLIT along the lines of:</p>

<pre><code>SET @j = '[1, 2, 3]';

SELECT GROUP_CONCAT(val)
FROM
  JSON_SPLIT(JSON_EXTRACT(@j, '$[ * ]'), '$')
</code></pre>

<p>If MySQL had a proper STRING_SPLIT(val, 'separator') table returning function, I could hack it (escaping be damned), but that's not available either.</p>