- カテゴリ:
半構造化データ関数と構造化データ関数 (解析)
PARSE_JSON¶
入力文字列を JSON ドキュメントとして解釈し、 VARIANT 値を生成します。
JSON 形式の入力データがある場合、 PARSE_JSON 関数を使用できます。この関数は、 JSON 形式からのデータを ARRAY または OBJECT のデータに変換し、そのデータを VARIANT 値で直接保管できます。その後、データを分析または操作できます。
- こちらもご参照ください。
構文¶
PARSE_JSON( <expr> )
引数¶
expr
有効な JSON 情報を保持する文字列型の式(例: VARCHAR)。
戻り値¶
JSON ドキュメントを含む型 VARIANT の値を返します。
入力が NULL の場合、関数は NULL を返します。
この関数は 構造化型 を返しません。
使用上の注意¶
この関数は、最大サイズ8 MB に圧縮された入力式をサポートします。
PARSE_JSON関数が空の文字列、または空白文字のみを含む文字列で呼び出された場合、空の文字列が無効なJSONであっても、関数は(エラーをスローするのではなく)NULLを返します。これにより、一部の入力が空の文字列である場合に、処理を中止するのではなく続行できます。
入力が 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互換の VARIANT を返します。
TO_JSON 関数はJSON互換VARIANTを受け取り、文字列を返します。
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つの理由で完全には逆数ではありません。
空の文字列、および空白のみの文字列は、相互に処理されません。例えば、
PARSE_JSON('')
の戻り値は NULL ですが、TO_JSON(NULL)
の戻り値は NULL であり、相反する''
ではありません。TO_JSON によって生成される文字列内のキーと値のペアの順序は予測できません。
TO_JSON によって生成された文字列には、 PARSE_JSON に渡される文字列よりも少ない空白を含めることができます。
次は同等のJSONですが、同等の文字列ではありません。
{"pi": 3.14, "e": 2.71}
{"e":2.71,"pi":3.14}
例¶
以下の例では、 PARSE_JSON 関数を使用しています。
VARIANT 列での異なるデータ型の値の格納¶
これは、 PARSE_JSON を呼び出して文字列を解析することにより、 VARIANT 列にさまざまな型のデータを保管する例を示しています。
テーブルを作成して入力します。INSERT ステートメントは、 PARSE_JSON を使用して VARIANT の値をテーブルの v
列に挿入します。
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;
データをクエリする。このクエリは、 TYPEOF 関数を使用して VARIANT 値に保管されている値のデータ型を表示します。
SELECT n, v, TYPEOF(v)
FROM vartab
ORDER BY n;
+---+------------------------+------------+
| 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_JSON と TO_JSON 関数を使用した NULL 値の処理¶
次の例は、 PARSE_JSON と TO_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_JSON と TO_JSON の比較¶
以下の例は、 PARSE_JSON と TO_JSON 関数の関係を示しています。
この例では、 VARCHAR 列と VARIANT 列を持つテーブルを作成します。INSERT ステートメントは VARCHAR 値を挿入し、 UPDATE ステートメントはその VARCHAR 値に対応する JSON 値を生成します。
CREATE OR REPLACE TABLE jdemo2 (
varchar1 VARCHAR,
variant1 VARIANT);
INSERT INTO jdemo2 (varchar1) VALUES ('{"PI":3.14}');
UPDATE jdemo2 SET variant1 = PARSE_JSON(varchar1);
このクエリは、 TO_JSON と PARSE_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_JSON と TO_VARIANT の比較¶
PARSE_JSON 関数と TO_VARIANT 関数の両方が文字列を受け取って VARIANT 値を返すことができますが、これらは同等ではありません。以下の例は、 VARIANT 列を2つ持つテーブルを作成しています。その後、 PARSE_JSON を使用して一方の列に値を挿入し、 TO_VARIANT を使用してもう一方の列に値を挿入します。
CREATE OR REPLACE TABLE jdemo3 (
variant1 VARIANT,
variant2 VARIANT);
INSERT INTO jdemo3 (variant1, variant2)
SELECT
PARSE_JSON('{"PI":3.14}'),
TO_VARIANT('{"PI":3.14}');
以下のクエリは、関数が異なるデータ型の値を保管する VARIANT 値を返したことを示しています。
SELECT variant1,
TYPEOF(variant1),
variant2,
TYPEOF(variant2),
variant1 = variant2
FROM jdemo3;
+--------------+------------------+-----------------+------------------+---------------------+
| VARIANT1 | TYPEOF(VARIANT1) | VARIANT2 | TYPEOF(VARIANT2) | VARIANT1 = VARIANT2 |
|--------------+------------------+-----------------+------------------+---------------------|
| { | OBJECT | "{\"PI\":3.14}" | VARCHAR | False |
| "PI": 3.14 | | | | |
| } | | | | |
+--------------+------------------+-----------------+------------------+---------------------+