The engineering team is 2x the size that it was at the time of the Oracle acquisition. 8.0 is the release where some of the investments are starting to pay off :)
This limitation had to be lifted for CTEs to be reused, which is in the same labs release as the JSON functions mentioned here.
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
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.
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.
The mapping is not strictly one index per field, but a virtual column expression must map to a MySQL type. So depending on the use-case you may be able to use more complicated expressions to limit indexes.
But to expand your question, there are two FRs we are looking at:
i believe you have to create a virtual column on specific document fields and create a secondary index on that similar to what you do with Postgres' expression indices, I do not think MySQL has a general JSON GIN index yet
Postgres' MVCC architecture makes the idea of 'partial updates' kind of meaningless. An update to any postgres column is equivalent to a DELETE followed by an INSERT and will result in a new row on disk, leaving the old row to be vacuumed later.
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...