標準 SQL の JSON 関数

BigQuery は JSON 形式の文字列に格納されたデータを取得する関数と、データを JSON 形式の文字列に変換する関数をサポートしています。

JSON_EXTRACT または JSON_EXTRACT_SCALAR

JSON_EXTRACT(json_string_expr, json_path_string_literal) は、JSON 値を STRING として返します。

JSON_EXTRACT_SCALAR(json_string_expr, json_path_string_literal) は、スカラー JSON 値を STRING として返します。

説明

JSON 値または JSON スカラー値を文字列として抽出します。

  • json_string_expr: JSON 形式の文字列。例:

    {"class" : {"students" : [{"name" : "Jane"}]}}
        
  • json_path_string_literal: JSONpath 形式。これは、JSON 形式の文字列から取得する値を指定します。

JSON キーが無効な JSONPath 文字を使用した場合は、単一引用符や括弧を使用して、それらの文字をエスケープできます。

戻り値の型

STRING

SELECT JSON_EXTRACT(json_text, '$') AS json_text_string
    FROM UNNEST([
      '{"class" : {"students" : [{"name" : "Jane"}]}}',
      '{"class" : {"students" : []}}',
      '{"class" : {"students" : [{"name" : "John"}, {"name": "Jamie"}]}}'
      ]) AS json_text;

    +-----------------------------------------------------------+
    | json_text_string                                          |
    +-----------------------------------------------------------+
    | {"class":{"students":[{"name":"Jane"}]}}                  |
    | {"class":{"students":[]}}                                 |
    | {"class":{"students":[{"name":"John"},{"name":"Jamie"}]}} |
    +-----------------------------------------------------------+
    
SELECT JSON_EXTRACT(json_text, '$.class.students[0]') AS first_student
    FROM UNNEST([
      '{"class" : {"students" : [{"name" : "Jane"}]}}',
      '{"class" : {"students" : []}}',
      '{"class" : {"students" : [{"name" : "John"}, {"name": "Jamie"}]}}'
      ]) AS json_text;

    +-----------------+
    | first_student   |
    +-----------------+
    | {"name":"Jane"} |
    | NULL            |
    | {"name":"John"} |
    +-----------------+
    
SELECT JSON_EXTRACT(json_text, '$.class.students[1].name') AS second_student_name
    FROM UNNEST([
      '{"class" : {"students" : [{"name" : "Jane"}]}}',
      '{"class" : {"students" : []}}',
      '{"class" : {"students" : [{"name" : "John"}, {"name": "Jamie"}]}}'
      ]) AS json_text;

    +-------------------+
    | second_student    |
    +-------------------+
    | NULL              |
    | NULL              |
    | "Jamie"           |
    +-------------------+
    
SELECT JSON_EXTRACT(json_text, "$.class['students']") AS student_names
    FROM UNNEST([
      '{"class" : {"students" : [{"name" : "Jane"}]}}',
      '{"class" : {"students" : []}}',
      '{"class" : {"students" : [{"name" : "John"}, {"name": "Jamie"}]}}'
      ]) AS json_text;

    +------------------------------------+
    | student_names                      |
    +------------------------------------+
    | [{"name":"Jane"}]                  |
    | []                                 |
    | [{"name":"John"},{"name":"Jamie"}] |
    +------------------------------------+
    
SELECT JSON_EXTRACT('{ "name" : "Jakob", "age" : "6" }', '$.name') as json_name,
      JSON_EXTRACT_SCALAR('{ "name" : "Jakob", "age" : "6" }', '$.name') as scalar_name,
      JSON_EXTRACT('{ "name" : "Jakob", "age" : "6" }', '$.age') as json_age,
      JSON_EXTRACT_SCALAR('{ "name" : "Jakob", "age" : "6" }', '$.age') as scalar;

    +-----------+-------------+----------+--------+
    | json_name | scalar_name | json_age | scalar |
    +-----------+-------------+----------+--------+
    | "Jakob"   | Jakob       | "6"      | 6      |
    +-----------+-------------+----------+--------+
    

