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]

数字

数字は十進数形式で表され、整数部を含み、場合によっては（オプション）マイナス記号（-）を接頭辞として使用し、その後に分数部と/または指数部を続けることができます。先頭のゼロは許可されません。小数部は小数点の後に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_PROPERTIES によって制御されますが、テーブルの ROW_FORMAT=COMPRESSED も設定する必要があります。そうでない場合、エラーが発生します。

• SEMISTRUCT_PROPERTIES=(encoding_type=encoding) の場合、テーブルはセミ構造化テーブルと見なされ、テーブル全体のJSON列でセミ構造化エンコーディングが有効になります。
• SEMISTRUCT_PROPERTIES=(encoding_type=none) の場合、テーブルは構造化テーブルと見なされます。
• また、freq_threshold パラメータを使用して頻度しきい値を設定することもできます。具体的な構文とパラメータの説明については、CREATE TABLEを参照してください。
• 現在、encoding_type と freq_threshold はOnline DDL構文であり、Offline DDL構文はサポートされていません。

1. セミ構造化エンコーディングの有効化

   注意

   セミ構造化エンコーディング機能を有効にする場合は、クラスタ構成パラメータ micro_block_merge_verify_level がデフォルト設定の 2 であることを必ず確認してください。マイクロブロックマージ検証を無効にしないでください。

   テーブル作成時に有効にした例

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

   CREATE TABLE t1(  j json)
   ROW_FORMAT=COMPRESSED
   SEMISTRUCT_PROPERTIES=(encoding_type=encoding, freq_threshold=50);
   

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

   CREATE TABLE t1(j json);
   ALTER TABLE t1 SET ROW_FORMAT=COMPRESSED SEMISTRUCT_PROPERTIES = (encoding_type=encoding, freq_threshold=50);
   

   詳細な構文説明については、ALTER TABLEを参照してください。

   一部の変更制限：

   • フリーカンバンティング列のしきい値を変更する場合、半構造化データが有効になっていないとエラーは報告されませんが、変更は反映されません。
   • ダイレクトロード中またはテーブルがロックされている場合、freq_thresholdパラメータは変更できません。
   • いずれかのサブパラメータを変更しても、他のサブパラメータには影響しません。

2. セミ構造化エンコーディングの無効化

   SEMISTRUCT_PROPERTIES を (encoding_type=none) に設定すると、セミ構造化エンコーディングが無効になります。この操作は既存のデータには影響せず、今後書き込まれるデータにのみ有効です。以下はセミ構造化エンコーディングを無効にする例です：

   ALTER TABLE t1 SET ROW_FORMAT=COMPRESSED SEMISTRUCT_PROPERTIES = (encoding_type=none);
   

3. セミ構造化エンコーディング設定の確認

   SHOW CREATE TABLE ステートメントを使用してセミ構造化エンコーディングの設定を確認します。例：

   SHOW CREATE TABLE t1;
   

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

   +-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
   | Table | Create Table                                                                                                                                                                                                                                                                                                                                            |
   +-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
   | t1    | CREATE TABLE `t1` (
     `j` json DEFAULT NULL
   ) ORGANIZATION INDEX 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_PROPERTIES=(ENCODING_TYPE=ENCODING, FREQ_THRESHOLD=50) |
   +-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
   1 row in set
   

   SEMISTRUCT_PROPERTIES=(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';