Go to Qiita Advent Calendar 2024 Top
LoginSignup
24
31

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 5 years have passed since last update.

@abcb2

MySQLでJSONを扱う

Posted at

mysql8上でJSON Data Typeを試してみる。

JSON Data Typeは5.7からサポートされた機能、のはず。

mysql6, 7の経緯はこちらを参照すると良いかも。

dockerでmysqlを準備する

データ移行を特に考えない場合

とりあえずすぐに使ってみたい場合は淡々と下記のコマンドを打ち込んでいけばよい。

# docker pull mysql:8.0.12
# docker run --name mysql8 -e MYSQL_ROOT_PASSWORD=mysqlpass --restart always -d mysql:8.0.12
# docker exec -it mysql8 bash
root@061095cb1b39:/# mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.12 MySQL Community Server - GPL

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

docker execしないでmysqlサーバーにアクセスする場合は下記を参考。

3306ポートをdockerのmysqlコンテナに向けて上げる方法もある。

# docker inspect mysql8 | jq .[0].NetworkSettings.IPAddress
"172.17.0.2"

$ mysql -u root -p -h 172.17.0.2
  Enter password: 
  Welcome to the MySQL monitor.  Commands end with ; or \g.
  Your MySQL connection id is 15
  Server version: 8.0.12 MySQL Community Server - GPL
  
  Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
  
  Oracle is a registered trademark of Oracle Corporation and/or its
  affiliates. Other names may be trademarks of their respective
  owners.
  
  Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

データ移行を考える場合。

先のデータ移行を考えない場合でもすでに下記の通り、 /var/lib/mysql だけをコンテナをホストしているシステムで保存してはいる。

# docker inspect mysql8 | jq .[0].Mounts
[
  {
    "Type": "volume",
    "Name": "d5dbc5f20b14c40405bb43dd29c10419f7ff8b9e834ae0408a7ff587496ab9e5",
    "Source": "/var/lib/docker/volumes/d5dbc5f20b14c40405bb43dd29c10419f7ff8b9e834ae0408a7ff587496ab9e5/_data",
    "Destination": "/var/lib/mysql",
    "Driver": "local",
    "Mode": "",
    "RW": true,
    "Propagation": ""
  }
]
# docker volume ls
DRIVER              VOLUME NAME
local               d5dbc5f20b14c40405bb43dd29c10419f7ff8b9e834ae0408a7ff587496ab9e5

まずはデータ移行を考えてボリュームを作成する。

# docker volume create mysql-data
mysql-data
# docker volume ls
DRIVER              VOLUME NAME
local               mysql-data

作成したボリュームを使ってmysqlコンテナを起動する。

# docker run -v mysql-data:/var/lib/mysql --name mysql8 -e MYSQL_ROOT_PASSWORD=mysqlp -d -p 3306:3306 mysql:8.0.12
$ mysql -u root -p -h 127.0.0.1
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.12 MySQL Community Server - GPL

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>
mysql> create database json_sample;
mysql> use json_sample;
mysql> create table jtable (jcol JSON);
mysql> insert into jtable values('{"a": 100}');
mysql> select * from jtable;
+------------+
| jcol       |
+------------+
| {"a": 100} |
+------------+
mysql> select jcol->>"$.a" from jtable;
+--------------+
| jcol->>"$.a" |
+--------------+
| 100          |
+--------------+

# docker stop mysql8
mysql8

他のコンテナ(今回はubuntu)を使ってvolumeをマウントし、ローカルのカレントディレクトリにtar.gzとしてmysqlのデータを出力する。

mysql-data2というボリュームを新たに作成し、そこにリストアする。

mysqlコンテナの2回目の起動時はパスワードを設定する必要はない。

# docker run -v mysql-data:/target -v `pwd`:/export -w /target ubuntu bash -c 'tar zcvf /export/mysql-data.YYYYMMDD.tar.gz .'
# docker volume create mysql-data2
mysql-data2
# docker volume ls
DRIVER              VOLUME NAME
local               mysql-data
local               mysql-data2
# docker run -v mysql-data2:/target -v `pwd`:/import -w /target ubuntu bash -c 'tar zxvf /import/mysql-data.YYYYMMDD.tar.gz'
# docker run -v mysql-data2:/var/lib/mysql --name mysql8_2 -d -p 3306:3306 mysql:8.0.12
# docker exec -it mysql8_2 bash
もしくは
# mysql -u root -p -h 127.0.0.1
mysql> use json_sample;
mysql> select * from jtable;
+------------+
| jcol       |
+------------+
| {"a": 100} |
+------------+

こうすることでテストに使うデータセット別のmysqlサーバーを用意して素早く利用することなどができる。

参考にした記事

JSON Data Type

JSONの検証は下記から。

こんな感じでカラムに適用できる。

mysql> show create table book\G
*************************** 1. row ***************************
       Table: book
Create Table: CREATE TABLE `book` (
  `id` mediumint(8) NOT NULL AUTO_INCREMENT,
  `title` varchar(200) NOT NULL,
  `tags` json DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.01 sec)
  • jsonデータの自動validationが有効になる
  • ストレージに最適化されバイナリ形式で保存される。
  • jsonカラムに格納されるサイズは max_allowed_packet で定義される
  • jsonカラムにはデフォルト値を設定できない
  • json関数群を使えるようになる。
  • GeoJSONも使えるようになる。
  • jsonカラムに対しては直接インデックスを作成できない。ただし、jsonデータのスカラー値に対してはインデックスを作成できる。

基本的な使い方

