Lindorm:JSON 関数

json_object

キーと値のペアから JSON オブジェクトを作成します。

構文

json_object(key1, value1, key2, value2, ...)

パラメーター

例

単純なオブジェクトを作成します：

SELECT json_object('name', 'Alice', 'age', 25);
-- 結果: {"name": "Alice", "age": 25}

UPSERT 文で使用します：

UPSERT INTO users (id, data) VALUES (1, json_object('name', 'Charlie', 'city', 'Beijing'));

json_array

値のリストから JSON 配列を作成します。

構文

json_array(value1, value2, value3, ...)

パラメーター

例

単純な配列を作成します：

SELECT json_array('apple', 'banana', 'orange');
-- 結果: ["apple", "banana", "orange"]

UPSERT 文で使用します：

UPSERT INTO products (id, tags) VALUES (1, json_array('electronics', 'mobile', 'smartphone'));

json_extract

JSON ドキュメントから指定されたパスの値を抽出し、そのネイティブ JSON 型で返します。

構文

json_extract(json_column, 'path')

パラメーター

例

トップレベルのフィールドを抽出します：

SELECT json_extract('{"name": "Alice", "age": 25}', '$.name');
-- 結果: "Alice"

ネストされたフィールドを抽出します：

SELECT json_extract('{"user": {"name": "Bob", "age": 30}}', '$.user.name');
-- 結果: "Bob"

配列要素を抽出します：

SELECT json_extract('{"skills": ["Java", "Python"]}', '$.skills[0]');
-- 結果: "Java"

配列全体を抽出します：

SELECT json_extract('{"skills": ["Java", "Python"]}', '$.skills');
-- 結果: ["Java", "Python"]

数値比較で WHERE 句で使用します：

SELECT * FROM tb WHERE json_extract(c2, '$.k3.k4') > 5;

文字列比較で WHERE 句で使用します (型安全のため json_extract_string を使用します)：

SELECT * FROM tb WHERE json_extract_string(c2, '$.k2') = '1';

json_extract_string

指定されたパスの値を抽出し、VARCHAR として返します。パスの値が文字列でない場合、関数は NULL を返します。

例

$.name の値は文字列なので、VARCHAR を返します：

SELECT json_extract_string('{"name": "Alice"}', '$.name');
-- 結果: Alice (VARCHAR)

$.number の値は数値であり、文字列ではないため、関数は NULL を返します：

SELECT json_extract_string('{"number": 30}', '$.number');
-- 結果: NULL

json_extract_long

指定されたパスの値を抽出し、LONG として返します。パスの値が数値でない場合、Java Database Connectivity (JDBC) はエラーをスローします。

例

値は整数なので、LONG を返します：

SELECT json_extract_long('{"id": 123456789}', '$.id');
-- 結果: 123456789 (LONG)

値は浮動小数点数です — JDBC はエラーをスローします：

SELECT json_extract_long('{"id": 123456.789}', '$.id');
-- エラーをスローします

値は数値文字列です — JDBC はエラーをスローします：

SELECT json_extract_long('{"id": "123456.789"}', '$.id');
-- エラーをスローします

json_extract_double

指定されたパスの値を抽出し、Double として返します。パスの値が浮動小数点数でない場合、JDBC はエラーをスローします。

例

値は浮動小数点数なので、Double を返します：

SELECT json_extract_double('{"id": 12345.56}', '$.id');
-- 結果: 12345.56 (Double)

値は整数です — 関数はそれを Double として返します：

SELECT json_extract_double('{"id": 12345}', '$.id');
-- 結果: 12345.0 (Double)

値は数値文字列です — JDBC はエラーをスローします：

SELECT json_extract_double('{"id": "123456.789"}', '$.id');
-- エラーをスローします

json_contains

target_json が candidate_json のすべての要素を含む場合は 1 を、そうでない場合は 0 を返します。オプションで、パスを指定してドキュメント内の特定の場所で包含をチェックできます。

指定されたパスが存在しない場合、またはいずれかのパラメーターが NULL の場合は NULL を返します。

構文

json_contains(target_json, candidate_json[, path])

パラメーター

`candidate_json` のフォーマット方法

