すべてのプロダクト
Search

このプロダクト

    AnalyticDB:JSON

    ドキュメントセンター

    AnalyticDB:JSON

    最終更新日:Sep 06, 2024

    AnalyticDB for MySQLはJSONデータ型をサポートしています。 このトピックでは、JSONデータ型の形式、使用法のメモ、および例について説明します。

    使用上の注意

    • AnalyticDB for MySQLは標準のJSON形式をサポートしています。 JSON文字列をテーブルに書き込むときは、標準のJSON形式に準拠する必要があります。

    • JSONデータ型の列にはデフォルト値を設定できません。

    JSON形式の要件

    AnalyticDB for MySQLでJSONデータを使用する場合、キーと値に関する次の要件に注意してください。

    • キー

      キーを二重引用符 ("") で囲む必要があります。 例: {"addr":"xyz"} の "addr" 。

    • 値

      • valueでサポートされているデータ型は、BOOLEAN、NUMBER、VARCHAR、ARRAY、OBJECT、NULLです。

        説明

        • JSONインデックスのNUMBER型の値は、DOUBLE型の許容値範囲内にある必要があります。

        • AnalyticDB for MySQLでは、プレーン配列やネスト配列などのJSON配列を記述できます。 例: {"hobby":["basketball", "football"]} および {"addr":[{"city":"beijing", "no":0}}, {"city":"shenzhen", "no":0}]} 。

      • 値がSTRING型の場合、値を二重引用符 ("") で囲む必要があります。

        説明

        値がSTRING型で、二重引用符 ("") を含む場合、二重引用符が値の一部であることを示すために、二重引用符の前にエスケープ文字を追加する必要があります。 たとえば、{"addr":"xyz" ab "c"} の値は "xyz" ab "c" です。 値は "xyz\" ab\"c" としてエスケープする必要があります。 データの書き込み時にバックスラッシュ (\) がエスケープされます。 この場合、{"addr":"xyz\\" ab\\"c"} と記述する必要があります。

      • valueがNUMBER型の場合、値を二重引用符 (") で囲む代わりに、valueをテーブルに直接書き込むことができます。

      • valueがBOOLEAN型の場合、テーブルに1または0ではなく、trueまたはfalseを書き込む必要があります。 trueとfalseは小文字である必要があります。

      • valueがNULLの場合、テーブルにNullを書き込む必要があります。

      • キーは、異なるデータ型の値を持つことができます。 指定したデータ型の値は、クエリ中に返されます。

        たとえば、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型のフィールドと同じ方法で、単一引用符 ('') でテーブルに書き込まれます。 次の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"]}');

    クエリデータ

    AnalyticDB for MySQLでは、json_extract() 関数を使用してデータをクエリできます。

    • 構文

      json_extract(json, jsonpath)

    • 説明

      この関数は、有効なJSON文字列からjsonpathで指定された1つ以上の値を抽出します。

    • Parameters

      • json: JSON型の列の名前。

      • jsonpath: ピリオド (.) で区切られたキーのパス。 $は、最も外側のパスを指定します。

      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';

      • ARRAYクエリ

        SELECT id, vj FROM json_test WHERE json_extract(vj, '$.addr[0].city') = 'beijing' and json_extract(vj, '$.addr[1].no') = 0;
        説明

        配列をクエリするときは、特定のデータの添え字を使用できます。 下付き文字の値は0から始まります。 配列全体を反復処理することはできません。

    関連ドキュメント

    JSONインデックス