カテゴリ:

半構造化データ関数 (解析)

PARSE_JSON

入力文字列を JSON ドキュメントとして解釈し、 VARIANT 値を生成します。

こちらもご参照ください。

TRY_PARSE_JSON

構文

PARSE_JSON( <expr> )

引数

文字列型の式(例:VARCHAR)で、有効な JSON 情報を保持しています。

戻り値

返される値は VARIANT 型で、 JSON ドキュメントが含まれています。

使用上の注意

  • この関数は、最大サイズ8 MB に圧縮された入力式をサポートします。

  • 入力が NULL の場合は、出力も NULL です。ただし、入力文字列が 'null' の場合は、 JSON null 値として解釈されるため、結果は SQL NULL ではなく、 null を含む有効な VARIANT 値になります。例は、下記の例セクションに含まれています。

  • 10進数を解析するとき、 PARSE_JSON は、123.45を DOUBLEではなく NUMBER(5,2)として処理することにより、表現の正確さを保持しようとします。ただし、科学表記法を使用した数値(例: 1.2345e +02)、または、範囲またはスケールの制限により固定小数点として保存できない数値は DOUBLE として保存されます。JSON は TIMESTAMP、 DATE、 TIME、または BINARY などの値をネイティブに表さないため、これらは文字列として表される必要があります。

  • JSON 内では、オブジェクト(別名「ディクショナリ」または「ハッシュ」)は、キーと値のペアの 順序付けられていない セットです。

  • TO_JSON および PARSE_JSON は(ほぼ)逆または逆関数です。

    • PARSE_JSON 関数は入力として文字列を取り、 JSON互換のバリアントを返します。

    • TO_JSON 関数は JSON互換バリアントを受け取り、文字列を返します。

    Xが有効な JSON を含む文字列である場合、次は(概念的に)trueです。

    X = TO_JSON(PARSE_JSON(X));

    例えば、次は(概念的に)trueです。

    '{"pi":3.14,"e":2.71}' = TO_JSON(PARSE_JSON('{"pi":3.14,"e":2.71}'))

    ただし、関数は次の2つの理由で完全には逆数ではありません。

    • TO_JSON によって生成される文字列内のキーと値のペアの順序は予測できません。

    • TO_JSON によって生成された文字列には、 PARSE_JSON に渡される文字列よりも少ない空白を含めることができます。

    次は同等の JSONですが、同等の文字列ではありません。

    • {"pi": 3.14, "e": 2.71}

    • {"e":2.71,"pi":3.14}

これは、 PARSE_JSON を呼び出して文字列を解析することにより、 VARIANT 列にさまざまな型のデータを保存する例を示しています。

テーブルを作成して入力します。 INSERT ステートメントは PARSE_JSON 関数を使用します。

create or replace table vartab (n number(2), v variant);

insert into vartab
    select column1 as n, parse_json(column2) as v
    from values (1, 'null'), 
                (2, null), 
                (3, 'true'),
                (4, '-17'), 
                (5, '123.12'), 
                (6, '1.912e2'),
                (7, '"Om ara pa ca na dhih"  '), 
                (8, '[-1, 12, 289, 2188, false,]'), 
                (9, '{ "x" : "abc", "y" : false, "z": 10} ') 
       AS vals;

データをクエリします。

select n, v, typeof(v) from vartab;
+---+------------------------+------------+
| N | V                      | TYPEOF(V)  |
|---+------------------------+------------|
| 1 | null                   | NULL_VALUE |
| 2 | NULL                   | NULL       |
| 3 | true                   | BOOLEAN    |
| 4 | -17                    | INTEGER    |
| 5 | 123.12                 | DECIMAL    |
| 6 | 1.912000000000000e+02  | DOUBLE     |
| 7 | "Om ara pa ca na dhih" | VARCHAR    |
| 8 | [                      | ARRAY      |
|   |   -1,                  |            |
|   |   12,                  |            |
|   |   289,                 |            |
|   |   2188,                |            |
|   |   false,               |            |
|   |   undefined            |            |
|   | ]                      |            |
| 9 | {                      | OBJECT     |
|   |   "x": "abc",          |            |
|   |   "y": false,          |            |
|   |   "z": 10              |            |
|   | }                      |            |
+---+------------------------+------------+

次の例は、 PARSE_JSONTO_JSON の NULL の処理を示しています。

SELECT TO_JSON(NULL), TO_JSON('null'::VARIANT),
       PARSE_JSON(NULL), PARSE_JSON('null');
