Lindorm:JSONデータ型

上記の例では、tbテーブルには、データ型がINTのプライマリキー列p1、データ型がVARCHARの非プライマリキー列c1、テーブルの作成時にデータ型がJSONに設定された非プライマリキー列c2、およびc3列がテーブルに追加されたときにデータ型がJSONに設定された非プライマリキー列c3が含まれています。次のステートメントを実行して、tbテーブルのスキーマをクエリできます。

DESCRIBE tb;

返された結果：

+--------------+---------------------+------------------------+---------+----------------+------------+
| TABLE_SCHEMA |     TABLE_NAME      |      COLUMN_NAME       |  TYPE   | IS_PRIMARY_KEY | SORT_ORDER |
+--------------+---------------------+------------------------+---------+----------------+------------+
| default      | tb                  | p1                     | INT     | true           | ASC        |
| default      | tb                  | c1                     | VARCHAR | false          | none       |
| default      | tb                  | c2                     | JSON    | false          | none       |
| default      | tb                  | c3                     | JSON    | false          | none       |
+--------------+---------------------+------------------------+---------+----------------+------------+

次の3つの方法のいずれかを使用して、JSON列にデータを書き込むことができます。JSONオブジェクトまたはJSON文字列のみを、データ型がJSONの列に書き込むことができます。それ以外の場合、データを書き込むときにエラーが発生します。LindormTable SQLは、json_object関数とjson_array関数を用意しており、テーブルに書き込まれるデータをJSONオブジェクトに変換できます。

• JSON列にJSON文字列を書き込みます。次の方法を使用して、JSON文字列をJSON列に書き込むことができます。

  • Statement()メソッドを使用してSQLステートメントを実行します。この方法で、JSON文字列をJSON列に書き込むことができます。

    Connection conn = DriverManager.getConnection("Lindorm URL", properties);
    Statement stmt = conn.createStatement();
    String jsonStr1 = "{\"k1\":4,\"k2\":{\"k3\":{\"k4\":4}}}"; 
    String upsertSQL = "UPSERT INTO tb(p1, c1, c2) VALUES(1, '1', '"+ jsonStr1 + "')"; 
    // テーブルに書き込まれる列の数を返します。
    int ret = stmt.executeUpdate(upsertSQL);

  • PrepareStatement()メソッドを使用してSQLステートメントを実行します。この方法では、SQLステートメントが前処理され、SQLテンプレートのパラメーターに値が指定されます。

    String jsonStr1 = "{\"k1\":4,\"k2\":{\"k3\":{\"k4\":4}}}";
    // データの書き込みに使用するSQLテンプレートを指定します。
    String upsertSQL = "UPSERT INTO tb(p1, c1, c2) VALUES(1, '1', ?)";
    PreparedStatement preStmt = conn.prepareStatement(upsertSQL);
    // JSON文字列をJSON列に書き込みます。
    preStmt.setString(1, jsonStr1);
    int ret = stmt.executeUpdate();

• json_object関数を使用して、書き込まれたデータを書き込み順に基づいてキーと値のペアで構成されるJSONオブジェクトに変換し、JSONオブジェクトをJSON列に書き込みます。

  String upsert = "UPSERT INTO tb(p1,c1,c2) VALUES(2,'2',json_object('k1', 2, 'k2', '2'))";

  LindormTable SQLを使用して、上記のステートメントで指定されたデータを書き込むには、次のステートメントを実行します。

  UPSERT INTO tb(p1,c1,c2) VALUES(2,'2','{"k1":2,"k2":"2"}');

• json_array関数を使用して、書き込まれたデータを書き込み順に基づいて配列形式のJSONオブジェクトに変換し、JSONオブジェクトをJSON列に書き込みます。

  String  upsert = "UPSERT INTO " + tableName + "(p1,c1,c2) VALUES(3,'3', json_array(1, 2, json_object('k1', 3, 'k2', '3')))";

  LindormTable SQLを使用して、上記のステートメントで指定されたデータを書き込むには、次のステートメントを実行します。

  UPSERT INTO tb(p1,c1,c2) VALUES(3,'3','[1,2,{"k1":3,"k2":"3"}]');

結果の確認

次のステートメントを実行して、テーブルに書き込まれたデータを表示します。

SELECT * FROM tb;

LindormTable SQLのjson_extract関数は、MySQLの関数と同様に使用できます。json_extract関数は、SELECT句またはWHERE句で使用できます。

