カテゴリ：: 半構造化データ関数と構造化データ関数（解析）

PARSE_JSON¶

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

JSON 形式の入力データがある場合、 PARSE_JSON 関数を使用できます。この関数は、 JSON 形式からのデータを ARRAY または OBJECT のデータに変換し、そのデータを VARIANT 値で直接保管できます。その後、データを分析または操作できます。

デフォルトでは、この関数は JSON オブジェクトのキーの重複を許可しませんが、 'parameter' 引数を設定してキーの重複を許可することができます。

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

構文¶

PARSE_JSON( <expr> [ , '<parameter>' ] )

引数¶

必須：

expr: 有効な JSON 情報を保持する文字列型の式（例: VARCHAR）。

オプション：

'parameter'

検索に使用するパラメーターを指定する文字列定数。サポートされている値:


パラメーター	説明
`d`	JSON オブジェクトでキーの重複を許可します。JSON オブジェクトに重複キーが含まれる場合、返されるオブジェクトには、そのキーに指定された最後の値を持つ、そのキーのインスタンスが1つ含まれます。
`s`	JSON オブジェクトでキーの重複を許可しません（厳密）。この値がデフォルトです。

戻り値¶

JSON ドキュメントを含む型 VARIANT の値を返します。

入力が NULL の場合、関数は NULL を返します。

この関数は構造化型を返しません。

使用上の注意¶

この関数は、最大サイズ64 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              |            |
|   | }                      |            |
+---+------------------------+------------+

VARIANT 値に、キーが重複する JSON オブジェクトを挿入します。¶

キーが重複している JSON オブジェクトを VARIANT 値に挿入しようとします。

INSERT INTO vartab
SELECT column1 AS n, PARSE_JSON(column2) AS v
  FROM VALUES (10, '{ "a" : "123", "b" : "456", "a": "789"} ')
     AS vals;

デフォルトではキーの重複は許可されていないため、エラーが返されます。

100069 (22P02): Error parsing JSON: duplicate object attribute "a", pos 31

キーが重複する JSON オブジェクトを VARIANT 値に挿入し、 d パラメーターで重複を許可するように指定します。

INSERT INTO vartab
SELECT column1 AS n, PARSE_JSON(column2, 'd') AS v
  FROM VALUES (10, '{ "a" : "123", "b" : "456", "a": "789"} ')
     AS vals;

+-------------------------+
| number of rows inserted |
|-------------------------|
|                       1 |
+-------------------------+

テーブルにクエリを実行すると、最後に重複したキーの値のみが挿入されていることがわかります。

SELECT v
  FROM vartab
  WHERE n = 10;

+---------------+
| V             |
|---------------|
| {             |
|   "a": "789", |
|   "b": "456"  |
| }             |
+---------------+

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 |                  |                 |                  |                     |
| }            |                  |                 |                  |                     |
+--------------+------------------+-----------------+------------------+---------------------+