candidate_json は、チェック対象の要素を表す有効な JSON 文字列である必要があります：

JSON 型の分類

• スカラー：文字列、数値 (整数/Double)、ブール値、Null

• 複合型：配列、オブジェクト

包含ルール

• スカラーは別のスカラーのみを含むことができ、配列を含むことはできません。

• 配列は要素 (スカラー) またはサブセット配列を含むことができます。

例

以下のクエリで使用するサンプルテーブルを設定します：

CREATE TABLE test_table (id INT, c1 VARCHAR, data JSON, PRIMARY KEY(id));
UPSERT INTO test_table (id, data) VALUES (1, NULL);
UPSERT INTO test_table (id, data) VALUES (2, '{"skills": ["Java", "Python"]}');
UPSERT INTO test_table (id, data) VALUES (3, '{"skills": ["Go", "C"]}');
UPSERT INTO test_table (id, data) VALUES (4, '{"technical_skills": ["Java", "Go", "Rust"]}');
UPSERT INTO test_table (id, data) VALUES (5, '["Java","C++", "JavaScript"]');
UPSERT INTO test_table (id, data) VALUES (6, '{"skills": "Java"}');
UPSERT INTO test_table (id, data) VALUES (7, '["Java", "C#"]');
UPSERT INTO test_table (id, data) VALUES (8, '{"skills": ["Go", "Rust"]}');

2つのパラメーター形式では、ドキュメント全体 (ルートパス $) をチェックします。これにより、トップレベルのドキュメントが "Java" を含む配列である行が見つかります：

SELECT id, data FROM test_table WHERE json_contains(data, '["Java"]') AND id > 0 AND id < 10;

+------+------------------------------+
| id   | data                         |
+------+------------------------------+
|    5 | ["Java","C++", "JavaScript"] |
|    7 | ["Java", "C#"]               |
+------+------------------------------+

3つのパラメーター形式では、特定のパスをチェックします。これにより、$.skills が "Java" を含む配列である行が見つかります：

SELECT id, data FROM test_table WHERE json_contains(data, '["Java"]', '$.skills') AND id > 0 AND id < 10;

+------+--------------------------------+
| id   | data                           |
+------+--------------------------------+
|    2 | {"skills": ["Java", "Python"]} |
+------+--------------------------------+

json_contains は candidate_json のすべての要素が存在することを要求します。このクエリでは、$.skills 配列に "Java" と "Go" の両方を含む行がないため、行は見つかりません：

SELECT id, data FROM test_table WHERE json_contains(data, '["Java","Go"]', '$.skills') AND id > 0 AND id < 10;
-- 空のセット

候補としてスカラーの "Java" を使用すると、"Java" を含む配列と、"Java" に等しいスカラーの両方に一致します：

SELECT id, data FROM test_table WHERE json_contains(data, '"Java"', '$.skills') AND id > 0 AND id < 10;

+------+--------------------------------+
| id   | data                           |
+------+--------------------------------+
|    2 | {"skills": ["Java", "Python"]} |
|    6 | {"skills": "Java"}             |
+------+--------------------------------+

オブジェクトがキーと値のペアを含むかどうかをチェックします：

SELECT json_contains('{"a": 1, "b": 2}', '{"a": 1}');
-- 結果: 1

特定のパスが指定された値を含むかどうかをチェックします：

SELECT json_contains('{"a": 1, "b": 2}', '1', '$.a');
-- 結果: 1

SELECT json_contains('{"a": 1, "b": 2}', '1', '$.b');
-- 結果: 0

json_contains_any

target_json が candidate_json の少なくとも1つの要素を含む場合、1 を返します。json_contains と同じ構文とパラメーターを使用します。

例

パスなしでは、ルートドキュメントをチェックします。トップレベルのドキュメントはオブジェクトであり、"Java" または "Go" を直接含む配列ではありません：

SELECT json_contains_any('{"skills": ["Java", "Python"]}', '["Java", "Go"]');
-- 結果: 0

パスを指定すると、$.skills をチェックします。$.skills の配列には "Java" が含まれており、これは候補の1つの要素と一致します：

SELECT json_contains_any('{"skills": ["Java", "Python"]}', '["Java", "Go"]', '$.skills');
-- 結果: 1

