Db2でJSONデータを扱う

はじめに

Db2では、バージョン11.1.4.4からSQL による JSON データとの対話を向上させる新規の組み込みの JSON SQL 関数一式が提供されています。
それにより、SQL を使用して JSON データを直接保管および検索することができるようになりました。

使用方法を以下に記載します。

なお、環境としては、IBM Cloud上のマネージドのDB2サービスで確認しています。
IBM CloudではLite Planという、クレジットカードの登録なしで、無料で使用できる環境がありますので、お気軽にお試しください。

参考資料：IBM Db2 マニュアル
JSON ドキュメントへの SQL アクセス

1. テーブル定義

ここでは以下のような社員情報を格納することを想定します。
EMPOLYEEIDとNAMEは通常のデータで、PROFILE欄にJSONデータを格納します。

EMPLOYEEID	NAME	PROFILE
100	Tom	{hobby: "baseball", license: "driver"}
101	Ken	{hobby: "football"}
101	Jhon	{license: "CPA"}

JSONデータを格納する列PROFILEはBlob型で定義します。

CERATE TABLE EMPLOYEES (
    EMPLOYEEID integer primary key not null,
    NAME varchar(10) not null,
    PROFILE Blob(1000)
);

2. レコード登録

JSONデータを格納する際は、SYSTOOLSスキーマのJSON2BSON関数をつかって格納します。

INSERT INTO TESTJSON VALUES (100, "Tom", SYSTOOLS.JSON2BSON('{hobby: "baseball", license: "driver"}'));

3. レコード検索

取り出すときは、SYSTOOLSスキーマのBSON2JSON関数をつかって取り出します。

SELECT EMPLOYEEID, NAME, SYSTOOLS.BSON2JSON(PROFILE) as PROFILE FROM EMPLOYEES;

JSONの中身を条件に抽出したい場合は、JSON_VAL関数を使用します。

(例)JSONのhobbyキーにbaseballが入っている人を抽出
SELECT EMPLOYEEID, NAME, SYSTOOLS.BSON2JSON(PROFILE) as PROFILE FROM EMPLOYEES WHERE SYSTOOLS.JSON_VAL(PROFILE, 'hobby','s:20') = 'baseball';

※JSON_VALの引数には、JSONが入っている列名、検索するJSONのキー、戻り値の型（例の's:20'は最大20文字の文字列として返すという意味です。）を指定します。

4. レコード更新

JSONの中身を更新した場合はUPDATE分で、JSONの値を変更しないキーも含めて、すべての情報のJSONデータで上書きします。

(例) Tomのホビーを、tennisに書き換える
UPDATE EMPLOYEES SET PROFILE = SYSTOOLS.JSON2BSON('{hobby: "tennis", license: "driver"}') WHERE NAME = 'Tom';