• json_extract関数がSELECT句で使用される場合、関数はJSON列の値を取得し、値をユーザーに返します。例：

  • JSON列に次のデータを書き込みます："{\"k1\":1}"。

    String json = "{\"k1\":1}";
    // SELECT句で json_extract 関数を使用して、c2 列の k1 の値を返し、結果を格納する列の名前を j に設定します。
    String select = "select p1, c1, c2, json_extract(c2, '$.k1') j from tb where p1 = 1";
    ResultSet resultSet = stmt.executeQuery(select);
    resultSet.next();
    String resultC2 = resultSet.getString(c2);
    // resultC2 は c2 列を示します。
    String resultC2k1 = resultSet.getString("j");
    // resultC2k1 は c2 列の k1 の値（1）を示します。

  • JSON列に次のデータを書き込みます："{\"k1\":2,\"k2\":\"2\"}"。

    String json ="{\"k1\":2,\"k2\":\"2\"}";
    // SELECT句で json_extract 関数を使用して、c2 列の k2 の値を返し、結果を格納する列の名前を j に設定します。
    String select =  "select p1, c1, c2, json_extract(c2, '$.k2') j from tb where p1 = 2";
    ResultSet resultSet = stmt.executeQuery(select);
    resultSet.next();
    String resultC2 = resultSet.getString(c2);
    // resultC2 は c2 列を示します。
    String resultC2k1 = resultSet.getString("j");
    // resultC2k1 は c2 列の k2 の値（2）を示します。

  • JSON列に次のデータを書き込みます："[1,2,{\"k1\":3,\"k2\":\"3\"}]"。

    String json ="[1,2,{\"k1\":3,\"k2\":\"3\"}]";
    // SELECT句で json_extract 関数を使用して、c2 列のJSON配列の2番目のインデックスにある k2 の値を返し、結果を格納する列の名前を j に設定します。
    String "select json_extract(c2, '$[2].k2') j from  tb where p1 = 3";
    ResultSet resultSet = stmt.executeQuery(select);
    resultSet.next();
    String resultC2 = resultSet.getString(c2);
    // resultC2 は c2 列を示します。
    String resultC2k1 = resultSet.getString("j");
    // resultC2k1 は c2 列のJSON配列の2番目のインデックスにある k2 の値（3）を示します。

• json_extract関数がWHERE句で使用される場合、関数はJSON列の値を取得し、値をフィルタリングします。json_extract関数がWHERE句でデータの比較とフィルタリングに使用される場合、LindormTable SQLで異なるタイプのデータを比較するために使用されるメソッドは、MySQLで使用されるメソッドと同じです。詳細については、JSONデータ型をご参照ください。

  • JSON列に次のデータを書き込みます："{\"k1\":2,\"k2\":\"2\"}"。

    String json =  "{\"k1\":2,\"k2\":\"2\"}";
    // WHERE句で json_extract 関数を使用して、c2 列の k2 の値が 0 より大きいデータ行を返します。
    String select = "select p1, c1, c2 from tb where where p1 >= 1 and p1 < 4 and json_extract(c2, '$.k2') > '0'";
    ResultSet resultSet = stmt.executeQuery(select);
    resultSet.next();
    String resultC2 = resultSet.getString(c2);
    // resultC2 は c2 列を示します。

  • JSON列に次のデータを書き込みます："{\"k1\":4,\"k2\":{\"k3\":{\"k4\":4}}}"。

    String json = "{\"k1\":4,\"k2\":{\"k3\":{\"k4\":4}}}";
    // WHERE句で json_extract 関数を使用して、c2 列の '$.k2.k3.k4' パスの値が 4 より大きいデータ行を返します。
    String select = "select * from tb where p1 >= 4 and p1 < 6 and json_extract(c2, '$.k2.k3.k4') > 4";
    ResultSet resultSet = stmt.executeQuery(select);
    resultSet.next();
    String resultC2 = resultSet.getString(c2);
    // resultC2 は c2 列を示します。

  • JSON列に次のデータを書き込みます："[1,2,{\"k1\":3,\"k2\":\"3\"}]"。

    String json = "[1,2,{\"k1\":3,\"k2\":\"3\"}]";
    // WHERE句で json_extract 関数を使用して、c2 列のJSON配列の2番目のインデックスにある k2 の値が 0 より大きいデータ行を返します。
    String select = "select * from  tb where p1 >= 1 and p1 < 4 and json_extract(c2, '$[2].k2') > '0'";
    ResultSet resultSet = stmt.executeQuery(select);
    resultSet.next();
    String resultC2 = resultSet.getString(c2);
    // resultC2 は c2 列を示します。

JSON列の指定されたパスのデータを更新するには、UPDATE構文を使用する必要があります。 UPDATE構文は、次の操作をサポートしています。

• JSON_SET：JSON列の指定されたパスの値を更新するか、JSON列の新しいパスとパスの値を追加します。

  例：c2列にk1.k2パスが存在する場合、k1.k2パスの値はvalueに更新されます。 c2列にk1.k2パスが存在しない場合、k1.k2パスが追加され、その値はvalueに設定されます。 k1.k2パスを追加する前に、k1パスが存在することを確認してください。

  UPDATE tb SET c2 = JSON_SET(c2, '$.k1.k2', 'value') WHERE p1 = 2;

• JSON_INERT：JSON列に存在しないパスに値を追加します。

  例：c2列にk1.k2パスが存在しない場合、k1.k2パスの値はnvalueに設定されます。パスに値を追加する前に、k1パスが存在することを確認してください。

  UPDATE tb SET c2 = JSON_INSERT(c2 ,'$.k1.k2' ,'nvalue') WHERE p1 = 2;

• JSON_REPLACE：JSON列の既存のパスの値を更新します。

  例：c2列にk1パスが存在する場合、k1パスの値はnvalueに更新されます。

  UPDATE tb SET c2 = JSON_REPLACE(c2 ,'$.k1' ,'nvalue') WHERE p1 = 2;

• JSON_REMOVE：JSON列の既存のパスとパスの値を削除します。

  例：c2列にk1パスが存在する場合、k1パスとパスの値が削除されます。

  UPDATE tb SET c2 = JSON_REMOVE(c2 , '$.k1') WHERE p1 = 2;

結果の確認

次のステートメントを実行して、更新されたデータを表示します。

SELECT * FROM tb;