2つのパラメーター形式では、トップレベルのドキュメントが ["Java", "Go"] のいずれかの要素を持つ配列である行が見つかります：

SELECT id, data FROM test_table WHERE json_contains_any(data, '["Java", "Go"]') LIMIT 10;

+------+------------------------------+
| id   | data                         |
+------+------------------------------+
|    5 | ["Java","C++", "JavaScript"] |
|    7 | ["Java", "C#"]               |
+------+------------------------------+

3つのパラメーター形式では、$.skills が候補リストのいずれかの要素を含む行が見つかります：

SELECT id, data FROM test_table WHERE json_contains_any(data, '["Java", "Go", "Rust"]', '$.skills') LIMIT 10;

+------+--------------------------------+
| id   | data                           |
+------+--------------------------------+
|    2 | {"skills": ["Java", "Python"]} |
|    3 | {"skills": ["Go", "C"]}        |
|    6 | {"skills": "Java"}             |
|    8 | {"skills": ["Go", "Rust"]}     |
+------+--------------------------------+

特定のパスが候補配列のいずれかの値を含むかどうかをチェックします：

SELECT json_contains_any('{"departments": ["Engineering", "Sales"]}', '["Marketing", "Engineering"]', '$.departments');
-- 結果: 1

パスにおけるスカラーと配列の包含の理解

json_contains_any(data, '["Java"]', '$.technical_skills') を考えます。動作は $.technical_skills の JSON 型に依存します：

• 値が {"technical_skills": "Java"} (スカラー文字列) の場合、関数は false を返します。スカラーのサブセットはリストにはなりえません。

• 値が {"technical_skills": ["Java"]} または {"technical_skills": ["Java", "Go"]} (配列) の場合、関数は true を返します。

json_contains_any(data, '"Java"', '$.technical_skills') を考えます。スカラー候補を使用する場合：

• 値が {"technical_skills": "Java"} ("Java" に等しいスカラー文字列) の場合、関数は true を返します。

• 値が {"technical_skills": ["Java"]} または {"technical_skills": ["Java", "Go"]} ("Java" を含む配列) の場合、関数は true を返します。

json_set

指定されたパスに値を挿入または更新します。パスが存在する場合は値を更新し、パスが存在しない場合は値を挿入します。JSON 列が NULL の場合、結果は NULL になります — 新しいオブジェクトは作成されません。

json_insert + json_replace に相当します。

構文

json_set(json_column, 'path', new_value)

例

既存のフィールドを更新します：

UPSERT INTO test_table (id, data) VALUES (3, '{"name": "Charlie", "age": 30}');
UPDATE test_table SET data = json_set(data, '$.age', 31) WHERE id = 3;
-- 結果: {"name": "Charlie", "age": 31}

新しいフィールドを挿入します：

UPDATE test_table SET data = json_set(data, '$.department', 'Engineering') WHERE id = 3;
-- 結果: {"name": "Charlie", "age": 31, "department": "Engineering"}

JSON 列が NULL の場合、結果は NULL のままです：

UPSERT INTO test_table (id, c1) VALUES (4, 'test');
UPDATE test_table SET data = json_set(data, '$.name', 'David') WHERE id = 4;
-- 結果: NULL

json_insert

指定されたパスがまだ存在しない場合にのみ、値を挿入します。パスが既に存在する場合、関数は何も実行しません。

json_insert は既存の値を上書きしません。既存のフィールドへの挿入試行は、何もせずに元の値を保持します。

構文

json_insert(json_column, 'path', new_value)

例

新しいフィールドを挿入します：

UPSERT INTO test_table (id, data) VALUES (5, '{"name": "Eve"}');
UPDATE test_table SET data = json_insert(data, '$.age', 28) WHERE id = 5;
-- 結果: {"name": "Eve", "age": 28}

既存のフィールドへの挿入試行は効果がありません — name フィールドは変更されません：

UPDATE test_table SET data = json_insert(data, '$.name', 'New Name') WHERE id = 5;
-- 結果: {"name": "Eve", "age": 28}

json_replace

指定されたパスが既に存在する場合にのみ、値を更新します。パスが存在しない場合、関数は何も実行しません。

