AnalyticDB for MySQL は JSON データ型をサポートしています。本トピックでは、JSON データのフォーマット、注意事項、および使用例について説明します。
注意事項
AnalyticDB for MySQL は標準的な JSON フォーマットをサポートしています。JSON 文字列を作成する際は、標準 JSON フォーマット仕様を厳密に準拠してください。
JSON データ型のカラムにはデフォルト値を設定できません。
JSON フォーマット要件
AnalyticDB for MySQL では、JSON データのプロパティ key およびプロパティ value について、以下の要件が適用されます：
プロパティ key
keykeyは二重引用符 ("") で囲む必要があります。たとえば、"addr"は{"addr":"xyz"}の中で使用されます。プロパティ value
valueプロパティ
valueは、以下のデータの型をサポートします：ブール値、数値、VARCHAR、配列、オブジェクト、NULL。説明JSON インデックスを使用する場合、数値は DOUBLE 型の値範囲を超えてはなりません。
配列型は、単純な配列またはネストされた配列のいずれかです。たとえば、
{"hobby":["basketball", "football"]}や{"addr":[{"city":"beijing", "no":0}, {"city":"shenzhen", "no":0}]}などです。
valueが文字列型（STRING）の場合、valueを二重引用符 ("") で囲む必要があります。説明valueが文字列であり、かつ二重引用符（""）を含む場合、その二重引用符をvalueの一部として認識させるために、エスケープ文字を付加する必要があります。たとえば、valueが{"addr":"xyz"ab"c"}の中の"xyz"ab"c"である場合、エスケープ後の形式は"xyz\"ab\"c"となります。データを記述する際には、バックスラッシュ (\) もエスケープする必要があります。したがって、実際には{"addr":"xyz\\"ab\\"c"}のように記述します。値が数値型の場合、データを直接記述してください。値をダブルクォーテーション ("") で囲まないでください。valueがBoolean型の場合、trueまたはfalseを記述します。1や0を記述してはいけません。trueおよびfalseは必ず小文字で記述してください。valueがNullの場合、Nullを直接記述します。keyは、異なるデータの型のvaluesをサポートします。クエリの実行結果は、指定されたデータの型になります。たとえば、文
INSERT INTO test_tb1 VALUES ({"id": 1})を実行すると、idの値として数値1が挿入されます。また、文INSERT INTO test_tb1 VALUES ({"id": "1"})を実行すると、idの値として文字列"1"が挿入されます。クエリとして文
SELECT id FROM test_tb1 WHERE json_extract(col, '$.id')= 1;を実行した場合、数値"id": 1が返されます。一方、文SELECT id FROM test_tb1 WHERE json_extract(col, '$.id')= '1';を実行した場合、文字列"id": "1"が返されます。
使用例
テーブルの作成
CREATE TABLE json_test(
id int,
vj json
)
DISTRIBUTED BY HASH(id);データの書き込み
データを書き込む際、JSON 型のフィールドは VARCHAR 型のフィールドと同様に記述します。JSON 文字列は、シングルクォートで囲む必要があります。以下の SQL 例では、さまざまな JSON データ形式を示しています。
INSERT INTO json_test VALUES(0, '{"id":0, "name":"abc", "age":0}');
INSERT INTO json_test VALUES(1, '{"id":1, "name":"abc", "age":10, "gender":"f"}');
INSERT INTO json_test VALUES(2, '{"id":3, "name":"xyz", "age":30, "company":{"name":"alibaba", "place":"hangzhou"}}');
INSERT INTO json_test VALUES(3, '{"id":5, "name":"a\\"b\\"c", "age":50, "company":{"name":"alibaba", "place":"america"}}');
INSERT INTO json_test VALUES(4, '{"a":1, "b":"abc-char", "c":true}');
INSERT INTO json_test VALUES(5, '{"uname":{"first":"lily", "last":"chen"}, "addr":[{"city":"beijing", "no":1}, {"city":"shenzhen", "no":0}], "age":10, "male":true, "like":"fish", "hobby":["basketball", "football"]}');json_extract 関数を用いたデータのクエリ
構文
json_extract(json, jsonpath)コマンドの説明
JSON から
jsonpathで指定された値を返します。パラメーター
json：JSON カラムのカラム名。jsonpath：JSON プロパティkeyのパスで、ピリオド (.) で区切られます。$は最上位のパスを表します。
JSON 関数の詳細については、「JSON 関数」をご参照ください。
使用例
基本クエリ
SELECT json_extract(vj,'$.name') FROM json_test WHERE id=1;等価クエリ
SELECT id, vj FROM json_test WHERE json_extract(vj, '$.name') = 'abc'; SELECT id, vj FROM json_test WHERE json_extract(vj, '$.c') = true; SELECT id, vj FROM json_test WHERE json_extract(vj, '$.age') = 30; SELECT id, vj FROM json_test WHERE json_extract(vj, '$.company.name') = 'alibaba';範囲クエリ
SELECT id, vj FROM json_test WHERE json_extract(vj, '$.age') > 0; SELECT id, vj FROM json_test WHERE json_extract(vj, '$.age') < 100; SELECT id, vj FROM json_test WHERE json_extract(vj, '$.name') > 'a' and json_extract(vj, '$.name') < 'z';IS NULL または IS NOT NULL クエリ
SELECT id, vj FROM json_test WHERE json_extract(vj, '$.remark') is null; SELECT id, vj FROM json_test WHERE json_extract(vj, '$.name') is not null;IN クエリ
SELECT id, vj FROM json_test WHERE json_extract(vj, '$.name') in ('abc','xyz'); SELECT id, vj FROM json_test WHERE json_extract(vj, '$.age') in (10,20);LIKE クエリ
SELECT id, vj FROM json_test WHERE json_extract(vj, '$.name') like 'ab%'; SELECT id, vj FROM json_test WHERE json_extract(vj, '$.name') like '%bc%'; SELECT id, vj FROM json_test WHERE json_extract(vj, '$.name') like '%bc';配列クエリ
SELECT id, vj FROM json_test WHERE json_extract(vj, '$.addr[0].city') = 'beijing' and json_extract(vj, '$.addr[1].no') = 0;説明配列データをクエリする場合、指定された添字（サブスクリプト）を使用して値を取得する必要があります。序数は 0 から始まり、増分されます。配列全体を反復処理することはサポートされていません。
unnest 関数を用いた JSON 配列のアンネスト
クラスターのカーネルバージョンは 3.2.5 以降である必要があります。
マイナーバージョンの確認および更新を行うには、AnalyticDB for MySQL コンソール の 構成情報 セクション（クラスター情報 ページ内）に移動してください。
構文
unnest(json_array)コマンドの説明
JSON 配列をアンネストし、配列内の各要素を結果セットの各行として出力します。
パラメーター
json_array：JSON データ。使用例
SELECT * FROM unnest(json '[{"a":"123"},{"a":"456"}]');返される結果
+-------------+ | _col0 | +-------------+ | {"a":"123"} | | {"a":"456"} | +-------------+