
MySQL 8.0 Labs: JSON aggregation functions - th3sly
http://mysqlserverteam.com/mysql-8-0-labs-json-aggregation-functions/
======
morgo
MySQL Product Manager here.

For a more general overview of 8.0, see: [http://mysqlserverteam.com/the-
mysql-8-0-0-milestone-release...](http://mysqlserverteam.com/the-
mysql-8-0-0-milestone-release-is-available/)

This feature is part of the optimizer labs release, which also includes CTEs:
[http://mysqlserverteam.com/mysql-8-0-labs-recursive-
common-t...](http://mysqlserverteam.com/mysql-8-0-labs-recursive-common-table-
expressions-in-mysql-ctes/)

~~~
rubiquity
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?

~~~
morgo
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/](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...](http://anithagopi.blogspot.ca/2013/05/new-feature-
qualification.html)

~~~
dcgudeman
when did the versioning for mysql change? After 5.7?

~~~
morgo
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".

~~~
dcgudeman
Thanks for the quick reply, looks like a great release!

------
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#...](https://docs.oracle.com/database/121/SQLRF/functions092.htm#SQLRF56973)

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.

------
ralusek
How is JSON stored/indexed in MySQL?

Is it like JSONB in Postgres with GIN indexing available?

~~~
morgo
Here is an example: [http://mysqlserverteam.com/indexing-json-documents-via-
virtu...](http://mysqlserverteam.com/indexing-json-documents-via-virtual-
columns/)

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

~~~
malisper
Is there a way to index JSON in MySQL without creating one index per unique
field?

~~~
morgo
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

------
greenlet
how does mysql's json type compare to postgres' jsonb? my biggest gripe with
both of them is lack of partial update

~~~
Psyonic
jsonb_set is available now in postgres 9.5:
[https://www.postgresql.org/docs/9.5/static/functions-
json.ht...](https://www.postgresql.org/docs/9.5/static/functions-json.html)

~~~
th3sly
but that's not partial update. this will update the entire document(blob).

~~~
semiquaver
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.

~~~
morgo
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.

