GET_JSON_OBJECT の構文、パラメーター、および戻り値 - MaxCompute

標準 JSON 文字列から特定の文字列を抽出します。このトピックでは、JSON 型と STRING 型の入力パラメーターを使用する場合の GET_JSON_OBJECT 関数の使用方法と考慮事項について説明します。

使用方法に関する注意事項

GET_JSON_OBJECT 関数は、JSONPath を使用して標準の JSON 文字列から特定の文字列を抽出します。この関数は、次のデータ型の入力パラメーターをサポートしています:

JSON 型: 入力パラメーターが JSON 型の場合、標準の JSONPath 式が使用されます。
STRING 型: 入力パラメーターが STRING 型の場合、元の JSONPath 式が使用されます。

説明

JSON 型の入力パラメーターと STRING 型の入力パラメーターに使用される JSONPath 式は、異なるルールに従います。これにより、互換性の問題が発生する可能性があります。
GET_JSON_OBJECT 関数は、JSONPath 式の構文をサポートしていません。

JSON 型の入力パラメーター

構文

STRING GET_JSON_OBJECT(JSON <json>, STRING <json_path>)

パラメーター

json: 必須。単一の文字列を抽出する JSON 文字列。
json_path: 必須。このパラメーターは、特定の JSON 値がチェックされる JSON パスを指定します。

戻り値

戻り値は STRING 型です。

例

例 1: JSON 文字列からキー a に対応する値を抽出します。
```
SELECT GET_JSON_OBJECT(JSON '{"a":1, "b":2}', '$.a');
```
次の結果が返されます。
```
+-----+
| _c0 |
+-----+
| 1   |
+-----+
```
例 2: JSON 文字列からキー c に対応する値を抽出します。
```
SELECT GET_JSON_OBJECT(JSON '{"a":1, "b":2}', '$.c');
```
次の結果が返されます。
```
+-----+
| _c0 |
+-----+
| NULL |
+-----+
```
例 3: 無効な JSON パスが指定されている場合、戻り値は NULL です。
```
SELECT GET_JSON_OBJECT(JSON '{"a":1, "b":2}', '$invalid_json_path');
```
次の結果が返されます。
```
+-----+
| _c0 |
+-----+
| NULL |
+-----+
```

STRING 型の入力パラメーター

背景情報

この関数は、path を使用して標準の JSON 文字列から単一の文字列を抽出します。

重要

この関数が呼び出されるたびに元のデータが読み取られます。したがって、繰り返し呼び出すと、システムのパフォーマンスに影響し、コストが増加する可能性があります。GET_JSON_OBJECT 関数をユーザー定義のテーブル値関数 (UDTF) と一緒に使用して、JSON ログデータを変換できます。これにより、関数の繰り返し呼び出しを防ぐことができます。詳細については、「MaxCompute のビルトイン関数と UDTF を使用して JSON ログデータを変換する」をご参照ください。

パラメーター

