5 ms·

MySQL 8.0 Labs: JSON aggregation functions

57 points by th3sly 10y ago

ralusek 10y ago
How is JSON stored/indexed in MySQL? Is it like JSONB in Postgres with GIN indexing available?
- greenlet 10y ago
  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
- morgo 10y ago
  Here is an example: http://mysqlserverteam.com/indexing-json-documents-via-virtual-columns/ 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
  - malisper 10y ago
    Is there a way to index JSON in MySQL without creating one index per unique field?
    - morgo 10y ago
      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 10y ago
how does mysql's json type compare to postgres' jsonb? my biggest gripe with both of them is lack of partial update
- deleted 10y ago
  [deleted]
- Psyonic 10y ago
  jsonb_set is available now in postgres 9.5: https://www.postgresql.org/docs/9.5/static/functions-json.html https://www.postgresql.org/docs/9.5/static/functions-json.ht...
  - th3sly 10y ago
    but that's not partial update. this will update the entire document(blob).
    - semiquaver 10y ago
      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 10y ago
        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.
- morgo 10y ago
  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-is-available/ http://mysqlserverteam.com/the-mysql-8-0-0-milestone-release...
morgo 10y ago
MySQL Product Manager here. For a more general overview of 8.0, see: http://mysqlserverteam.com/the-mysql-8-0-0-milestone-release-is-available/ 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-table-expressions-in-mysql-ctes/ http://mysqlserverteam.com/mysql-8-0-labs-recursive-common-t...
- rubiquity 10y ago
  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 10y ago
    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-qualification.html http://anithagopi.blogspot.ca/2013/05/new-feature-qualificat...
    - dcgudeman 10y ago
      when did the versioning for mysql change? After 5.7?
      - morgo 10y ago
        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".
chrishynes 10y ago
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 10y ago
  It sounds like a JSON_TABLE function: https://docs.oracle.com/database/121/SQLRF/functions092.htm#SQLRF56973 https://docs.oracle.com/database/121/SQLRF/functions092.htm#... We are looking to add something similar.
  - chrishynes 10y ago
    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.

57 points by th3sly 10y ago

ralusek 10y ago

How is JSON stored/indexed in MySQL? Is it like JSONB in Postgres with GIN indexing available?

greenlet 10y ago
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
morgo 10y ago
Here is an example: http://mysqlserverteam.com/indexing-json-documents-via-virtual-columns/ 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
- malisper 10y ago
  Is there a way to index JSON in MySQL without creating one index per unique field?
  - morgo 10y ago
    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 10y ago

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

deleted 10y ago
[deleted]
Psyonic 10y ago
jsonb_set is available now in postgres 9.5: https://www.postgresql.org/docs/9.5/static/functions-json.html https://www.postgresql.org/docs/9.5/static/functions-json.ht...
- th3sly 10y ago
  but that's not partial update. this will update the entire document(blob).
  - semiquaver 10y ago
    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 10y ago
      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.
morgo 10y ago
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-is-available/ http://mysqlserverteam.com/the-mysql-8-0-0-milestone-release...

morgo 10y ago

MySQL Product Manager here. For a more general overview of 8.0, see: http://mysqlserverteam.com/the-mysql-8-0-0-milestone-release-is-available/ 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-table-expressions-in-mysql-ctes/ http://mysqlserverteam.com/mysql-8-0-labs-recursive-common-t...

rubiquity 10y ago
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 10y ago
  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-qualification.html http://anithagopi.blogspot.ca/2013/05/new-feature-qualificat...
  - dcgudeman 10y ago
    when did the versioning for mysql change? After 5.7?
    - morgo 10y ago
      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".

chrishynes 10y ago

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 10y ago
It sounds like a JSON_TABLE function: https://docs.oracle.com/database/121/SQLRF/functions092.htm#SQLRF56973 https://docs.oracle.com/database/121/SQLRF/functions092.htm#... We are looking to add something similar.
- chrishynes 10y ago
  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.