mysql> create table users (data JSON);
mysql> insert into users values (json_object('name', 'taro', 'age', 20));
mysql> insert into users values (json_object('name', 'jiro', 'age', 15));
mysql> insert into users values (json_object('name', 'saburo', 'age', 10));
mysql> select data->>"$.name" from users;
+-----------------+
| data->>"$.name" |
+-----------------+
| taro            |
| jiro            |
| saburo          |
+-----------------+
mysql> select * from users where data->>"$.age" > 14;
+-----------------------------+
| data                        |
+-----------------------------+
| {"age": 20, "name": "taro"} |
| {"age": 15, "name": "jiro"} |
+-----------------------------+
mysql> select * from users where data->>"$.age" > 9;
+-------------------------------+
| data                          |
+-------------------------------+
| {"age": 20, "name": "taro"}   |
| {"age": 15, "name": "jiro"}   |
| {"age": 10, "name": "saburo"} |
+-------------------------------+
mysql> update users set data = json_set(data, '$.age', json_extract(data, '$.age') + 10);
mysql> select * from users;
+---------------------------------+
| data                            |
+---------------------------------+
| {"age": 30.0, "name": "taro"}   |
| {"age": 25.0, "name": "jiro"}   |
| {"age": 20.0, "name": "saburo"} |
+---------------------------------+
mysql> update users set data = json_set(data, '$.alcohol', true) where data->>"$.age" > 25;
mysql> select * from users;
+------------------------------------------------+
| data                                           |
+------------------------------------------------+
| {"age": 30.0, "name": "taro", "alcohol": true} |
| {"age": 25.0, "name": "jiro"}                  |
| {"age": 20.0, "name": "saburo"}                |
+------------------------------------------------+
mysql> select * from users where data->"$.alcohol" = true;
+------------------------------------------------+
| data                                           |
+------------------------------------------------+
| {"age": 30.0, "name": "taro", "alcohol": true} |
+------------------------------------------------+

$ はある特定の状況を指し示す。 ワイルドカードを使ったアクセスも可能だ。

mysql> select json_extract('[3, {"a": [5, 6], "b": 10}, [99, 100]]', '$[1]') as json;
+------------------------+
| json                   |
+------------------------+
| {"a": [5, 6], "b": 10} |
+------------------------+
mysql> select json_extract('[3, {"a": [5, 6], "b": 10}, [99, 100]]', '$[1].a[0]') as json;
+------+
| json |
+------+
| 5    |
+------+
mysql> select json_extract('{"a":1, "b":2, "c":[3,4,5]}', '$.*');
+----------------------------------------------------+
| json_extract('{"a":1, "b":2, "c":[3,4,5]}', '$.*') |
+----------------------------------------------------+
| [1, 2, [3, 4, 5]]                                  |
+----------------------------------------------------+
mysql> select json_extract('{"a": {"b": 1}, "c": {"b": 2}, "d":{"e": {"b": 3}}}', '$**.b');
+------------------------------------------------------------------------------+
| json_extract('{"a": {"b": 1}, "c": {"b": 2}, "d":{"e": {"b": 3}}}', '$**.b') |
+------------------------------------------------------------------------------+
| [1, 2, 3]                                                                    |
+------------------------------------------------------------------------------+
mysql> select json_extract('{"a": {"b": 1}, "c": {"b": 2}, "d":{"e": {"b": 3}}}', '$.*.b');
+------------------------------------------------------------------------------+
| json_extract('{"a": {"b": 1}, "c": {"b": 2}, "d":{"e": {"b": 3}}}', '$.*.b') |
+------------------------------------------------------------------------------+
| [1, 2]                                                                       |
+------------------------------------------------------------------------------+
mysql> select json_extract('[1, 2, {"foo":"bar"}, 3, {"foo":"hoge"}, 5]', '$[0 to 2].foo');
+------------------------------------------------------------------------------+
| json_extract('[1, 2, {"foo":"bar"}, 3, {"foo":"hoge"}, 5]', '$[0 to 2].foo') |
+------------------------------------------------------------------------------+
| ["bar"]                                                                      |
+------------------------------------------------------------------------------+

functions

mysql> SET @j = '["a", {"b": [true, false]}, [10, 20]]';
mysql> select json_insert(@j, '$[2]', 100, '$[3]', 1, '$[2][2]', 10);
+--------------------------------------------------------+
| json_insert(@j, '$[2]', 100, '$[3]', 1, '$[2][2]', 10) |
+--------------------------------------------------------+
| ["a", {"b": [true, false]}, [10, 20, 10], 1]           |
+--------------------------------------------------------+
mysql> select json_replace(@j, '$[1].b[0]', 100, '$[3]', 777);
+-------------------------------------------------+
| json_replace(@j, '$[1].b[0]', 100, '$[3]', 777) |
+-------------------------------------------------+
| ["a", {"b": [100, false]}, [10, 20]]            |
+-------------------------------------------------+
mysql> select json_remove(@j, '$[2]', '$[1].b[1]', '$[1].b[1]');
+---------------------------------------------------+
| json_remove(@j, '$[2]', '$[1].b[1]', '$[1].b[1]') |
+---------------------------------------------------+
| ["a", {"b": [true]}]                              |
+---------------------------------------------------+

JSON Functions

公式のドキュメントはこちら

計28個

Name Description
JSON_ARRAY() Create JSON array
JSON_ARRAY_APPEND() Append data to JSON document
JSON_ARRAY_INSERT() Insert into JSON array
-> Return value from JSON column after evaluating path; equivalent to JSON_EXTRACT().
JSON_CONTAINS() Whether JSON document contains specific object at path
JSON_CONTAINS_PATH() Whether JSON document contains any data at path
JSON_DEPTH() Maximum depth of JSON document
JSON_EXTRACT() Return data from JSON document
->> Return value from JSON column after evaluating path and unquoting the result; equivalent to JSON_UNQUOTE(JSON_EXTRACT()).
JSON_INSERT() Insert data into JSON document
JSON_KEYS() Array of keys from JSON document
JSON_LENGTH() Number of elements in JSON document
JSON_MERGE() (deprecated 8.0.3) Merge JSON documents, preserving duplicate keys. Deprecated synonym for JSON_MERGE_PRESERVE()
JSON_MERGE_PATCH() Merge JSON documents, replacing values of duplicate keys
JSON_MERGE_PRESERVE() Merge JSON documents, preserving duplicate keys
JSON_OBJECT() Create JSON object
JSON_PRETTY() Prints a JSON document in human-readable format, with each array element or object member printed on a new line, indented two spaces with respect to its parent.
JSON_QUOTE() Quote JSON document
JSON_REMOVE() Remove data from JSON document
JSON_REPLACE() Replace values in JSON document
JSON_SEARCH() Path to value within JSON document
JSON_SET() Insert data into JSON document
JSON_STORAGE_FREE() Freed space within binary representation of a JSON column value following a partial update
JSON_STORAGE_SIZE() Space used for storage of binary representation of a JSON document; for a JSON column, the space used when the document was inserted, prior to any partial updates
JSON_TABLE() Returns data from a JSON expression as a relational table
JSON_TYPE() Type of JSON value
JSON_UNQUOTE() Unquote JSON value
JSON_VALID() Whether JSON value is valid

