JSON値のクエリと変更

OceanBaseデータベースは、JSON値のクエリと参照をサポートしており、パス表現を使用してJSONドキュメントの一部を抽出したり、JSONドキュメントの一部の内容を変更したりできます。

JSON値の参照

OceanBaseデータベースでは、以下の2つの方法でJSON値をクエリおよび参照できます：

• 「-\>」記号を使用して、JSONデータ内の二重引用符で囲まれたキーと値を参照します。

• 「-\>>」記号を使用して、JSONデータ内のシングル引用符で囲まれていないキーと値を参照します。

例：

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

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

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

JSONファイルには階層があるため、JSON関数を使用してパス表現でJSONドキュメントの一部を抽出または変更する際には、ドキュメント内で操作する位置を指定する必要があります。JSON関数の詳細については、JSON関数を参照してください。

OceanBaseデータベースでは、「プレフィックス $ 文字＋記号セレクタ」というパス構文を使用して、アクセス対象のJSONドキュメントを表します。記号セレクタの種類は以下のとおりです：

• 「.」記号は、アクセスするキー名を表します。引用符なしの名前（例：スペース）はパス表現では無効なため、キー名は必ず二重引用符で囲む必要があります。

  例：

  obclient> SELECT JSON_EXTRACT('{"id": 14, "name": "Aztalan"}', '$.name');
  +---------------------------------------------------------+
  | JSON_EXTRACT('{"id": 14, "name": "Aztalan"}', '$.name') |
  +---------------------------------------------------------+
  | "Aztalan"                                               |
  +---------------------------------------------------------+
  1 row in set
  

• 「[N]」記号は、選択された配列のパスの後に置かれ、配列の位置Nの値にアクセスすることを示します。ここで、Nは非負の整数です。配列の位置は0から始まる整数です。path が配列値を選択していない場合、path[0] は path と同じ計算値を持ちます。

  例：

  obclient> SELECT JSON_SET('"x"', '$[0]', 'a');
  +------------------------------+
  | JSON_SET('"x"', '$[0]', 'a') |
  +------------------------------+
  | "a"                          |
  +------------------------------+
  1 row in set
  

• 「[M to N]」記号は、配列値の部分集合または範囲を指定するために使用されます。つまり、位置Mの値から始まり、位置Nの値で終わる範囲を指します。

  例：

  obclient> SELECT JSON_EXTRACT('[1, 2, 3, 4, 5]', '$[1 to 3]');
  +----------------------------------------------+
  | JSON_EXTRACT('[1, 2, 3, 4, 5]', '$[1 to 3]') |
  +----------------------------------------------+
  | [2, 3, 4]                                    |
  +----------------------------------------------+
  1 row in set
  

• パス表現には * または ** ワイルドカードも含めることができます。説明は以下のとおりです：

  • .[*] は、JSONオブジェクト内のすべてのメンバーの値を表します。

  • [*] は、JSON配列内のすべての要素の値を計算することを示します。

  • prefix**suffix は、指定された名前前缀で始まり、名前後綴で終わるパスを指します。前缀部分は必須ではありませんが、後綴部分は必須です。任意のパスを記述するために「\*」または「\***」を使用することは許可されません。

  説明

  ドキュメントに存在しないパス（存在しないデータとして計算される）は、NULLとして計算されます。

JSON値の変更

OceanBaseデータベースでは、DMLステートメントを使用して完全なJSON値を変更したり、UPDATEステートメントでJSON_SET()、JSON_REPLACE()、またはJSON_REMOVE()関数を使用して部分的なJSON値を操作したりすることもサポートされています。

例：

// 全データの挿入
INSERT INTO json_tab(json_info) VALUES ('[1, {"a": "b"}, [2, "qwe"]');

// 一部データの挿入
UPDATE json_tab SET json_info=JSON_ARRAY_APPEND(json_info, '$', 2) WHERE id=1;

// 全データの更新
UPDATE json_tab SET json_info='[1, {"a": "b"}]';

// 一部データの更新
UPDATE json_tab SET json_info=JSON_REPLACE(json_info, '$[2]', 'aaa') WHERE id=1;

// 一部データの削除
DELETE FROM json_tab WHERE id=1;

// 関数を使用して一部データを更新
UPDATE json_tab SET json_info=JSON_REMOVE(json_info, '$[2]') WHERE id=1;

JSONパス構文

パスは、パス範囲と1つ以上のパスセグメントで構成されます。JSON関数で使用されるパスでは、範囲は現在検索またはその他の方法で操作されているドキュメントであり、先導文字$で表されます。

パスセグメントはピリオド（.）で区切られます。配列の要素は [N] で表され、ここでNは非負の整数です。キー名は二重引用符で囲まれた文字列または有効なECMAScript識別子である必要があります。

パス式（例えばJSONテキスト）は、ascii、utf8、または utf8mb4文字セットでエンコードされる必要があります（その他の文字エンコーディングは暗黙的にutf8mb4に強制変換されます）。

完全な構文は以下のとおりです：

pathExpression: // パス式
    scope[(pathLeg)*]   // 範囲は先導文字 $ で記述されます

pathLeg:
    member | arrayLocation | doubleAsterisk

member:
    period ( keyName | asterisk )

arrayLocation:
    leftBracket ( nonNegativeInteger | asterisk ) rightBracket

keyName:
    ESIdentifier | doubleQuotedString

doubleAsterisk:
    '**'

period:
    '.'

asterisk:
    '*'

leftBracket:
    '['

rightBracket:
    ']'