JSON キーが無効な JSONPath 文字を使用した場合は、単一引用符や括弧 [' '] を使用して、それらの文字をエスケープできます。例:

SELECT JSON_EXTRACT_SCALAR('{"a.b": {"c": "world"}}', "$['a.b'].c") as hello;

    +-------+
    | hello |
    +-------+
    | world |
    +-------+
    

TO_JSON_STRING

TO_JSON_STRING(value[, pretty_print])
    

説明

value の JSON 形式の文字列表現を返します。この関数では、省略可能な pretty_print パラメータを使用できます。pretty_print がある場合、戻り値は読みやすいように書式設定されています。

入力データ型 戻り値
任意の型の NULL null
BOOL true または false
INT64

value が [253, 253] の範囲内にある場合は、CAST(value AS STRING) と同じです。この範囲は IEEE 754 倍精度浮動小数点数としてロスレスに表現できる整数の範囲です。この範囲外となる値は、引用符付きの文字列で表されます。例:

-1
0
12345678901
9007199254740992
-9007199254740992
"9007199254740993"

9007199254740993 は 253 より大きいため、引用符付きの文字列として表されます。

NUMERIC

value が [-253, 253] の範囲内にあり、小数部が存在しない場合は、CAST(value AS STRING) と同じです。この範囲外となる値は、引用符付きの文字列で表されます。例:

-1
0
"9007199254740993"
"123.56"
FLOAT64 +/-infNaN は、それぞれ Infinity-InfinityNaN として表されます。

それ以外は、CAST(value AS STRING) と同じです。

STRING 引用符付きの文字列値。JSON 標準に従ってエスケープされます。具体的には "\、および U+0000 から U+001F までの制御文字がエスケープされます。
BYTES

引用符付きの RFC 4648 の base64 エスケープ値。例:

"R29vZ2xl" はバイト b"Google" の base64 表現です。

DATE

引用符付きの日付。例:

"2017-03-06"
TIMESTAMP

引用符付きの ISO 8601 の日時。T は日付と時刻を区切り、Zulu/UTC はタイムゾーンを表します。例:

"2017-03-06T12:34:56.789012Z"
DATETIME

引用符付きの ISO 8601 の日時。T は日付と時刻を区切ります。例:

"2017-03-06T12:34:56.789012"
TIME

引用符付きの ISO 8601 の時刻。例:

"12:34:56.789012"
ARRAY

[elem1,elem2,...]。各 elem は要素タイプに従って書式設定されます。

書式設定の例:

[
      elem1,
      elem2,
      ...
    ]

各 elem は、要素の種類に応じて書式設定されます。空の配列は [] と表されます。

STRUCT {"field_name1":field_value1,"field_name2":field_value2,...}

field_value は型に従って書式設定されます。

書式設定の例:

{
      "field_name1": field_value1,
      "field_name2": field_value2,
      ...
    }

field_value は型に従って書式設定されます。field_value が、空でない ARRAY または STRUCT である場合、要素は適切なレベルにインデントされます。空の STRUCT は {} と表されます。

名前が重複するフィールドがある場合、JSON が解析不能になる可能性があります。匿名のフィールドは "" と表されます。

無効な UTF-8 フィールド名がある場合、JSON が解析不能になる可能性があります。文字列値は、JSON 標準に従ってエスケープされます。具体的には "\、および U+0000 から U+001F までの制御文字がエスケープされます。

戻り値の型

値の JSON 文字列表現。

表内の行を JSON に変換します。

WITH Input AS (
      SELECT [1, 2] AS x, 'foo' AS y, STRUCT(true AS a, DATE '2017-04-05' AS b) AS s UNION ALL
      SELECT NULL AS x, '' AS y, STRUCT(false AS a, DATE '0001-01-01' AS b) AS s UNION ALL
      SELECT [3] AS x, 'bar' AS y, STRUCT(NULL AS a, DATE '2016-12-05' AS b) AS s
    )
    SELECT
      t,
      TO_JSON_STRING(t) AS json_row
    FROM Input AS t;
    

