JSON_EXTRACT
詳細
JSON_EXTRACTは、JSONデータからjson_pathで指定されたフィールドを抽出し、抽出するフィールドのタイプに応じて異なる一連の関数を提供する関数シリーズです。
- JSON_EXTRACTはVARCHARタイプのjson文字列に対してVARCHARタイプを返します。
- 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タイプを返します。
Alias
- JSONB_EXTRACTはJSON_EXTRACTと同じです。
- 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_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 documentのルートを表す
- '.k1' はキー'k1'を持つjson objectの要素を表す
- キー列の値に"."が含まれる場合、json_pathでは二重引用符が必要です。例: SELECT json_extract('{"k1.a":"abc","k2":300}', '$."k1.a"');
- '[i]' はインデックスiにおけるjson arrayの要素を表す
- json_arrayの最後の要素を取得するには'[last-1]'を使用します。以下同様。
戻り値
抽出対象フィールドの型に応じて、対象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') |
+-----------------------------------------------------------------+
| [null,"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 |
+------------------------------------------------------------------------------+