json: 必須。値は STRING 型である必要があります。このパラメーターは、{Key:Value, Key:Value,...} 形式の標準 JSON オブジェクトを指定します。文字列に二重引用符 (") が含まれている場合は、抽出前に 2 つのバックスラッシュ (\\) を使用して二重引用符をエスケープします。文字列に一重引用符 (') が含まれている場合は、抽出前に 1 つのバックスラッシュ (\) を使用して一重引用符をエスケープします。
path: 必須。値は STRING 型である必要があります。このパラメーターは、json パラメーターの値の path を指定し、$ で始まります。path パラメーターの詳細については、「LanguageManual UDF」をご参照ください。ベストプラクティスの詳細については、「OSS から MaxCompute への JSON 形式のデータの移行」をご参照ください。さまざまな文字には次の意味があります:
- $: ルートノードを示します。
- . または ['']: 子ノードを示します。MaxCompute は、JSON オブジェクトを解析するために両方の表記をサポートしています。JSON オブジェクトのキーにピリオド (.) が含まれている場合は、[''] を使用してキーを指定できます。
- []: [number] は配列の添字を示し、0 から始まります。
- *: [] のワイルドカード、配列全体を返します。* はエスケープできません。

制限

最新バージョンの関数のみが、path パラメーターで [''] を使用してデータを抽出できます。[''] を使用するには、SET odps.sql.udf.getjsonobj.new=true; 構成を追加する必要があります。

構文

STRING GET_JSON_OBJECT(STRING <json>, STRING <path>)

戻り値

json パラメーターが空であるか、無効な json 形式である場合、NULL が返されます。
json パラメーターが有効で、パスが存在する場合、関連する文字列が返されます。
セッションの odps.sql.udf.getjsonobj.new パラメーターを構成して、関数が値を返す方法を指定できます:
- SET odps.sql.udf.getjsonobj.new=true; コマンドを実行すると、関数は元の文字列を保持して値を返します。
  この構成を使用することをお勧めします。これにより、関数の戻り値の動作がより標準的になり、データ処理が容易になり、データ処理のパフォーマンスが向上します。MaxCompute プロジェクトに JSON 予約文字をエスケープする動作を使用する既存のジョブがある場合は、検証なしで新しい動作を使用すると発生する可能性のあるエラーや正確性の問題を回避するために、元の動作を保持することをお勧めします。この関数は、値を返すときに次のルールに準拠します:
  - 戻り値は JSON 文字列であり、JSON データとして解析できます。バックスラッシュ (\) を置き換えるために replace または regexp_replace 関数を使用する必要はありません。
  - JSON オブジェクトでは重複するキーが許可され、正常に解析できます。
```
--戻り値は 1 です。
SELECT GET_JSON_OBJECT('{"a":"1","a":"2"}', '$.a');
```
  - 絵文字に対応するエンコードされた文字列がサポートされています。ただし、DataWorks は絵文字入力をサポートしていません。Data Integration などのツールを使用して、絵文字に対応するエンコードされた文字列を MaxCompute に書き込み、GET_JSON_OBJECT 関数を使用して処理することしかできません。
```
--絵文字記号が返されます。
SELECT GET_JSON_OBJECT('{"a":"<Emoji symbol>"}', '$.a');
```
  - 出力結果は、JSON 文字列の元の順序で表示されます。
```
--戻り値は {"b":"1","a":"2"} です。
SELECT GET_JSON_OBJECT('{"b":{"b":"1","a":"2"},"a":"2"}', '$.b');
```
- SET odps.sql.udf.getjsonobj.new=false; コマンドを実行すると、関数は JSON 予約文字をエスケープして値を返します。この関数は、値を返すときに次のルールに準拠します:
  説明
  Hive 互換のデータ型エディションが有効になっている場合、GET_JSON_OBJECT 関数は元の文字列を保持して値を返します。
  - 改行 (\n) や引用符 (") などの JSON 予約文字は、'\n' および '\"' として表示されます。
  - JSON オブジェクトの各キーは一意である必要があります。重複キーが存在する場合、データの解析に失敗する可能性があります。
```
--戻り値は NULL です。
SELECT GET_JSON_OBJECT('{"a":"1","a":"2"}', '$.a');
```
  - 絵文字に対応するエンコードされた文字列は解析できません。
```
--戻り値は NULL です。
SELECT GET_JSON_OBJECT('{"a":"<Emoji symbol>"}', '$.a');
```
  - 出力結果はアルファベット順に表示されます。
```
--戻り値は {"a":"2","b":"1"} です。
SELECT GET_JSON_OBJECT('{"b":{"b":"1","a":"2"},"a":"2"}', '$.b');
```
説明
2021 年 1 月 21 日以降に作成された MaxCompute プロジェクトの場合、GET_JSON_OBJECT 関数のデフォルトの戻り値の動作は、元の文字列を保持することです。2021 年 1 月 21 日より前に作成された MaxCompute プロジェクトの場合、GET_JSON_OBJECT 関数のデフォルトの戻り値の動作は、JSON 予約文字をエスケープすることです。次のコマンドを実行して、MaxCompute プロジェクトで GET_JSON_OBJECT 関数が使用する動作を判別できます:
```
SELECT GET_JSON_OBJECT('{"a":"[\\"1\\"]"}', '$.a');
--エスケープ文字を使用して JSON 予約文字を返す:
[\"1\"]

--元の文字列を保持して返す:
["1"]
```
アプリケーションリンクを使用するか、DingTalk グループ番号 11782920 を検索して MaxCompute Developer Community DingTalk グループに参加できます。MaxCompute テクニカルサポートチームに連絡して、プロジェクトの GET_JSON_OBJECT 関数の戻り値の動作を元の文字列を保持するように切り替えることができます。これにより、セッションレベルでプロパティを頻繁に構成する必要がなくなります。

例

JSON オブジェクト src_json.json から情報を抽出します。例:

--JSON 文字列 src_json.json には次の内容が含まれています:
+----+
json
+----+
{"store":
{"fruit":[{"weight":8,"type":"apple"},{"weight":9,"type":"pear"}],
"bicycle":{"price":19.95,"color":"red"}
},
"email":"amy@only_for_json_udf_test.net",
"owner":"amy"
}
--owner フィールドの情報を抽出し、amy を返します。
SELECT GET_JSON_OBJECT(src_json.json, '$.owner') FROM src_json;
--store.fruit フィールドの最初の配列の情報を抽出し、{"weight":8,"type":"apple"} を返します。
SELECT GET_JSON_OBJECT(src_json.json, '$.store.fruit[0]') FROM src_json;
--存在しないフィールドの情報を抽出し、NULL を返します。
SELECT GET_JSON_OBJECT(src_json.json, '$.non_exist_key') FROM src_json;

ARRAY 型の JSON オブジェクトから情報を抽出します。例:

--戻り値は 2222 です。
SELECT GET_JSON_OBJECT('{"array":[["aaaa",1111],["bbbb",2222],["cccc",3333]]}','$.array[1][1]');

--戻り値は ["h0","h1","h2"] です。
SET odps.sql.udf.getjsonobj.new=true;
SELECT GET_JSON_OBJECT('{"aaa":"bbb","ccc":{"ddd":"eee","fff":"ggg","hhh":["h0","h1","h2"]},"iii":"jjj"}','$.ccc.hhh[*]');

--戻り値は ["h0","h1","h2"] です。
SET odps.sql.udf.getjsonobj.new=false;
SELECT GET_JSON_OBJECT('{"aaa":"bbb","ccc":{"ddd":"eee","fff":"ggg","hhh":["h0","h1","h2"]},"iii":"jjj"}','$.ccc.hhh[*]');

--戻り値は h1 です。
SELECT GET_JSON_OBJECT('{"aaa":"bbb","ccc":{"ddd":"eee","fff":"ggg","hhh":["h0","h1","h2"]},"iii":"jjj"}','$.ccc.hhh[1]');

ピリオド (.) を含む JSON オブジェクトから情報を抽出します。例:

--テーブルを作成します。
CREATE TABLE mf_json (id string, json string);
--テーブルにデータを挿入します。データのキーにはピリオド (.) が含まれています。
INSERT INTO TABLE mf_json (id, json) VALUES ("1", "{
\"China.beijing\":{\"school\":{\"id\":0,\"book\":[{\"title\": \"A\",
\"price\": 8.95},{\"title\": \"B\",\"price\": 10.2}]}}}");
--テーブルにデータを挿入します。データのキーにはピリオド (.) が含まれていません。
INSERT INTO TABLE mf_json (id, json) VALUES ("2", "{
\"China_beijing\":{\"school\":{\"id\":0,\"book\":[{\"title\": \"A\",
\"price\": 8.95},{\"title\": \"B\",\"price\": 10.2}]}}}");
--キーが China.beijing である JSON オブジェクトの id の値を照会します。値 0 が返されます。キーにピリオド (.) が含まれているため、キーを指定するには [''] のみを使用できます。
SELECT GET_JSON_OBJECT(json, "$['China.beijing'].school['id']") FROM mf_json WHERE id =1;
--キーが China_beijing である JSON オブジェクトの id の値を照会します。値 0 が返されます。次のいずれかの文を使用できます:
SELECT GET_JSON_OBJECT(json, "$['China_beijing'].school['id']") FROM mf_json WHERE id =2;
SELECT GET_JSON_OBJECT(json, "$.China_beijing.school['id']") FROM mf_json WHERE id =2;

json パラメーターが空または無効です。例:

--戻り値は NULL です。
SELECT GET_JSON_OBJECT('','$.array[1][1]');
--戻り値は NULL です。
SELECT GET_JSON_OBJECT('"array":["aaaa",1111],"bbbb":["cccc",3333]','$.array[1][1]');

JSON 文字列をエスケープします。例:

SET odps.sql.udf.getjsonobj.new=true;
--戻り値は "1" です。
SELECT GET_JSON_OBJECT('{"a":"\\"1\\"","b":"2"}', '$.a'); 
--戻り値は '1' です。
SELECT GET_JSON_OBJECT('{"a":"\'1\'","b":"2"}', '$.a');

JSON オブジェクトのキーにピリオド (.) が含まれている場合は、代わりに [''] を使用できます。例:
```
SET odps.sql.udf.getjsonobj.new=true;
--戻り値は "1" です。
SELECT GET_JSON_OBJECT('{"a.1":"1","a":"2"}', '$[\'a.1\']');
```