JSON値の作成

JSON値は、オブジェクト（JSONオブジェクト）、配列、文字列、数値、ブール値（false/true）、またはnullで構成されていなければなりません。false、true、nullは小文字のみを許容します。

JSONテキスト構造

JSONテキスト構造には、文字、文字列、数値、および3つのリテラル名が含まれます。任意の構造文字の前後には、スペース、水平タブ、改行、キャリッジリターンなど、さまざまな区切り文字を使用できます。

       配列の開始 =  [ 左角括弧

       オブジェクトの開始 =  { 左大括弧

       配列の終了 =  ] 右角括弧

       オブジェクトの終了 =  } 右大括弧

       名前の区切り文字 = ： コロン

       値の区切り文字 = , コンマ

オブジェクト

オブジェクトの構造は、0個以上の名前/値ペア（またはメンバー）を含む一対の大括弧として表されます。オブジェクト内の名前は一意である必要があります。名前は文字列であり、各名前の後にはコロンが続き、名前と値を区切ります。複数の名前/値を分割するには、単一のカンマを使用します。 例：

{ "NAME": "SAM",  "Height": 175, "Weight":  100，"Registered" : false}

配列

配列の構造は、0個以上の値（要素とも呼ばれる）を含む一つの角括弧として表されます。配列の要素はカンマで区切られ、配列内の値が同一である必要はありません。

例：

["abc", 10, null, true, false]

数値

数値は10進形式を使用し、整数部を含み、前に（オプション）マイナス記号（-）を付けることができます。その後に小数部と/または指数部を続けることができます。先頭のゼロは許可されません。小数部は小数点の後に1桁または複数の数字が続きます。指数部は大文字または小文字のEで始まり、その後にプラス記号（+）またはマイナス記号（-）を続けることができます。Eとオプションの記号の後には、1桁または複数の数字を続けることができます。

例：

[100, 0, -100, 100.11, -12.11, 10.22e2, -10.22e2]

文字列

文字列は、開始と終了の両方に引用符（"）を使用します。すべてのUnicode文字は引用符内に配置できますが、エスケープする必要がある文字（引用符、バックスラッシュ、制御文字を含む）は例外です。

JSONテキストはUTF-8、UTF-16、またはUTF-32でエンコードする必要があります。デフォルトのエンコーディングはUTF-8です。

例：

{"Url":    "http://www.example.com/image/481989943"}

JSON値の作成

OceanBaseデータベースは、JSON型に対して以下のDDL操作をサポートしています：

• JSON列を含むテーブルの作成。

• JSON列の追加/削除。

• 生成列を基にJSON型の列にインデックスを作成する。

• MySQLテナントでのテーブル作成時に半構造化エンコーディングを有効にすることをサポートします。

• MySQLテナントで既存のテーブルを変更して半構造化エンコーディングを有効にすることをサポートします。

制限事項

ユーザーは各テーブルに複数のJSON型列を作成できますが、以下の制限があります：

• JSON型列はPRIMARY KEY、FOREIGN KEY、およびUNIQUE KEYとして使用できませんが、NOT NULLまたはCHECK制約を追加することはできます。

• JSON型列にはデフォルト値を含めることはできません。

• JSON型列はパーティションキーとして使用できません。

• JSONデータの長さはLONGTEXTの長さを超えてはならず、各JSONオブジェクトまたは配列の最大深さは99です。

例

JSON列の作成・変更

obclient> CREATE TABLE tbl1 (id INT PRIMARY KEY, docs JSON NOT NULL, docs1 JSON);
Query OK, 0 rows affected

obclient> ALTER TABLE tbl1 MODIFY docs JSON CHECK(docs <'{"a" : 100'));
Query OK, 0 rows affected

obclient> CREATE TABLE json_tab(
      id         INT UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT '主キー',
      json_info  JSON COMMENT 'JSONデータ',
      json_id    INT GENERATED ALWAYS AS (json_info -> '$.id') COMMENT 'JSONデータの仮想フィールド',
      json_name  VARCHAR(5) GENERATED ALWAYS AS (json_info -> '$.NAME'),
      index      json_info_id_idx (json_id)
    )COMMENT 'jsonサンプルテーブル';
Query OK, 0 rows affected

obclient> ALTER TABLE json_tab ADD COLUMN json_info1 JSON;
Query OK, 0 rows affected

obclient> ALTER TABLE json_tab ADD INDEX (json_name);
Query OK, 0 rows affected

obclient> ALTER TABLE json_tab drop COLUMN json_info1;
Query OK, 0 rows affected

生成列を指定キーとしてインデックスを作成する

obclient> CREATE TABLE jn ( c JSON, g INT GENERATED ALWAYS AS (c->"$.id"));
Query OK, 0 rows affected

