JSON_EXTRACT
Description
JSON_EXTRACTは、JSONデータからjson_pathで指定されたフィールドを抽出し、抽出するフィールドのタイプに応じて異なる一連の関数を提供する関数シリーズです。
- JSON_EXTRACTは、VARCHAR型のjson文字列に対してVARCHAR型を返します。
- JSON_EXTRACT_NO_QUOTESは、VARCHAR型のjson文字列に対してVARCHAR型を返します。JSONフィールドの値が文字列の場合、二重引用符が削除されます。
- JSON_EXTRACT_ISNULLは、json nullかどうかを示すBOOLEAN型を返します。
- JSON_EXTRACT_BOOLは、BOOLEAN型を返します。
- JSON_EXTRACT_INTは、INT型を返します。
- JSON_EXTRACT_BIGINTは、BIGINT型を返します。
- JSON_EXTRACT_LARGEINTは、LARGEINT型を返します。
- JSON_EXTRACT_DOUBLEは、DOUBLE型を返します。
- JSON_EXTRACT_STRINGは、STRING型を返します。
ヒント
JSON_EXTRACT_NO_QUOTES関数は、バージョン3.0.6以降でサポートされています。
Alias
- JSONB_EXTRACTは、JSON_EXTRACTと同じです。
- JSON_EXTRACT_NO_QUOTESは、JSON_EXTRACT_NO_QUOTESと同じです。
- JSONB_EXTRACT_ISNULLは、JSON_EXTRACT_ISNULLと同じです。
- JSONB_EXTRACT_BOOLは、JSON_EXTRACT_BOOLと同じです。
- JSONB_EXTRACT_INTは、JSON_EXTRACT_INTと同じです。
- JSONB_EXTRACT_BIGINTは、JSON_EXTRACT_BIGINTと同じです。
- JSONB_EXTRACT_LARGEINTは、JSON_EXTRACT_LARGEINTと同じです。
- JSONB_EXTRACT_DOUBLEは、JSON_EXTRACT_DOUBLEと同じです。
- JSONB_EXTRACT_STRINGは、JSON_EXTRACT_STRINGと同じです。
Syntax
JSON_EXTRACT (<json_str>, <path>[, path] ...)
JSON_EXTRACT_NO_QUOTES (<json_str>, <path>[, path] ...)
JSON_EXTRACT_ISNULL (<json_str>, <path>)
JSON_EXTRACT_BOOL (<json_str>, <path>)
JSON_EXTRACT_INT (<json_str>, <path>)
JSON_EXTRACT_BIGINT (<json_str>, <path>)
JSON_EXTRACT_LARGEINT (<json_str>, <path>)
JSON_EXTRACT_DOUBLE (<json_str>, <path>)
JSON_EXTRACT_STRING (<json_str>, <path>)
エイリアス関数は、関数名を除いて上記の関数と同じ構文と使用法を持ちます。
パラメータ
| パラメータ | 説明 |
|---|---|
<json_str> | 抽出対象のJSON型パラメータまたはフィールド。 |
<path> | 対象JSONから目的の要素を抽出するためのJSONパス。 |
| json pathの構文: |
- '$' はjsonドキュメントのルート
- '.k1' はキー'k1'を持つjsonオブジェクトの要素
- キー列の値に"."が含まれている場合、json_pathでは二重引用符が必要です。例: SELECT json_extract('{"k1.a":"abc","k2":300}', '$."k1.a"');
- '[i]' はインデックスiでのjson配列の要素
- json_arrayの最後の要素を取得するには'$[last]'を使用し、最後から2番目の要素を取得するには'$[last-1]'を使用します。以下同様です。
<path>は配列に対して自動ブロードキャストしません: JSON値が配列で<path>が$.kの場合、$.kはオブジェクトメンバーのみを走査するため、結果はNULLになります。インデックスで要素をターゲットにするには$[i].kを使用します。$[*].kによる配列ワイルドカードブロードキャストはDoris 4.0で導入されました; 2.1と3.xでは、代わりにLATERAL VIEW EXPLODEパターンを使用して要素ごとの値を抽出してください。
戻り値
抽出対象フィールドの型に応じて、対象JSONの指定されたJSON_PATHのデータ型を返します。特別なケースの処理は以下の通りです:
- json_pathで指定されたフィールドがJSONに存在しない場合、NULLを返します。それ以外の場合、存在しないフィールドをスキップします。
- JSONのjson_pathで指定されたフィールドの実際の型が、json_extract_tで指定された型と一致しない場合。
- 指定された型に無損失で変換できる場合、指定された型tを返します; できない場合、NULLを返します。
例
SELECT json_extract('{"id": 123, "name": "doris"}', '$.id');
+------------------------------------------------------+
| json_extract('{"id": 123, "name": "doris"}', '$.id') |
+------------------------------------------------------+
| 123 |
+------------------------------------------------------+
SELECT json_extract('[1, 2, 3]', '$.[1]');
+------------------------------------+
| json_extract('[1, 2, 3]', '$.[1]') |
+------------------------------------+
| 2 |
+------------------------------------+
SELECT json_extract('{"k1": "v1", "k2": { "k21": 6.6, "k22": [1, 2] } }', '$.k1', '$.k2.k21', '$.k2.k22', '$.k2.k22[1]');
+-------------------------------------------------------------------------------------------------------------------+
| json_extract('{"k1": "v1", "k2": { "k21": 6.6, "k22": [1, 2] } }', '$.k1', '$.k2.k21', '$.k2.k22', '$.k2.k22[1]') |
+-------------------------------------------------------------------------------------------------------------------+
| ["v1",6.6,[1,2],2] |
+-------------------------------------------------------------------------------------------------------------------+
SELECT json_extract('{"id": 123, "name": "doris"}', '$.aaa', '$.name');
+-----------------------------------------------------------------+
| json_extract('{"id": 123, "name": "doris"}', '$.aaa', '$.name') |
+-----------------------------------------------------------------+
| ["doris"] |
+-----------------------------------------------------------------+
SELECT json_extract_no_quotes('{"id": 123, "name": "doris"}', '$.name');
+------------------------------------------------------------------+
| json_extract_no_quotes('{"id": 123, "name": "doris"}', '$.name') |
+------------------------------------------------------------------+
| doris |
+------------------------------------------------------------------+
SELECT JSON_EXTRACT_ISNULL('{"id": 123, "name": "doris"}', '$.id');
+----------------------------------------------------------------------------+
| jsonb_extract_isnull(cast('{"id": 123, "name": "doris"}' as JSON), '$.id') |
+----------------------------------------------------------------------------+
| 0 |
+----------------------------------------------------------------------------+
SELECT JSON_EXTRACT_BOOL('{"id": 123, "name": "NULL"}', '$.id');
+-------------------------------------------------------------------------+
| jsonb_extract_bool(cast('{"id": 123, "name": "NULL"}' as JSON), '$.id') |
+-------------------------------------------------------------------------+
| NULL |
+-------------------------------------------------------------------------+
SELECT JSON_EXTRACT_INT('{"id": 123, "name": "NULL"}', '$.id');
+------------------------------------------------------------------------+
| jsonb_extract_int(cast('{"id": 123, "name": "NULL"}' as JSON), '$.id') |
+------------------------------------------------------------------------+
| 123 |
+------------------------------------------------------------------------+
SELECT JSON_EXTRACT_INT('{"id": 123, "name": "doris"}', '$.name');
+---------------------------------------------------------------------------+
| jsonb_extract_int(cast('{"id": 123, "name": "doris"}' as JSON), '$.name') |
+---------------------------------------------------------------------------+
| NULL |
+---------------------------------------------------------------------------+
SELECT JSON_EXTRACT_STRING('{"id": 123, "name": "doris"}', '$.name');
+------------------------------------------------------------------------------+
| jsonb_extract_string(cast('{"id": 123, "name": "doris"}' as JSON), '$.name') |
+------------------------------------------------------------------------------+
| doris |
+------------------------------------------------------------------------------+