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;