obclient> CREATE INDEX idx1 ON jn(g);
Query OK, 0 rows affected
Records: 0  Duplicates: 0  Warnings: 0

obclient> INSERT INTO jn (c) VALUES
      ('{"id": "1", "name": "Fred"}'), ('{"id": "2", "name": "Wilma"}'),
      ('{"id": "3", "name": "Barney"}'), ('{"id": "4", "name": "Betty"}');
Query OK, 4 rows affected
Records: 4  Duplicates: 0  Warnings: 0

obclient> SELECT c->>"$.name" AS name FROM jn WHERE g <= 2;
+-------+
| name  |
+-------+
| Fred  |
| Wilma |
+-------+
2 rows in set

obclient> EXPLAIN SELECT c->>"$.name" AS name FROM jn WHERE g <= 2\G
*************************** 1. row ***************************
Query Plan: =========================================
|ID|OPERATOR  |NAME      |EST. ROWS|COST|
-----------------------------------------
|0 |TABLE SCAN|jemp(idx1)|2        |92  |
=========================================

Outputs & filters:
-------------------------------------
  0 - output([JSON_UNQUOTE(JSON_EXTRACT(jemp.c, '$.name'))]), filter(nil),
      access([jemp.c]), partitions(p0)

1 row in set

セミストラクチャードエンコーディングの使用

OceanBaseデータベースは、MySQLテナントでのテーブル作成時にセミストラクチャードエンコーディングを有効にすることをサポートしており、主にテーブルレベルパラメータ semistruct_encoding_type によって制御されます。同時に、テーブルの row_format=COMPRESSED も設定する必要があります。そうしない場合、エラーが報告されます。semistruct_encoding_type='encoding' の場合、テーブルはセミストラクチャードテーブルと見なされ、つまりテーブル全体のJSON列でセミストラクチャードエンコーディングが有効になります。semistruct_encoding_type='' の場合、テーブルは構造化テーブルと見なされます。

1. セミストラクチャードエンコーディングを有効にする

   注意

   セミストラクチャードエンコーディング機能を有効にする場合は、クラスタ構成パラメータ micro_block_merge_verify_level をデフォルト設定の 2 に保証し、マイクロブロックコンパクション検証を無効にしないでください。

   既存のテーブルを変更して半構造化エンコーディングを有効にする例

   CREATE TABLE t1(j json);
   ALTER TABLE t1 SET row_format=COMPRESSED semistruct_encoding_type = 'encoding';
   

   その他の構文の詳細については、ALTER TABLEを参照してください。

2. セミストラクチャードエンコーディングを無効にする

   セミストラクチャードエンコーディングを無効にする例は以下のとおりです：

   ALTER TABLE t1 SET row_format=COMPRESSED semistruct_encoding_type = '';
   

3. セミストラクチャードエンコーディングの設定を確認する

   SHOW CREATE TABLE ステートメントを使用して、セミストラクチャードエンコーディングの設定を確認します。例文は以下のとおりです：

   SHOW CREATE TABLE t1;
   

   実行結果は次のとおりです：

   +-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
   | Table | Create Table                                                                                                                                                                                                                                                                                           |
   +-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
   | t1    | CREATE TABLE `t1` (
     `j` json DEFAULT NULL
   ) DEFAULT CHARSET = utf8mb4
     ROW_FORMAT = COMPRESSED COMPRESSION = 'zstd_1.3.8' REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE ENABLE_MACRO_BLOCK_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0
     SEMISTRUCT_ENCODING_TYPE='ENCODING' |
   +-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
   1 row in set
   

   semistruct_encoding_type=encoding' の場合にのみ、この構成パラメータ情報が表示され、セミストラクチャードエンコーディング機能が有効になっていることを示します。

セミストラクチャードエンコーディングを使用すると、JSON_VALUE()関数条件フィルタリングクエリのパフォーマンスを向上させることができます。JSONセミストラクチャードエンコーディング技術に基づき、OceanBaseは JSON_VALUE 式条件フィルタリングクエリシナリオのパフォーマンスを最適化しています。JSONデータはすでにサブカラムに分割されているため、システムはエンコード後のサブカラムデータに基づいて直接フィルタリングでき、完全なJSON構造を復元する必要がなく、クエリ効率を大幅に向上させます。

クエリ例は以下のとおりです：

-- nameフィールドの値が'Devin'の行を照会する
SELECT * FROM t WHERE JSON_VALUE(j_doc, '$.name' RETURNING CHAR) = 'Devin';

文字セットに関する注意事項は以下のとおりです：

• OceanBaseのJSONは utf8_bin エンコーディングを使用します。

• 文字列ホワイトボックスフィルタリングが正常に動作するようにするため、次の設定を推奨します：

  SET @@collation_server = 'utf8mb4_bin';
  SET @@collation_connection='utf8mb4_bin';