以降はここに挙がっている関数群を公式ドキュメントに載っているサンプルを参考に試していく。(長い)

Functions That Create JSON Values

計3個

  • JSON_ARRAY
  • JSON_OBJECT
  • JSON_QUOTE

Functions That Search JSON Values

計7個

  • JSON_CONTAINS
  • JSON_CONTAINS_PATH
  • JSON_EXTRACT
  • column->path
  • column->>path
  • JSON_KEYS
  • JSON_SEARCH

Functions That Modify JSON Values

計10個

  • JSON_ARRAY_APPEND
  • JSON_ARRAY_INSERT
  • JSON_INSERT
  • JSON_MERGE
  • JSON_MERGE_PATCH
  • JSON_MERGE_PRESERVE
  • JSON_REMOVE
  • JSON_REPLACE
  • JSON_SET
  • JSON_UNQUOTE

Functions That Return JSON Value Attributes

計4個

  • JSON_DEPTH
  • JSON_LENGTH
  • JSON_TYPE
  • JSON_VALID

JSON Table Functions

計1個

  • JSON_TABLE

JSON Utility Functions

計3個

  • JSON_PRETTY
  • JSON_STORAGE_FREE
  • JSON_STORAGE_SIZE

JSON Functions Sample

JSON_ARRAY

json配列を返す

mysql> select json_array(1, "abc", null, true, curtime());
+---------------------------------------------+
| json_array(1, "abc", null, true, curtime()) |
+---------------------------------------------+
| [1, "abc", null, true, "23:30:04.000000"]   |
+---------------------------------------------+

JSON_OBJECT

key valueのペアリストを評価し、オブジェクトを返す。

keyがnullの場合や引数が奇数の場合はエラー

mysql> select json_object('id', 87, 'name', 'taro');
+---------------------------------------+
| json_object('id', 87, 'name', 'taro') |
+---------------------------------------+
| {"id": 87, "name": "taro"}            |
+---------------------------------------+
mysql> select json_object('id', 87, 'name', 'taro', 'foo');
ERROR 1582 (42000): Incorrect parameter count in the call to native function 'json_object'
mysql> select json_object('id', 87, 'name', 'taro', null, 'yes');
ERROR 3158 (22032): JSON documents may not contain NULL member names.

JSON_QUOTE

文字列をダブルクォートで囲みその他の文字をエスケープしてくれる。

引数がnullの場合はNULL

mysql> select json_quote(null), json_quote('null'), json_quote('"null"');
+------------------+--------------------+----------------------+
| json_quote(null) | json_quote('null') | json_quote('"null"') |
+------------------+--------------------+----------------------+
| NULL             | "null"             | "\"null\""           |
+------------------+--------------------+----------------------+

JSON_CONTAINS

特定の候補がターゲットに含まれているかをチェックし1か0を返す

pathを指定することもできる。

mysql> SET @j = '{"a": 1, "b": 2, "c": {"d": 4}}';
mysql> select json_contains(@j, '{"a":1}');
+------------------------------+
| json_contains(@j, '{"a":1}') |
+------------------------------+
|                            1 |
+------------------------------+
mysql> select json_contains(@j, '1', "$.a");
+-------------------------------+
| json_contains(@j, '1', "$.a") |
+-------------------------------+
|                             1 |
+-------------------------------+
mysql> select json_contains(@j, '{"d":4}', "$.c");
+-------------------------------------+
| json_contains(@j, '{"d":4}', "$.c") |
+-------------------------------------+
|                                   1 |
+-------------------------------------+
mysql> select json_contains(@j, '4', "$.c.d");
+---------------------------------+
| json_contains(@j, '4', "$.c.d") |
+---------------------------------+
|                               1 |
+---------------------------------+

JSON_CONTAINS_PATH

JSONドキュメントに引数に与えられたパスもしくは複数のパスが含まれているかを確認し0か1を返す。

引数がNULLの場合はNULLを返す。

one もしくは all という引数を与えて、パスが一つでも含まれている場合と全て含まれている場合の条件を指定することができる。

mysql> SET @j = '{"a": 1, "b": 2, "c": {"d": 4}}';
mysql> select json_contains_path(@j, 'one', '$.foo', '$.bar');
+-------------------------------------------------+
| json_contains_path(@j, 'one', '$.foo', '$.bar') |
+-------------------------------------------------+
|                                               0 |
+-------------------------------------------------+
mysql> select json_contains_path(@j, 'one', '$.foo', '$.bar', '$.a');
+--------------------------------------------------------+
| json_contains_path(@j, 'one', '$.foo', '$.bar', '$.a') |
+--------------------------------------------------------+
|                                                      1 |
+--------------------------------------------------------+
mysql> select json_contains_path(@j, 'all', '$.foo', '$.bar', '$.a');
+--------------------------------------------------------+
| json_contains_path(@j, 'all', '$.foo', '$.bar', '$.a') |
+--------------------------------------------------------+
|                                                      0 |
+--------------------------------------------------------+
mysql> select json_contains_path(@j, 'all', '$.a', '$.b');
+---------------------------------------------+
| json_contains_path(@j, 'all', '$.a', '$.b') |
+---------------------------------------------+
|                                           1 |
+---------------------------------------------+
mysql> select json_contains_path(@j, 'one', '$.c.d');
+----------------------------------------+
| json_contains_path(@j, 'one', '$.c.d') |
+----------------------------------------+
|                                      1 |
+----------------------------------------+
mysql> select json_contains_path(@j, 'one', '$.a.d');
+----------------------------------------+
| json_contains_path(@j, 'one', '$.a.d') |
+----------------------------------------+
|                                      0 |
+----------------------------------------+

