mysqlのjson型でcrud操作してみる。

参考URL:
https://zenn.dev/rescuenow/articles/854cf493737672

MySQL8.0でJSONの配列にインデックスを使う

json型でcrud操作

-- テーブル定義
CREATE TABLE STORE_TBL (
   ID    INT,
   STORE JSON,
   PRIMARY KEY (ID)
);

-- レコード生成
INSERT INTO STORE_TBL VALUES (1, '{"store_cd":"001", "store_nm":"東店", "staff":{"fulltime":["sato", "hayashi"], "parttime":["shimizu", "ogawa"]}}');
INSERT INTO STORE_TBL VALUES (2, '{"store_cd":"002", "store_nm":"北店", "staff":{"fulltime":["suzuki", "kimura"], "parttime":["nakamura"]}}');
INSERT INTO STORE_TBL VALUES (3, '{"store_cd":"003", "store_nm":"西店", "staff":{"fulltime":["tanaka", "sasaki"]}}');


-- SELECTで、取り出す項目の指定方法は、JSON_EXTRACT(JSONカラム名, '$.項目名')
SELECT JSON_EXTRACT(STORE, '$.store_nm') FROM STORE_TBL;
SELECT STORE->"$.store_nm" FROM STORE_TBL; -- カラム名->"$.項目名" でもOK

-- ダブルクオーテーションを外した状態で取得するには、JSON_UNQUOTE
SELECT JSON_UNQUOTE(STORE->"$.store_nm") FROM STORE_TBL;

-- WHERE項目の指定方法は、JSON_EXTRACT(JSONカラム名, '$.項目名') = '検索する値' 
select JSON_EXTRACT(STORE, '$.staff') FROM STORE_TBL WHERE JSON_EXTRACT(STORE, '$.store_cd') = '002';

-- キーと値で検索は、JSON_CONTAINS
SELECT JSON_EXTRACT(STORE, '$.store_nm') FROM STORE_TBL WHERE JSON_CONTAINS(STORE, '"kimura"', '$.staff.fulltime');



-- 項目の更新はJSON_SET()を使う。store_cd="002"のstaff.parttimeを'saito'に更新する
UPDATE STORE_TBL SET STORE = JSON_SET(STORE, '$.staff.parttime[0]', 'saito') WHERE JSON_EXTRACT(STORE, '$.store_cd') = "002";

-- 項目の追加はJSON_ARRAY_APPEND()を使う。
UPDATE STORE_TBL SET STORE = JSON_ARRAY_APPEND(STORE, '$.staff.parttime', 'nakano') WHERE JSON_EXTRACT(STORE, '$.store_cd') = "002";

-- 項目の削除は、JSON_REMOVE()
UPDATE STORE_TBL
SET STORE = JSON_REMOVE(STORE, '$.staff.parttime[1]')
WHERE JSON_EXTRACT(STORE, '$.store_cd') = "002";

-- テーブル定義

CREATE TABLE STORE_TBL (

ID INT,

STORE JSON,

PRIMARY KEY (ID)

);

-- レコード生成

INSERT INTO STORE_TBL VALUES (1, '{"store_cd":"001", "store_nm":"東店", "staff":{"fulltime":["sato", "hayashi"], "parttime":["shimizu", "ogawa"]}}');

INSERT INTO STORE_TBL VALUES (2, '{"store_cd":"002", "store_nm":"北店", "staff":{"fulltime":["suzuki", "kimura"], "parttime":["nakamura"]}}');

INSERT INTO STORE_TBL VALUES (3, '{"store_cd":"003", "store_nm":"西店", "staff":{"fulltime":["tanaka", "sasaki"]}}');

-- SELECTで、取り出す項目の指定方法は、JSON_EXTRACT(JSONカラム名, '$.項目名')

SELECT JSON_EXTRACT(STORE, '$.store_nm') FROM STORE_TBL;

SELECT STORE->"$.store_nm" FROM STORE_TBL; -- カラム名->"$.項目名" でもOK

-- ダブルクオーテーションを外した状態で取得するには、JSON_UNQUOTE

SELECT JSON_UNQUOTE(STORE->"$.store_nm") FROM STORE_TBL;

-- WHERE項目の指定方法は、JSON_EXTRACT(JSONカラム名, '$.項目名') = '検索する値'

select JSON_EXTRACT(STORE, '$.staff') FROM STORE_TBL WHERE JSON_EXTRACT(STORE, '$.store_cd') = '002';

-- キーと値で検索は、JSON_CONTAINS

SELECT JSON_EXTRACT(STORE, '$.store_nm') FROM STORE_TBL WHERE JSON_CONTAINS(STORE, '"kimura"', '$.staff.fulltime');

-- 項目の更新はJSON_SET()を使う。store_cd="002"のstaff.parttimeを'saito'に更新する

UPDATE STORE_TBL SET STORE = JSON_SET(STORE, '$.staff.parttime[0]', 'saito') WHERE JSON_EXTRACT(STORE, '$.store_cd') = "002";

-- 項目の追加はJSON_ARRAY_APPEND()を使う。

UPDATE STORE_TBL SET STORE = JSON_ARRAY_APPEND(STORE, '$.staff.parttime', 'nakano') WHERE JSON_EXTRACT(STORE, '$.store_cd') = "002";

-- 項目の削除は、JSON_REMOVE()

UPDATE STORE_TBL

SET STORE = JSON_REMOVE(STORE, '$.staff.parttime[1]')

WHERE JSON_EXTRACT(STORE, '$.store_cd') = "002";

関連記事