+---------------+--------------------------+------------------+--------------------+
| TO_JSON(NULL) | TO_JSON('NULL'::VARIANT) | PARSE_JSON(NULL) | PARSE_JSON('NULL') |
|---------------+--------------------------+------------------+--------------------|
| NULL          | "null"                   | NULL             | null               |
+---------------+--------------------------+------------------+--------------------+

次の例は、 PARSE_JSONTO_JSONTO_VARIANT の間の関係を示しています。

テーブルを作成し、 VARCHAR、汎用 VARIANT、 JSON 互換の VARIANT データを追加します。INSERT ステートメントは VARCHAR 値を挿入し、 UPDATE ステートメントはその VARCHAR に対応する JSON 値を生成します。

CREATE or replace TABLE jdemo2 (varchar1 VARCHAR, variant1 VARIANT,
    variant2 VARIANT);
INSERT INTO jdemo2 (varchar1) VALUES ('{"PI":3.14}');
UPDATE jdemo2 SET variant1 = PARSE_JSON(varchar1);

このクエリは、 TO_JSONPARSE_JSON が概念的に逆関数であることを示しています。

SELECT varchar1, PARSE_JSON(varchar1), variant1,  TO_JSON(variant1),
                 PARSE_JSON(varchar1) = variant1, TO_JSON(variant1) = varchar1
    FROM jdemo2;
+-------------+----------------------+--------------+-------------------+---------------------------------+------------------------------+
| VARCHAR1    | PARSE_JSON(VARCHAR1) | VARIANT1     | TO_JSON(VARIANT1) | PARSE_JSON(VARCHAR1) = VARIANT1 | TO_JSON(VARIANT1) = VARCHAR1 |
|-------------+----------------------+--------------+-------------------+---------------------------------+------------------------------|
| {"PI":3.14} | {                    | {            | {"PI":3.14}       | True                            | True                         |
|             |   "PI": 3.14         |   "PI": 3.14 |                   |                                 |                              |
|             | }                    | }            |                   |                                 |                              |
+-------------+----------------------+--------------+-------------------+---------------------------------+------------------------------+

ただし、関数は厳密には逆数ではありません。空白またはキーと値のペアの順序が違うときは、出力が入力と一致しない場合があります。例:

SELECT
    TO_JSON(PARSE_JSON('{"b":1,"a":2}')),
    TO_JSON(PARSE_JSON('{"b":1,"a":2}')) = '{"b":1,"a":2}',
    TO_JSON(PARSE_JSON('{"b":1,"a":2}')) = '{"a":2,"b":1}'
    ;
+--------------------------------------+--------------------------------------------------------+--------------------------------------------------------+
| TO_JSON(PARSE_JSON('{"B":1,"A":2}')) | TO_JSON(PARSE_JSON('{"B":1,"A":2}')) = '{"B":1,"A":2}' | TO_JSON(PARSE_JSON('{"B":1,"A":2}')) = '{"A":2,"B":1}' |
|--------------------------------------+--------------------------------------------------------+--------------------------------------------------------|
| {"a":2,"b":1}                        | False                                                  | True                                                   |
+--------------------------------------+--------------------------------------------------------+--------------------------------------------------------+

PARSE_JSONTO_VARIANT はどちらも文字列を取り、バリアントを返すことができますが、同等ではありません。次のコードは、 PARSE_JSON を使用して1つの列を更新し、 TO_VARIANT を使用して他の列を更新します。(列 variant1 の更新は、同じ関数呼び出しを使用して以前に更新されたため不要です。ただし、列を更新するために呼び出される関数を並べて確認できるように、以下のコードで再度更新します)。

UPDATE jdemo2 SET
    variant1 = PARSE_JSON(varchar1),
    variant2 = TO_VARIANT(varchar1);

以下のクエリは、 PARSE_JSON の出力と TO_VARIANT の出力が同じでないことを示しています。空白の些細な違いに加えて、引用符には大きな違いがあります。

SELECT variant1, variant2, variant1 = variant2 FROM jdemo2;
+--------------+-----------------+---------------------+
| VARIANT1     | VARIANT2        | VARIANT1 = VARIANT2 |
|--------------+-----------------+---------------------|
| {            | "{\"PI\":3.14}" | False               |
|   "PI": 3.14 |                 |                     |
| }            |                 |                     |
+--------------+-----------------+---------------------+