JSON_EXTRACT

JSONドキュメントからpathにマッチしたデータを返す。pathがマッチしない場合はNULLを返す。

複数マッチするバスの場合は配列データとして返される。

mysql> select json_extract('[10, 20, [30, 40]]', '$[1]');
+--------------------------------------------+
| json_extract('[10, 20, [30, 40]]', '$[1]') |
+--------------------------------------------+
| 20                                         |
+--------------------------------------------+
mysql> select json_extract('[10, 20, [30, 40]]', '$[2][1]');
+-----------------------------------------------+
| json_extract('[10, 20, [30, 40]]', '$[2][1]') |
+-----------------------------------------------+
| 40                                            |
+-----------------------------------------------+
mysql> select json_extract('[10, 20, [30, 40]]', '$[0]', '$[2][1]');
+-------------------------------------------------------+
| json_extract('[10, 20, [30, 40]]', '$[0]', '$[2][1]') |
+-------------------------------------------------------+
| [10, 40]                                              |
+-------------------------------------------------------+
mysql> select json_extract('[10, 20, [30, 40]]', '$[0]', '$[2][1]', '$[2][*]');
+------------------------------------------------------------------+
| json_extract('[10, 20, [30, 40]]', '$[0]', '$[2][1]', '$[2][*]') |
+------------------------------------------------------------------+
| [10, 40, 30, 40]                                                 |
+------------------------------------------------------------------+

column->path

-> オペレーターは JSON_EXTRACT のエイリアスとなっている。

データを作成する

mysql> create table json_samples ( data json);
mysql> insert into json_samples (data) values (json_object("id", 2, "name", "Wilma"));
mysql> insert into json_samples (data) values (json_object("id", 3, "name", "Barney"));
mysql> insert into json_samples (data) values (json_object("id", 4, "name", "Betty"));
mysql> select * from json_samples;
+-----------------------------+
| data                        |
+-----------------------------+
| {"id": 3, "name": "Barney"} |
| {"id": 4, "name": "Betty"}  |
| {"id": 2, "name": "Wilma"}  |
+-----------------------------+

オペレーターを試す。

mysql> select data, json_extract(data, "$.id"), json_extract(data, "$.name") from json_samples where json_extract(data, "$.id") > 2 order by json_extract(data, "$.name") desc;
+-----------------------------+----------------------------+------------------------------+
| data                        | json_extract(data, "$.id") | json_extract(data, "$.name") |
+-----------------------------+----------------------------+------------------------------+
| {"id": 4, "name": "Betty"}  | 4                          | "Betty"                      |
| {"id": 3, "name": "Barney"} | 3                          | "Barney"                     |
+-----------------------------+----------------------------+------------------------------+

mysql> select data, data->"$.id", data->"$.name" from json_samples where data->"$.id" > 2 order by data->"$.name" desc;
+-----------------------------+--------------+----------------+
| data                        | data->"$.id" | data->"$.name" |
+-----------------------------+--------------+----------------+
| {"id": 4, "name": "Betty"}  | 4            | "Betty"        |
| {"id": 3, "name": "Barney"} | 3            | "Barney"       |
+-----------------------------+--------------+----------------+

column->>path

column->path が抽出したデータをunquoteしてくれなかった部分を改良してquoteしてくれるようになっている。

JSON_UNQUOTE(JSON_EXTRACT()) しているのと同じこと。

mysql> select data, data->>"$.id", data->>"$.name" from json_samples where data->"$.id" > 2 order by data->"$.name" desc;
+-----------------------------+---------------+-----------------+
| data                        | data->>"$.id" | data->>"$.name" |
+-----------------------------+---------------+-----------------+
| {"id": 4, "name": "Betty"}  | 4             | Betty           |
| {"id": 3, "name": "Barney"} | 3             | Barney          |
+-----------------------------+---------------+-----------------+
mysql> select data, json_extract(data, "$.id"), json_unquote(json_extract(data, "$.name")) from json_samples where json_extract(data, "$.id") > 2 order by json_extract(data, "$.name") desc;
+-----------------------------+----------------------------+--------------------------------------------+
| data                        | json_extract(data, "$.id") | json_unquote(json_extract(data, "$.name")) |
+-----------------------------+----------------------------+--------------------------------------------+
| {"id": 4, "name": "Betty"}  | 4                          | Betty                                      |
| {"id": 3, "name": "Barney"} | 3                          | Barney                                     |
+-----------------------------+----------------------------+--------------------------------------------+

JSON_KEYS

JSONオブジェクトからtop-levelのkeyを配列で返す。

pathが指定された場合は選択されたpathのtop-levelのkeyの配列をを返す。pathにワイルドカード指定はできない。

mysql> select json_keys('{"a":1,"b":{"c":30}}');
+-----------------------------------+
| json_keys('{"a":1,"b":{"c":30}}') |
+-----------------------------------+
| ["a", "b"]                        |
+-----------------------------------+
mysql> select json_keys('{"a":1,"b":{"c":30}}', '$.b');
+------------------------------------------+
| json_keys('{"a":1,"b":{"c":30}}', '$.b') |
+------------------------------------------+
| ["c"]                                    |
+------------------------------------------+

JSON_SEARCH

JSON_SEARCH(json_doc, one_or_all, search_str[, escape_char[, path] ...])

与えられた文字列を含む場合にそのpathを返す。含まない場合はNULLを返す。

one は最初にマッチしたpathを一つ返す。 all はマッチするパス全てを返す。

% と _ はLIKE検索の時に使うオペレータと同等に機能する。 % は複数の文字にマッチし _ は一つの文字にマッチする。

