メインコンテンツまでスキップ
バージョン: 2.1

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型を返します。

エイリアス​

  • 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と同じです。

構文​

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>)

Alias関数は、関数名以外は上記の関数と同じ構文と使用方法を持ちます。

パラメータ​

パラメータ説明
<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]'を使用します。以下同様です。

戻り値​

抽出対象フィールドの型に応じて、対象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                                                                        |
+------------------------------------------------------------------------------+