カテゴリ:

半構造化データ関数と構造化データ関数 (抽出)

JSON_EXTRACT_PATH_TEXT

最初の引数を JSON 文字列として解析し、2番目の引数のパスが指す要素の値を返します。これは、 TO_VARCHAR(GET_PATH(PARSE_JSON(JSON), PATH)) と同等です

構文

JSON_EXTRACT_PATH_TEXT( <column_identifier> , '<path_name>' )
Copy

引数

column_identifier

抽出するデータを含む列の名前。

path_name

抽出する要素へのパスを含む文字列。

戻り値

戻り値のデータ型はVARCHARです。

使用上の注意

  • この関数は、パス名がどの要素にも対応しない場合に NULL を返します。

  • パス名の構文は標準の JavaScript 表記です。これは、ピリオド(例: .)とインデックス演算子(例: [<インデックス>])が前に付いたフィールド名(識別子)の連結で構成されます。

    • 最初のフィールド名には、先行ピリオドを指定する必要はありません。

    • インデックス演算子のインデックス値は、負でない整数(配列の場合)または一重引用符または二重引用符で囲まれた文字列リテラル(オブジェクトフィールドの場合)です。

    詳細については、 半構造化データのクエリ をご参照ください。

  • 構文の一貫性を維持するために、パス表記は SQL スタイルの二重引用符で囲まれた識別子もサポートし、パス区切り文字として : を使用します。

テーブルを作成して値を挿入します。

CREATE TABLE demo1 (id INTEGER, json_data VARCHAR);
INSERT INTO demo1 SELECT
   1, '{"level_1_key": "level_1_value"}';
INSERT INTO demo1 SELECT
   2, '{"level_1_key": {"level_2_key": "level_2_value"}}';
INSERT INTO demo1 SELECT
   3, '{"level_1_key": {"level_2_key": ["zero", "one", "two"]}}';
Copy

JSON_EXTRACT_PATH_TEXT を使用して、単純な1レベルの文字列から値を抽出します。

SELECT 
        TO_VARCHAR(GET_PATH(PARSE_JSON(json_data), 'level_1_key')) 
            AS OLD_WAY,
        JSON_EXTRACT_PATH_TEXT(json_data, 'level_1_key')
            AS JSON_EXTRACT_PATH_TEXT
    FROM demo1
    ORDER BY id;
+--------------------------------------+--------------------------------------+
| OLD_WAY                              | JSON_EXTRACT_PATH_TEXT               |
|--------------------------------------+--------------------------------------|
| level_1_value                        | level_1_value                        |
| {"level_2_key":"level_2_value"}      | {"level_2_key":"level_2_value"}      |
| {"level_2_key":["zero","one","two"]} | {"level_2_key":["zero","one","two"]} |
+--------------------------------------+--------------------------------------+
Copy

2レベルのパスを使用して2レベルの文字列から値を抽出するには、 JSON_EXTRACT_PATH_TEXT を使用します。

SELECT 
        TO_VARCHAR(GET_PATH(PARSE_JSON(json_data), 'level_1_key.level_2_key'))
            AS OLD_WAY,
        JSON_EXTRACT_PATH_TEXT(json_data, 'level_1_key.level_2_key')
            AS JSON_EXTRACT_PATH_TEXT
    FROM demo1
    ORDER BY id;
+----------------------+------------------------+
| OLD_WAY              | JSON_EXTRACT_PATH_TEXT |
|----------------------+------------------------|
| NULL                 | NULL                   |
| level_2_value        | level_2_value          |
| ["zero","one","two"] | ["zero","one","two"]   |
+----------------------+------------------------+
Copy

この例には配列が含まれています。

SELECT 
      TO_VARCHAR(GET_PATH(PARSE_JSON(json_data), 'level_1_key.level_2_key[1]'))
          AS OLD_WAY,
      JSON_EXTRACT_PATH_TEXT(json_data, 'level_1_key.level_2_key[1]')
          AS JSON_EXTRACT_PATH_TEXT
    FROM demo1
    ORDER BY id;
+---------+------------------------+
| OLD_WAY | JSON_EXTRACT_PATH_TEXT |
|---------+------------------------|
| NULL    | NULL                   |
| NULL    | NULL                   |
| one     | one                    |
+---------+------------------------+
Copy