escape_char でエスケープ文字列を指定することができる。指定しない場合は何も記述しないかNULLをパラメータとして渡す。

mysql> set @j='["abc", [{"k":"10"}, "def"], {"x":"abc"}, {"y":"bcd"}]';
mysql> select json_search(@j, 'one', 'abc');
+-------------------------------+
| json_search(@j, 'one', 'abc') |
+-------------------------------+
| "$[0]"                        |
+-------------------------------+
mysql> select json_search(@j, 'all', 'abc');
+-------------------------------+
| json_search(@j, 'all', 'abc') |
+-------------------------------+
| ["$[0]", "$[2].x"]            |
+-------------------------------+
mysql> select json_search(@j, 'all', 'ghi');
+-------------------------------+
| json_search(@j, 'all', 'ghi') |
+-------------------------------+
| NULL                          |
+-------------------------------+
mysql> select json_search('["hoge%%"]', 'all', 'hoge|%|%', '|');
+---------------------------------------------------+
| json_search('["hoge%%"]', 'all', 'hoge|%|%', '|') |
+---------------------------------------------------+
| "$[0]"                                            |
+---------------------------------------------------+
mysql> select json_search(@j, 'all', 'abc');
+-------------------------------+
| json_search(@j, 'all', 'abc') |
+-------------------------------+
| ["$[0]", "$[2].x"]            |
+-------------------------------+
mysql> select json_search(@j, 'all', 'abc', NULL, '$[2]');
+---------------------------------------------+
| json_search(@j, 'all', 'abc', NULL, '$[2]') |
+---------------------------------------------+
| "$[2].x"                                    |
+---------------------------------------------+

JSON_ARRAY_APPEND

指定のpathに与えられたvalueをappendする

mysql5.7ではJSON_APPENDという名前だったが、mysql8ではサポートされていない。

mysql> set @j = '["a", ["b", "c"], "d"]';
mysql> select json_array_append(@j, '$[1]', 1);
+----------------------------------+
| json_array_append(@j, '$[1]', 1) |
+----------------------------------+
| ["a", ["b", "c", 1], "d"]        |
+----------------------------------+
mysql> select json_array_append(@j, '$[2]', 1);
+----------------------------------+
| json_array_append(@j, '$[2]', 1) |
+----------------------------------+
| ["a", ["b", "c"], ["d", 1]]      |
+----------------------------------+
mysql> select json_array_append(@j, '$[0]', 1);
+----------------------------------+
| json_array_append(@j, '$[0]', 1) |
+----------------------------------+
| [["a", 1], ["b", "c"], "d"]      |
+----------------------------------+
mysql> select json_array_append(@j, '$', 1);
+-------------------------------+
| json_array_append(@j, '$', 1) |
+-------------------------------+
| ["a", ["b", "c"], "d", 1]     |
+-------------------------------+
mysql> select json_array_append(@j, '$[1][0]', 1);
+-------------------------------------+
| json_array_append(@j, '$[1][0]', 1) |
+-------------------------------------+
| ["a", [["b", 1], "c"], "d"]         |
+-------------------------------------+
mysql> select json_array_append(@j, '$[1]', json_array(1,2,3));
+--------------------------------------------------+
| json_array_append(@j, '$[1]', json_array(1,2,3)) |
+--------------------------------------------------+
| ["a", ["b", "c", [1, 2, 3]], "d"]                |
+--------------------------------------------------+
mysql> select json_array_append(@j, '$[1]', 1, '$[1]', 2, '$[1]', 3);
+--------------------------------------------------------+
| json_array_append(@j, '$[1]', 1, '$[1]', 2, '$[1]', 3) |
+--------------------------------------------------------+
| ["a", ["b", "c", 1, 2, 3], "d"]                        |
+--------------------------------------------------------+

hashで試す

mysql> set @j = '{"a":1, "b":[2,3], "c":4}';
mysql> select json_array_append(@j, '$.b', 'x');
+------------------------------------+
| json_array_append(@j, '$.b', 'x')  |
+------------------------------------+
| {"a": 1, "b": [2, 3, "x"], "c": 4} |
+------------------------------------+
mysql> select json_array_append(@j, '$.c', 'x');
+--------------------------------------+
| json_array_append(@j, '$.c', 'x')    |
+--------------------------------------+
| {"a": 1, "b": [2, 3], "c": [4, "x"]} |
+--------------------------------------+
mysql> select json_array_append(@j, '$', 'x');
+--------------------------------------+
| json_array_append(@j, '$', 'x')      |
+--------------------------------------+
| [{"a": 1, "b": [2, 3], "c": 4}, "x"] |
+--------------------------------------+

JSON_ARRAY_INSERT

例の最後にあるように、複数のpathとvalueを指定する場合は、最初の修正は後続のpathに影響を与えるので注意が必要。

mysql> set @j = '["a", {"b": [1,2]}, [3,4]]';
mysql> select json_array_insert(@j, '$[1]', 'x');
+------------------------------------+
| json_array_insert(@j, '$[1]', 'x') |
+------------------------------------+
| ["a", "x", {"b": [1, 2]}, [3, 4]]  |
+------------------------------------+
mysql> select json_array_insert(@j, '$[100]', 'x');
+--------------------------------------+
| json_array_insert(@j, '$[100]', 'x') |
+--------------------------------------+
| ["a", {"b": [1, 2]}, [3, 4], "x"]    |
+--------------------------------------+
mysql> select json_array_insert(@j, '$[1].b[0]', 'x');
+-----------------------------------------+
| json_array_insert(@j, '$[1].b[0]', 'x') |
+-----------------------------------------+
| ["a", {"b": ["x", 1, 2]}, [3, 4]]       |
+-----------------------------------------+
mysql> select json_array_insert(@j, '$[2][1]', 'y');
+---------------------------------------+
| json_array_insert(@j, '$[2][1]', 'y') |
+---------------------------------------+
| ["a", {"b": [1, 2]}, [3, "y", 4]]     |
+---------------------------------------+
mysql> select json_array_insert(@j, '$[0]', 'x', '$[2][1]', 'y');
+----------------------------------------------------+
| json_array_insert(@j, '$[0]', 'x', '$[2][1]', 'y') |
+----------------------------------------------------+
| ["x", "a", {"b": [1, 2]}, [3, 4]]                  |
+----------------------------------------------------+