構文

json_replace(json_column, 'path', new_value)

例

既存のフィールドを更新します：

UPSERT INTO test_table (id, data) VALUES (6, '{"name": "Frank", "age": 35}');
UPDATE test_table SET data = json_replace(data, '$.age', 36) WHERE id = 6;
-- 結果: {"name": "Frank", "age": 36}

存在しないフィールドの更新試行は効果がありません — city フィールドは追加されません：

UPDATE test_table SET data = json_replace(data, '$.city', 'Shanghai') WHERE id = 6;
-- 結果: {"name": "Frank", "age": 36}

json_remove

指定されたパスの値を削除し、変更されたドキュメントを返します。パスが存在しない場合、操作はエラーなしで成功します。

構文

json_remove(json_column, 'path')

例

フィールドを削除します：

UPSERT INTO test_table (id, data) VALUES (7, '{"name": "Grace", "temp_field": "to_remove"}');
UPDATE test_table SET data = json_remove(data, '$.temp_field') WHERE id = 7;
-- 結果: {"name": "Grace"}

存在しないフィールドを削除しても何も起こらず、ドキュメントは変更されずに返されます：

UPDATE test_table SET data = json_remove(data, '$.nonexistent') WHERE id = 7;
-- 結果: {"name": "Grace"}

json_upsert

指定されたパスに値を挿入または更新します。json_set のように動作しますが、1つの重要な違いがあります：JSON 列が NULL の場合、json_upsert は NULL を返す代わりに新しい JSON オブジェクトを作成します。

構文

json_upsert(json_column, 'path', new_value)

例

JSON 列が NULL の場合、json_upsert は新しいオブジェクトを作成します：

CREATE TABLE test_table (id INT, c1 VARCHAR, data JSON, PRIMARY KEY(id));
UPSERT INTO test_table (id, c1) VALUES (1, 'test');
UPDATE test_table SET data = json_upsert(data, '$.name', 'Alice') WHERE id = 1;
-- 結果: {"name": "Alice"}

既存のフィールドを更新します：

UPSERT INTO test_table (id, data) VALUES (2, '{"name": "Bob", "age": 25}');
UPDATE test_table SET data = json_upsert(data, '$.age', 26) WHERE id = 2;
-- 結果: {"name": "Bob", "age": 26}

新しいフィールドを挿入します：

UPDATE test_table SET data = json_upsert(data, '$.city', 'Beijing') WHERE id = 2;
-- 結果: {"name": "Bob", "age": 26, "city": "Beijing"}

包含チェック関数

更新関数

構文

CREATE INDEX [index_name]
    ON table_name (json_extract_type(column, json_path))
  [INCLUDE (column_name1, ..., column_namen)]
  [ASYNC]
  [index_options]

パラメーター

サポートされている関数

以下の関数は、関数ベースインデックスの作成をサポートしています：

• json_extract_string(json_column, 'path') — 文字列値にインデックスを作成します

• json_extract_long(json_column, 'path') — 長整数値にインデックスを作成します

• json_extract_double(json_column, 'path') — 浮動小数点値にインデックスを作成します

• json_contains(json_column, 'value', 'path') — 包含チェックにインデックスを作成します。value と path パラメーターは固定 (静的) 値である必要があります

例

json_contains クエリを $.roles で高速化するためのインデックスを作成します：

CREATE INDEX idx_user_role ON test_table (json_contains(data, '"admin"', '$.roles')) INCLUDE(data) SYNC;

$.address.city の文字列フィールドにインデックスを作成します：

CREATE INDEX idx_user_city ON test_table (json_extract_string(data, '$.address.city')) INCLUDE(data) SYNC;

追加の列を含めずに文字列フィールドにインデックスを作成します：

CREATE INDEX idx_user_city ON test_table (json_extract_string(data, '$.address.city'));

$.age の数値フィールドにインデックスを作成します：

CREATE INDEX idx_user_age ON test_table (json_extract_long(data, '$.age')) INCLUDE(data) SYNC;

インデックスが作成されたことを確認します：

SHOW INDEX FROM test_table;

JSON 検索インデックスのサポートについては、「Lindorm JSON 型の検索インデックスの使用」をご参照ください。