[chrishynes]

Very cool!

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.

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.

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

In other words, I know I can do this:

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 < JSON_LENGTH(@j);

But that hurts my eyes. And my heart.

How can I do something like:

SET @j = '[1, 2, 3]';

SELECT GROUP_CONCAT(JSON_EXTRACT(@j, '$[ * ]'))

... and have it concatenate together the values in the array vs. the JSON array itself.

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

SET @j = '[1, 2, 3]';

SELECT GROUP_CONCAT(val) FROM JSON_SPLIT(JSON_EXTRACT(@j, '$[ * ]'), '$')

Which, speaking of, a STRING_SPLIT(val, 'separator') table returning function is also sorely needed.

[morgo]

It sounds like a JSON_TABLE function: https://docs.oracle.com/database/121/SQLRF/functions092.htm#...

We are looking to add something similar.

[chrishynes]

That looks perfect, looking forward to it!

I was doing a little more research and it looks like MySQL doesn't support table valued functions at all right now -- not built-ins, not custom. I didn't realize that, I guess I just assumed that was a fundamental feature.

Probably explains the longstanding lack of STRING_SPLIT() etc., I guess.

Here's hoping you are able to get in a more generic support for table valued functions, including stuff like STRING_SPLIT() as well as custom table returning functions.