JSON_INSERT

path-value は左から右に評価される。

pathが存在する場合、何もインサートされない。

mysql> set @j = '{"a":1, "b":[2,3]}';
mysql> select json_insert(@j, '$.a', 10);
+----------------------------+
| json_insert(@j, '$.a', 10) |
+----------------------------+
| {"a": 1, "b": [2, 3]}      |
+----------------------------+
mysql> select json_insert(@j, '$.a', 10, '$.c', '[true, false]');
+----------------------------------------------------+
| json_insert(@j, '$.a', 10, '$.c', '[true, false]') |
+----------------------------------------------------+
| {"a": 1, "b": [2, 3], "c": "[true, false]"}        |
+----------------------------------------------------+
mysql> select json_insert(@j, '$.a', 10, '$.c', cast('[true, false]' as json));+------------------------------------------------------------------+
| json_insert(@j, '$.a', 10, '$.c', cast('[true, false]' as json)) |
+------------------------------------------------------------------+
| {"a": 1, "b": [2, 3], "c": [true, false]}                        |
+------------------------------------------------------------------+
mysql> select json_insert(@j, '$.a', 10, '$.c', json_array(true, false));
+------------------------------------------------------------+
| json_insert(@j, '$.a', 10, '$.c', json_array(true, false)) |
+------------------------------------------------------------+
| {"a": 1, "b": [2, 3], "c": [true, false]}                  |
+------------------------------------------------------------+

JSON_MERGE

2個かそれ以上のJSON documentを連結する。 8.0.3で廃止予定になっている。 JSON_MERGE_PRESERVEが代替。

mysql> select json_merge('[1,2]', '3', '[true, false]', '{"a":100}');
+--------------------------------------------------------+
| json_merge('[1,2]', '3', '[true, false]', '{"a":100}') |
+--------------------------------------------------------+
| [1, 2, 3, true, false, {"a": 100}]                     |
+--------------------------------------------------------+
mysql> show warnings\G
*************************** 1. row ***************************
  Level: Warning
   Code: 1287
Message: 'JSON_MERGE' is deprecated and will be removed in a future release. Please use JSON_MERGE_PRESERVE/JSON_MERGE_PATCH instead

JSON_MERGE_PATCH

8.0.3以降でサポートしている関数

最初の引数がobjectでない場合空のobjectつまり '{}' が2番目の引数にマージされるのと同じことになる。

2番めの引数がobjectでない場合、結果は2番目の引数と同じになる。

mysql> select json_merge_patch('[1,2]', '[true,false]');
+-------------------------------------------+
| json_merge_patch('[1,2]', '[true,false]') |
+-------------------------------------------+
| [true, false]                             |
+-------------------------------------------+
mysql> select json_merge_patch('{"name":"x"}', '{"id":47}');
+-----------------------------------------------+
| json_merge_patch('{"name":"x"}', '{"id":47}') |
+-----------------------------------------------+
| {"id": 47, "name": "x"}                       |
+-----------------------------------------------+

mysql> select json_merge_patch('{"a":"b"}', '[1,2]');
+----------------------------------------+
| json_merge_patch('{"a":"b"}', '[1,2]') |
+----------------------------------------+
| [1, 2]                                 |
+----------------------------------------+
mysql> select json_merge_patch('1', 'true');
+-------------------------------+
| json_merge_patch('1', 'true') |
+-------------------------------+
| true                          |
+-------------------------------+
mysql> select json_merge_patch('[1,2]', '{"foo":"bar"}');
+--------------------------------------------+
| json_merge_patch('[1,2]', '{"foo":"bar"}') |
+--------------------------------------------+
| {"foo": "bar"}                             |
+--------------------------------------------+
mysql> select json_merge_patch('{"a":1,"b":2}', '{"a":3,"c":4}');
+----------------------------------------------------+
| json_merge_patch('{"a":1,"b":2}', '{"a":3,"c":4}') |
+----------------------------------------------------+
| {"a": 3, "b": 2, "c": 4}                           |
+----------------------------------------------------+

nullをvalueとして与えることでkeyを削除できる

mysql> select json_merge_patch('{"a":1,"b":2}', '{"b": null}');
+--------------------------------------------------+
| json_merge_patch('{"a":1,"b":2}', '{"b": null}') |
+--------------------------------------------------+
| {"a": 1}                                         |
+--------------------------------------------------+

オブジェクトの構造も辿って修正することができる。

mysql> select json_merge_patch('{"a": {"b":2}}', '{"a": {"b": [1,2,3]}}');
+-------------------------------------------------------------+
| json_merge_patch('{"a": {"b":2}}', '{"a": {"b": [1,2,3]}}') |
+-------------------------------------------------------------+
| {"a": {"b": [1, 2, 3]}}                                     |
+-------------------------------------------------------------+

JSON_MERGE_PRESERVE

8.0.3以降で追加された関数。それ以前の JSON_MERGE は廃止予定

JSON_MERGE_PATCH との比較は下記

mysql> set @x = '{"a":1, "b":2}',
    ->     @y = '{"a":3, "c":4}',
    ->     @z = '{"a":5, "d":6}';
mysql> select json_merge_patch(@x, @y, @z) as patch,
    ->        json_merge_preserve(@x, @y, @z) as preserve\G
*************************** 1. row ***************************
   patch: {"a": 5, "b": 2, "c": 4, "d": 6}
preserve: {"a": [1, 3, 5], "b": 2, "c": 4, "d": 6}

array同士のmergeはひとつのarrayに連結される。

mysql> select json_merge_preserve('[1,2]', '[true, false]');
+-----------------------------------------------+
| json_merge_preserve('[1,2]', '[true, false]') |
+-----------------------------------------------+
| [1, 2, true, false]                           |
+-----------------------------------------------+
1 row in set (0.00 sec)

