For a more general overview of 8.0, see: http://mysqlserverteam.com/the-mysql-8-0-0-milestone-release...
This feature is part of the optimizer labs release, which also includes CTEs: http://mysqlserverteam.com/mysql-8-0-labs-recursive-common-t...
We're very happy with 5.7 right now and 8.0 looks great. Our app is quite old so invisible indexes will be awesome for finding what we can get rid of.
When will the JSON and CTEs make it into "proper" MySQL?
Invisible indexes was one of my FRs, so very happy to hear you like it. For others: http://mysqlserverteam.com/mysql-8-0-invisible-indexes/
Labs are merged into regular trunk when they pass QA qualification. A list of what is involved is here: http://anithagopi.blogspot.ca/2013/05/new-feature-qualificat...
I haven't checked yet if it is lifted for CTEs only, or all cases. But CTEs are a replacement for most (all?) uses of temporary tables that I can think of.
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
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.
We are looking to add something similar.
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.
Is it like JSONB in Postgres with GIN indexing available?
The optimizer will match JSON expressions to virtual columns w/indexes. So that means you can keep your queries in this form and have them indexed:
EXPLAIN SELECT * FROM features WHERE feature->"$.properties.STREET" = 'MARKET'\G
The json binary format spec allows for padding. Search in page for 'BLOB' for details: http://mysqlserverteam.com/the-mysql-8-0-0-milestone-release...