mysql8.0以降に追加されたJSON型 #MySQL

nosql と同じ使用感で利用可能
カラムごとに使用でき、CREATE文でデータ型をJSONと記述するだけ

CREATE文

CREATE TABLE `test` (
  `id` int(11) NOT NULL COMMENT '謎ID' AUTO_INCREMENT PRIMARY KEY,
  `pattern` char(3) NOT NULL COMMENT 'pattern:問題パターン(A,B,C,D)',
  `format_or_hint` JSON NOT NULL COMMENT '[format:回答形式(手入力), hint:解答群]',
  `images` JSON NOT NULL COMMENT '問題画像[ラベル, ファイルパス]',
  `answer` JSON NOT NULL COMMENT '[こたえ群]'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT '謎';

INSERT文

JSON_OBJECT関数

key-value のデータを格納

引数の個数は必ず偶数個
引数の奇数番目と偶数番目で JSON object となる

JSON_OBJECT("key1", "val1") == {"key1", "val1"}

JSON_ARRAY関数

配列データを格納

JSON_ARRAY("apple", "orange") == ["apple", "orange"]

insert into test values(1, 'A', JSON_OBJECT('format','ひらがな'), JSON_ARRAY('q001.png'), JSON_ARRAY('とけい'));
insert into test values(2, 'A', JSON_OBJECT('format','カタカナ'), JSON_ARRAY('q002-1.png', 'q002-1.png'), JSON_ARRAY('カキ','ｶｷ'));
insert into test (pattern, format_or_hint, images, answer)
  values('B', JSON_OBJECT("format","半角英数字"), JSON_OBJECT("A","B1-1.jpg","B","B1-2.jpg","LAST","B1-3.jpg"), JSON_ARRAY("ジャマイカ"));
insert into test (pattern, format_or_hint, images, answer)
  values('D', JSON_ARRAY('ひかり','こだま','つばさ','あさひ'), JSON_ARRAY('DDD.gif'), JSON_ARRAY('こだま'));

関数を使用しない場合

insert into test (pattern, format_or_hint, images, answer)
  values('A', '{"format":"漢字"}', '["abc.png"]', '["東京都"]');
insert into test (pattern, format_or_hint, images, answer)
  values('C', '{"format":"数字"}', '["def.png","gg.png","hh.png"]', '["59","５９"]');

SELECT文

-- SELECT JSON_OBJECT(Q_answer, '') from test;
-- SELECT JSON_EXTRACT(Q_answer, '') from test;
SELECT * FROM test;
SELECT Q_answer FROM test;
-- 結局文字列で取得なので、いつも通りでよい。

JSON 内部の検索

JSON_CONTAINS(target, candidate[, path])

指定値を含むか判定

JSON_CONTAINS_PATH(json_doc, one_or_all, path[, path] ...)

指定パスを含むか判定

JSON_EXTRACT(json_doc, path[, path] ...)

指定パスのデータ抽出

select images, json_extract(images, '$') from test;
select images, json_extract(images, '$[0]') from test;
select format_or_hint, json_extract(format_or_hint, '$.format') from test;
select * from test where json_extract(format_or_hint, '$.format') = '漢字';
select * from test where json_extract(format_or_hint, '$.format') like '%字%';

column->path

JSON_EXTRACT(column, path) と同等

column->>path

JSON_UNQUOTE(JSON_EXTRACT(column, path)) と同等
JSON_UNQUOTE(column -> path) と同等

JSON_KEYS(json_doc[, path])

キーだけを抽出

select format_or_hint, json_keys(format_or_hint) from test;

JSON_OVERLAPS(json_doc1, json_doc2)

2つの JSON ドキュメントを比較

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

指定値が含まれるパスを検索

select format_or_hint, json_search(format_or_hint, 'all', '漢字') from test;

JSON_VALUE(json_doc, path)

値だけを抽出

select format_or_hint, json_value(format_or_hint, '$.format') from test;

UPDATE文

配列を追加する

JSON_ARRAY_APPEND(json_doc, path, val[, path, val] ...)

json_doc : 追加する元データの列名
path : $を起点に配置したい階層等を指定 ($, $[1], $[1][0])
val : 追加するデータ

UPDATE test SET images = JSON_ARRAY_APPEND(images, '$', 'hyoo.jpg') WHERE id=7;
-- ["abc.png"] → ["abc.png", "hyoo.jpg"]
UPDATE test SET images = JSON_ARRAY_APPEND(images, '$[1]', 'hyoo.jpg') WHERE id=7;
-- ["abc.png", "hyoo.jpg"] → ["abc.png", ["hyoo.jpg", "hyoo.jpg"]]
UPDATE test SET images = JSON_ARRAY_APPEND(images, '$[0]', 'hyoo.jpg') WHERE id=7;
-- ["abc.png"] → [["abc.png", "hyoo.jpg"]]

配列に要素を追加する

JSON_ARRAY_INSERT(json_doc, path, val[, path, val] ...)

json_doc : 追加する元データの列名
path : $を起点に配置したい階層等を指定 (\$[0], \$[99], \$[1][0])
val : 追加するデータ

UPDATE test SET images = JSON_ARRAY_INSERT(images, '$[1]', 'hyoo.jpg') WHERE id=7;
-- ["abc.png"] → ["abc.png", "hyoo.jpg"]

json要素を追加する

JSON_INSERT(json_doc, path, val[, path, val] ...)

json_doc : 追加する元データの列名
path : key
val : value

UPDATE test SET format_or_hint = JSON_INSERT(format_or_hint, '$.test', 'kyu') WHERE id=7;
-- {"format": "漢字"} → {"format": "漢字", "test": "kyu"}

json要素を結合する

JSON_MERGE(json_doc, json_doc[, json_doc] ...)

SELECT JSON_MERGE('[1,2]', '["i", "ro"]');
-- [1, 2, "i", "ro"]
UPDATE test SET answer = JSON_MERGE(answer, '["169"]') WHERE id=8;
-- ["59", "５９"] → ["59", "５９", "169"]
SELECT JSON_MERGE('{"name": "x"}', '{"id": 47}');
-- {"name": "x", "id": 47}

頭パンクしますよ

JSON_MERGE_PATCH()
JSON_MERGE_PRESERVE()

json要素を削除する

JSON_REMOVE(json_doc, path[, path] ...)

SELECT JSON_REMOVE('["a", ["b", "c"], "d"]', '$[1]');
-- ["a", "d"]

json要素を置換する

JSON_REPLACE(json_doc, path, val[, path, val] ...)

SELECT JSON_REPLACE('{ "a": 1, "b": [2, 3]}', '$.a', 10 '$.c', '[true, false]');
-- {"a": 10, "b": [2, 3]}

json要素を設置する

JSON_SET(json_doc, path, val[, path, val] ...)

SELECT JSON_SET('{ "a": 1, "b": [2, 3]}', '$.a', 10 '$.c', '[true, false]');
-- {"a": 10, "b": [2, 3], "c": "[true, false]"}

さっぱり意味不明

JSON_UNQUOTE()

参考文献

NoSQLどんなデータが向いている？
NoSQLとは？RDBMSと比較しながら解説
 MySQLでNoSQLを使ってみる
 JSON を扱う - MariaDB Server
mysqlのjson型のupdate、insert
MySQL 8.0 リファレンスマニュアル JSON 値を変更する関数
 JSON型の使い方(検索, 抽出, 挿入, 更新, 置換)
MySQL 8.0 リファレンスマニュアル JSON 値を検索する関数