mysql> select json_merge_preserve('[1,2]', '[true, false, [3,4]]');
+------------------------------------------------------+
| json_merge_preserve('[1,2]', '[true, false, [3,4]]') |
+------------------------------------------------------+
| [1, 2, true, false, [3, 4]]                          |
+------------------------------------------------------+

object同士のmergeはひとつのobjectに統合される。

mysql> select json_merge_preserve('{"name":"x"}', '{"id":47}');
+--------------------------------------------------+
| json_merge_preserve('{"name":"x"}', '{"id":47}') |
+--------------------------------------------------+
| {"id": 47, "name": "x"}                          |
+--------------------------------------------------+
mysql> select json_merge_preserve('{"name":"x"}', '{"name":"x"}');
+-----------------------------------------------------+
| json_merge_preserve('{"name":"x"}', '{"name":"x"}') |
+-----------------------------------------------------+
| {"name": ["x", "x"]}                                |
+-----------------------------------------------------+
mysql> select json_merge_patch('{"name":"x"}', '{"name":"x"}');
+--------------------------------------------------+
| json_merge_patch('{"name":"x"}', '{"name":"x"}') |
+--------------------------------------------------+
| {"name": "x"}                                    |
+--------------------------------------------------+

その他

mysql> select json_merge_preserve('1', 'true');
+----------------------------------+
| json_merge_preserve('1', 'true') |
+----------------------------------+
| [1, true]                        |
+----------------------------------+

mysql> select json_merge_preserve('[1,2]', '{"id": 100}');
+---------------------------------------------+
| json_merge_preserve('[1,2]', '{"id": 100}') |
+---------------------------------------------+
| [1, 2, {"id": 100}]                         |
+---------------------------------------------+

JSON_REMOVE

JSONドキュメントからデータを削除する。

mysql> set @j = '["a", ["b", "c"], "d"]';
mysql> select json_remove(@j, '$[1]');
+-------------------------+
| json_remove(@j, '$[1]') |
+-------------------------+
| ["a", "d"]              |
+-------------------------+

JSON_REPLACE

path-valueが存在しないpathへの置換は無視される。

mysql> set @j = '{"a":1, "b":[2,3]}';
mysql> select json_replace(@j, '$.a', 10, '$.c', '[true, false]');
+-----------------------------------------------------+
| json_replace(@j, '$.a', 10, '$.c', '[true, false]') |
+-----------------------------------------------------+
| {"a": 10, "b": [2, 3]}                              |
+-----------------------------------------------------+

JSON_SET

JSON_SET と JSON_INSERT と JSON_REPLACE との比較は下記

JSON_SETは問答無用で既存のvalueを置き換えてないものは追加する。

JSON_INSERTは既存の置き換えはせずにinsertする。

JSON_REPLACEは既存の置き換えのみをしてinsertはしない。

mysql> set @j = '{"a":1, "b":[2,3]}';
mysql> select json_set(@j, '$.a', 10, '$.c', json_array(true, false));
+---------------------------------------------------------+
| json_set(@j, '$.a', 10, '$.c', json_array(true, false)) |
+---------------------------------------------------------+
| {"a": 10, "b": [2, 3], "c": [true, false]}              |
+---------------------------------------------------------+
mysql> select @j;
+--------------------+
| @j                 |
+--------------------+
| {"a":1, "b":[2,3]} |
+--------------------+

mysql> select json_insert(@j, '$.a', 10, '$.c', json_array(true, false));
+------------------------------------------------------------+
| json_insert(@j, '$.a', 10, '$.c', json_array(true, false)) |
+------------------------------------------------------------+
| {"a": 1, "b": [2, 3], "c": [true, false]}                  |
+------------------------------------------------------------+
mysql> select @j;
+--------------------+
| @j                 |
+--------------------+
| {"a":1, "b":[2,3]} |
+--------------------+

mysql> select json_replace(@j, '$.a', 10, '$.c', json_array(true, false));
+-------------------------------------------------------------+
| json_replace(@j, '$.a', 10, '$.c', json_array(true, false)) |
+-------------------------------------------------------------+
| {"a": 10, "b": [2, 3]}                                      |
+-------------------------------------------------------------+
mysql> select @j;
+--------------------+
| @j                 |
+--------------------+
| {"a":1, "b":[2,3]} |
+--------------------+

JSON_UNQUOTE

JSON valueをUNQUOTEして utf8mb4 文字列を返す。

mysql> set @x = '"abc"',
    ->     @y = '[1,2,3]';
mysql> select json_unquote(@x), 
    ->        json_valid(json_unquote(@x)),
    ->        json_unquote(@y),
    ->        json_valid(json_unquote(@y))\G
*************************** 1. row ***************************
            json_unquote(@x): abc
json_valid(json_unquote(@x)): 0
            json_unquote(@y): [1,2,3]
json_valid(json_unquote(@y)): 1

JSON_DEPTH

JSONドキュメントの最大の深さを返す。

mysql> select json_depth('{}'), json_depth('[]'), json_depth('true');
+------------------+------------------+--------------------+
| json_depth('{}') | json_depth('[]') | json_depth('true') |
+------------------+------------------+--------------------+
|                1 |                1 |                  1 |
+------------------+------------------+--------------------+
mysql> select json_depth('{"a":1}'), json_depth('[1]'), json_depth('true');
+-----------------------+-------------------+--------------------+
| json_depth('{"a":1}') | json_depth('[1]') | json_depth('true') |
+-----------------------+-------------------+--------------------+
|                     2 |                 2 |                  1 |
+-----------------------+-------------------+--------------------+
mysql> select json_depth('{"a":[1,2,3]}'), json_depth('[1]'), json_depth('true');
+-----------------------------+-------------------+--------------------+
| json_depth('{"a":[1,2,3]}') | json_depth('[1]') | json_depth('true') |
+-----------------------------+-------------------+--------------------+
|                           3 |                 2 |                  1 |
+-----------------------------+-------------------+--------------------+

