MySQL5.7で約20程のJSON関数が追加されていましたが、MySQL8.0においても更に追加でJSON関数が加えられているので、基本的な動作のみを確認しています。JSON_ARRAYAGG(), JSON_OBJECTAGG(),JSON_PRETTY()
MySQL5.7 JSON関数マニュアル
https://dev.mysql.com/doc/refman/5.7/en/json-functions.html
MySQL5.7のJSONの概要に関しては、こちらにて資料がダウンロード可能です。https://www.mysql.com/jp/why-mysql/presentations/mysql-json-201701-ja/
まだ、MySQL8.0はDMRなので、これからまだ仕様が変わる部分がある事はご了承ください。
確認バージョン
mysql> select now(),@@version; +---------------------+-----------+ | now() | @@version | +---------------------+-----------+ | 2017-05-31 19:50:46 | 8.0.1-dmr | +---------------------+-----------+ 1 row in set (0.00 sec) mysql>
WL#7987 : JSON aggregation functions
https://dev.mysql.com/worklog/task/?id=7987
Add aggregation functions to generate JSON arrays and objects. This makes it possible to combine JSON documents in multiple rows into a JSON array or a JSON object.
mysql> select body from T_JSON_DOC where id in (1,2); +---------------------------------------------------------------------------------+ | body | +---------------------------------------------------------------------------------+ | {"id": 1, "name": "", "price": 10000, "Conditions": ["NEW", 2015, "Excellent"]} | | {"id": 2, "name": "", "price": 30000, "Conditions": ["USED", 2013, ""]} | +---------------------------------------------------------------------------------+ 2 rows in set (0.01 sec) mysql> select JSON_ARRAYAGG(body) from T_JSON_DOC where id in(1,2); +------------------------------------------------------------------------------------------------------------------------------------------------------------+ | JSON_ARRAYAGG(body) | +------------------------------------------------------------------------------------------------------------------------------------------------------------+ | [{"id": 1, "name": "", "price": 10000, "Conditions": ["NEW", 2015, "Excellent"]}, {"id": 2, "name": "", "price": 30000, "Conditions": ["USED", 2013, ""]}] | +------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> select id,body from T_JSON_DOC where id in(1,2); +----+---------------------------------------------------------------------------------+ | id | body | +----+---------------------------------------------------------------------------------+ | 1 | {"id": 1, "name": "", "price": 10000, "Conditions": ["NEW", 2015, "Excellent"]} | | 2 | {"id": 2, "name": "", "price": 30000, "Conditions": ["USED", 2013, ""]} | +----+---------------------------------------------------------------------------------+ 2 rows in set (0.00 sec) mysql> select JSON_OBJECTAGG(id,body) from T_JSON_DOC where id in(1,2); +----------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | JSON_OBJECTAGG(id,body) | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | {"1": {"id": 1, "name": "", "price": 10000, "Conditions": ["NEW", 2015, "Excellent"]}, "2": {"id": 2, "name": "", "price": 30000, "Conditions": ["USED", 2013, ""]}} | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> select id,body from T_JSON_DOC; +----+---------------------------------------------------------------------------------+ | id | body | +----+---------------------------------------------------------------------------------+ | 1 | {"id": 1, "name": "", "price": 10000, "Conditions": ["NEW", 2015, "Excellent"]} | | 2 | {"id": 2, "name": "", "price": 30000, "Conditions": ["USED", 2013, ""]} | | 3 | {"id": 3, "name": "", "price": 18198, "Conditions": ["NEW", 2015]} | | 4 | {"id": 4, "name": "", "price": 500000, "Conditions": ["NEW", 2015]} | | 5 | {"id": 5, "name": "", "price": 25000, "Conditions": ["NEW", 2015, "January"]} | +----+---------------------------------------------------------------------------------+ 5 rows in set (0.01 sec) mysql> select JSON_OBJECTAGG(id,body) from T_JSON_DOC group by body->"$.Conditions[0]"\G *************************** 1. row *************************** JSON_OBJECTAGG(id,body): {"1": {"id": 1, "name": "", "price": 10000, "Conditions": ["NEW", 2015, "Excellent"]}, "3": {"id": 3, "name": "", "price ": 18198, "Conditions": ["NEW", 2015]}, "4": {"id": 4, "name": "", "price": 500000, "Conditions": ["NEW", 2015]}, "5": {"id": 5, "name": "", "pri ce": 25000, "Conditions": ["NEW", 2015, "January"]}} *************************** 2. row *************************** JSON_OBJECTAGG(id,body): {"2": {"id": 2, "name": "", "price": 30000, "Conditions": ["USED", 2013, ""]}} 2 rows in set (0.01 sec) mysql> mysql> select JSON_OBJECTAGG(id,body) from T_JSON_DOC group by body->"$.Conditions[1]"\G *************************** 1. row *************************** JSON_OBJECTAGG(id,body): {"2": {"id": 2, "name": "", "price": 30000, "Conditions": ["USED", 2013, ""]}} *************************** 2. row *************************** JSON_OBJECTAGG(id,body): {"1": {"id": 1, "name": "", "price": 10000, "Conditions": ["NEW", 2015, "Excellent"]}, "3": {"id": 3, "name": "", "price": 18198, "Conditions": ["NEW", 2015]}, "4": {"id": 4, "name": "", "price": 500000, "Conditions": ["NEW", 2015]}, "5": {"id": 5, "name": "", "price": 25000, "Conditions": ["NEW", 2015, "January"]}} 2 rows in set (0.00 sec) mysql>
WL#9191: JSON_PRETTY function
https://dev.mysql.com/worklog/task/?id=9191
User Feedback from presenting JSON features has suggested that we are missing a function to format JSON in a human-readable way (with new lines and indentation).
This functionality is available in both PHP and PostgreSQL under the name “pretty”:
mysql> select body from T_JSON_DOC where id = 1\G *************************** 1. row *************************** body: {"id": 1, "name": "", "price": 10000, "Conditions": ["NEW", 2015, "Excellent"]} 1 row in set (0.00 sec) mysql> select json_pretty(body) from T_JSON_DOC where id = 1\G *************************** 1. row *************************** json_pretty(body): { "id": 1, "name": "", "price": 10000, "Conditions": [ "NEW", 2015, "Excellent" ] } 1 row in set (0.00 sec) mysql>
その他のJSON関数(運用向け)
JSON_STORAGE_FREE(json_val)
For a JSON column value, this function shows how much storage space was freed in its binary representation after it was updated in place using JSON_SET() or JSON_REPLACE(). The argument can also be a valid JSON document or a string which can be parsed as one—either as a literal value or as the value of a user variable—in which case the function returns 0.
※Updating the column without using JSON_SET() (or JSON_REPLACE()) means that the optimizer cannot perform the update in place; in this case, JSON_STORAGE_FREE() returns 0.
JSON_STORAGE_SIZE(json_val)
This function returns the number of bytes used to store the binary representation of a JSON document.
mysql> SELECT -> jcol, -> JSON_STORAGE_SIZE(jcol) AS Size, -> JSON_STORAGE_FREE(jcol) AS Free -> FROM jtable;
JSON_STORAGE_SIZE(): Return value (bytes)
JSON_STORAGE_FREE(): If no updates have yet been performed, this is 0, as expected.
SELECT -> JSON_STORAGE_SIZE('[100, "sakila", [1, 3, 5], 425.05]') AS A, -> JSON_STORAGE_SIZE('{"a": 1000, "b": "a", "c": "[1, 3, 5, 7]"}') AS B, -> JSON_STORAGE_SIZE('{"a": 1000, "b": "wxyz", "c": "[1, 3, 5, 7]"}') AS C, -> JSON_STORAGE_SIZE('[100, "json", [[10, 20, 30], 3, 5], 425.05]') AS D;