上のクエリは、次の結果を生成します。

+-----------------------------------+-------------------------------------------------------+
    | t                                 | json_row                                              |
    +-----------------------------------+-------------------------------------------------------+
    | {[1, 2], foo, {true, 2017-04-05}} | {"x":[1,2],"y":"foo","s":{"a":true,"b":"2017-04-05"}} |
    | {NULL, , {false, 0001-01-01}}     | {"x":null,"y":"","s":{"a":false,"b":"0001-01-01"}}    |
    | {[3], bar, {NULL, 2016-12-05}}    | {"x":[3],"y":"bar","s":{"a":null,"b":"2016-12-05"}}   |
    +-----------------------------------+-------------------------------------------------------+
    

表内の行を、書式設定付きの JSON に変換します。

WITH Input AS (
      SELECT [1, 2] AS x, 'foo' AS y, STRUCT(true AS a, DATE '2017-04-05' AS b) AS s UNION ALL
      SELECT NULL AS x, '' AS y, STRUCT(false AS a, DATE '0001-01-01' AS b) AS s UNION ALL
      SELECT [3] AS x, 'bar' AS y, STRUCT(NULL AS a, DATE '2016-12-05' AS b) AS s
    )
    SELECT
      TO_JSON_STRING(t, true) AS json_row
    FROM Input AS t;
    

上のクエリは、次の結果を生成します。

+-----------------------+
    | json_row              |
    +-----------------------+
    | {                     |
    |  "x": [               |
    |    1,                 |
    |    2                  |
    |  ],                   |
    |  "y": "foo",          |
    |  "s": {               |
    |    "a": true,         |
    |    "b": "2017-04-05"  |
    |  }                    |
    |}                      |
    | {                     |
    |  "x": null,           |
    |  "y": "",             |
    |  "s": {               |
    |    "a": false,        |
    |    "b": "0001-01-01"  |
    |  }                    |
    |}                      |
    | {                     |
    |  "x": [               |
    |    3                  |
    |  ],                   |
    |  "y": "bar",          |
    |  "s": {               |
    |    "a": null,         |
    |    "b": "2016-12-05"  |
    |  }                    |
    |}                      |
    +-----------------------+
    

JSONPath 形式

ほとんどの JSON 関数は json_string_expr パラメータと json_path_string_literal パラメータで渡されます。json_string_expr パラメータは JSON 形式の文字列で渡され、json_path_string_literal パラメータは JSON 形式の文字列から取得する値を指定します。

json_string_expr パラメータは次のような形式の JSON 文字列にする必要があります。

{"class" : {"students" : [{"name" : "Jane"}]}}
    

JSONPath 形式を使用して json_path_string_literal パラメータを作成します。このパラメータの書式は $ 記号で開始する必要があります。これは JSON 形式の文字列の最も外側のレベルを指すシンボルです。子の値は、ドットを使用して識別できます。JSON オブジェクトが配列の場合は、ブラケットを使用して配列のインデックスを指定できます。キーに $、ドット、かっこが含まれている場合、エスケープする方法については、各 JSON 関数をご覧ください。

JSONPath 説明 上記の json_string_expr を使用した結果
$ ルートのオブジェクトまたは要素 "$" {"class":{"students":[{"name":"Jane"}]}}
. 子の演算子 "$.class.students" [{"name":"Jane"}]
[] 添字演算子 "$.class.students[0]" {"name":"Jane"}

JSON 関数は、json_path_string_literal パラメータが json_string_expr 内の値と一致しない場合は NULL を返します。この関数は、スカラー関数に選択した値がオブジェクトや配列などのスカラー値ではない場合は、NULL を返します。

JSONPath が無効な場合、この関数はエラーとなります。