JSON_LENGTH

ドキュメントの長さを返す。

mysql> select json_length('[1,2,{"a":3}]');
+------------------------------+
| json_length('[1,2,{"a":3}]') |
+------------------------------+
|                            3 |
+------------------------------+
mysql> select json_length('{"a":1, "b":{"c":2}}');
+-------------------------------------+
| json_length('{"a":1, "b":{"c":2}}') |
+-------------------------------------+
|                                   2 |
+-------------------------------------+
mysql> select json_length('{"a":1, "b":{"c":2}}', '$.b');
+--------------------------------------------+
| json_length('{"a":1, "b":{"c":2}}', '$.b') |
+--------------------------------------------+
|                                          1 |
+--------------------------------------------+

JSON_TYPE

JSON valueのTypeを utf8mb4 で返す。

mysql> set @j='{"a":[10,true]}';
mysql> select json_type(@j);
+---------------+
| json_type(@j) |
+---------------+
| OBJECT        |
+---------------+
mysql> select json_type(json_extract(@j, '$.a'));
+------------------------------------+
| json_type(json_extract(@j, '$.a')) |
+------------------------------------+
| ARRAY                              |
+------------------------------------+
mysql> select json_type(json_extract(@j, '$.a[0]'));
+---------------------------------------+
| json_type(json_extract(@j, '$.a[0]')) |
+---------------------------------------+
| INTEGER                               |
+---------------------------------------+
mysql> select json_type(json_extract(@j, '$.a[1]'));
+---------------------------------------+
| json_type(json_extract(@j, '$.a[1]')) |
+---------------------------------------+
| BOOLEAN                               |
+---------------------------------------+
mysql> select json_type(NULL);
+-----------------+
| json_type(NULL) |
+-----------------+
| NULL            |
+-----------------+
mysql> select json_quote("1");
+-----------------+
| json_quote("1") |
+-----------------+
| "1"             |
+-----------------+
mysql> select json_valid(json_quote("1"));
+-----------------------------+
| json_valid(json_quote("1")) |
+-----------------------------+
|                           1 |
+-----------------------------+
mysql> select json_type(json_quote("1"));
+----------------------------+
| json_type(json_quote("1")) |
+----------------------------+
| STRING                     |
+----------------------------+

JSON_VALID

validなJSONなら1を返す

mysql> select json_valid('{"a": "b}');
+-------------------------+
| json_valid('{"a": "b}') |
+-------------------------+
|                       0 |
+-------------------------+
mysql> select json_valid('{"a": "b"}');
+--------------------------+
| json_valid('{"a": "b"}') |
+--------------------------+
|                        1 |
+--------------------------+

JSON_TABLE

JSONデータを表形式に変換する関数。

現時点ではこの記事では取り上げない。

公式のサンプルはここ

JSON_PRETTY

良い感じに整形してくれる。

mysql> select json_pretty('{"a": {"b": {"c": [1,2,{"d": [4,5,6]}]}}}')\G
*************************** 1. row ***************************
json_pretty('{"a": {"b": {"c": [1,2,{"d": [4,5,6]}]}}}'): {
  "a": {
    "b": {
      "c": [
        1,
        2,
        {
          "d": [
            4,
            5,
            6
          ]
        }
      ]
    }
  }
}

JSON_STORAGE_FREE

JSON_SET, JSON_REPLACE, JSON_REMOVEを使ってカラムをアップデートした後にどれくらいのストレージが空いたかを確認することができる。

これらの関数を使わないでカラムをアップデートした後はpartial updateにならないのでJSON_STORAGE_FREEの戻り値は0となる。

mysql> create table jtable(jcol JSON);
mysql> insert into jtable values ('{"a":10, "b": "xyz", "c": "[true, false]"}');
mysql> select * from jtable;
+---------------------------------------------+
| jcol                                        |
+---------------------------------------------+
| {"a": 10, "b": "xyz", "c": "[true, false]"} |
+---------------------------------------------+
mysql> select json_storage_free(jcol) from jtable;
+-------------------------+
| json_storage_free(jcol) |
+-------------------------+
|                       0 |
+-------------------------+
mysql> update jtable set jcol=json_set(jcol, "$.a", 10, "$.b", "xyz", "$.c", 1);
mysql> select * from jtable;
+-------------------------------+
| jcol                          |
+-------------------------------+
| {"a": 10, "b": "xyz", "c": 1} |
+-------------------------------+
mysql> select json_storage_free(jcol) from jtable;
+-------------------------+
| json_storage_free(jcol) |
+-------------------------+
|                      14 |
+-------------------------+

普通にアップデートすると下記のようになる。

mysql> update jtable set jcol='{"a":10,"b":20}';
mysql> select json_storage_free(jcol) from jtable;
+-------------------------+
| json_storage_free(jcol) |
+-------------------------+
|                       0 |
+-------------------------+

JSON_STORAGE_SIZE

JSON documentが使用しているバイト数を返す

mysql> create table jtable(jcol JSON);
mysql> insert into jtable values('{"a": 1000, "b": "wxyz", "c": "[1,3,5,7]"}');
mysql> select jcol, json_storage_size(jcol) as size, json_storage_free(jcol) as free from jtable;
+--------------------------------------------+------+------+
| jcol                                       | size | free |
+--------------------------------------------+------+------+
| {"a": 1000, "b": "wxyz", "c": "[1,3,5,7]"} |   44 |    0 |
+--------------------------------------------+------+------+
mysql> update jtable set jcol=json_set(jcol, "$.b", "a");
mysql> select jcol, json_storage_size(jcol) as size, json_storage_free(jcol) as free from jtable;
+-----------------------------------------+------+------+
| jcol                                    | size | free |
+-----------------------------------------+------+------+
| {"a": 1000, "b": "a", "c": "[1,3,5,7]"} |   44 |    3 |
+-----------------------------------------+------+------+

originはこちら

24
31
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
24
31

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?