MySQL 8.0 Labs: JSON aggregation functions
mysqlserverteam.comMySQL Product Manager here.
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...
Wow! That's a lot of cool stuff! MySQL seems to really be alive lately. Did Oracle decide to give it more attention?
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?
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 :)
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...
when did the versioning for mysql change? After 5.7?
Yes. We wanted to be able to drop the leading 5. For context:
6.0 was a canceled release. 7.x. is used by Cluster. 8.0 makes it easy to follow as "it just drops the 5".
Thanks for the quick reply, looks like a great release!
Can MySQL queries refer to a temporary tables more than once in the same query, yet?
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.
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
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.
It sounds like a JSON_TABLE function: https://docs.oracle.com/database/121/SQLRF/functions092.htm#...
We are looking to add something similar.
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.
How is JSON stored/indexed in MySQL?
Is it like JSONB in Postgres with GIN indexing available?
Here is an example: http://mysqlserverteam.com/indexing-json-documents-via-virtu...
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
Is there a way to index JSON in MySQL without creating one index per unique field?
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:
1) Inverted (aka FULLTEXT) indexes on JSON documents 2) Multi-value indexes
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
how does mysql's json type compare to postgres' jsonb? my biggest gripe with both of them is lack of partial update
No partial update yet, but we are working towards it -
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...
jsonb_set is available now in postgres 9.5: https://www.postgresql.org/docs/9.5/static/functions-json.ht...
but that's not partial update. this will update the entire document(blob).
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.
Just to clarify: s/architecture/implementation/
InnoDB is MVCC too, but does an update in place with relocation of the old row to UNDO space. There are pros and cons to both approaches.