標準 SQL の JSON 関数

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

JSON_EXTRACT または JSON_EXTRACT_SCALAR

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

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

説明

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

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

    {"class" : {"students" : [{"name" : "Jane"}]}}
    
  • json_path_format: JSONpath 形式。これは、JSON 形式の文字列から取得する値を指定します。json_path_format が JSON null を返す場合、これは SQL NULL に変換されます。

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" : null}]}}',
  '{"class" : {"students" : [{"name" : "John"}, {"name": "Jamie"}]}}'
  ]) AS json_text;

+-------------------+
| second_student    |
+-------------------+
| NULL              |
| 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 |
+-------+

JSON_EXTRACT_ARRAY

JSON_EXTRACT_ARRAY(json_string_expr[, json_path_format])

説明

JSON 形式の文字列から配列を抽出します。

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

    {"class" : {"students" : [{"name" : "Jane"}]}}
    
  • json_path_format: JSONpath 形式。これは、JSON 形式の文字列から取得する値を指定します。この省略可能なパラメータが指定されていない場合は、JSONPath $ 記号が適用され、JSON 形式の文字列全体が分析されます。

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

戻り値の型

ARRAY<STRING>

これにより、JSON 形式の文字列内の項目が文字列配列に抽出されます。

SELECT JSON_EXTRACT_ARRAY('[1,2,3]') as string_array

+----------------+
| string_array   |
+----------------+
| ['1','2','3']  |
+----------------+

文字列配列が抽出され、整数配列に変換されます。

SELECT ARRAY(
  SELECT CAST(integer_element as INT64)
  FROM UNNEST(
    JSON_EXTRACT_ARRAY('[1,2,3]','$')
  ) AS integer_element
) AS integer_array

+---------------+
| integer_array |
+---------------+
| [1,2,3]       |
+---------------+

JSON 形式文字列の文字列の値が配列に抽出されます。

-- Don't strip the double quotes
SELECT JSON_EXTRACT_ARRAY('["apples","oranges","grapes"]', '$') as string_array

+--------------------------------------+
| string_array                         |
+--------------------------------------+
| ['"apples"','"oranges"','"grapes"']  |
+--------------------------------------+

-- Strip the double quotes
SELECT ARRAY(
  SELECT JSON_EXTRACT_SCALAR(string_element, '$')
  FROM UNNEST(JSON_EXTRACT_ARRAY('["apples","oranges","grapes"]','$')) AS string_element
) AS string_array

+---------------------------------+
|  string_array                   |
+---------------------------------+
| ['apples', 'oranges', 'grapes'] |
+---------------------------------+

これにより、fruit 内の項目のみが配列に抽出されます。

SELECT JSON_EXTRACT_ARRAY(
  '{"fruit":[{"apples":5,"oranges":10},{"apples":2,"oranges":4}],"vegetables":[{"lettuce":7,"kale": 8}}',
  '$.fruit'
) as string_array

+----------------------------------------------------------------------+
| string_array                                                         |
+----------------------------------------------------------------------+
| ['{"apples" : 5, "oranges" : 10}' , '{"apples" : 2, "oranges" : 4}'] |
+----------------------------------------------------------------------+

次のものは同等です。

JSON_EXTRACT_ARRAY('{"fruits":["apples","oranges","grapes"]}','$[fruits]') as string_array

JSON_EXTRACT_ARRAY('{"fruits":["apples","oranges","grapes"]}','$.fruits') as string_array

-- The queries above produce this result:
+---------------------------------------+
|  string_array                         |
+---------------------------------------+
| [""apples"", ""oranges"", ""grapes""] |
+---------------------------------------+

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

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

+---------+
| hello   |
+---------+
| [world] |
+---------+

次の例は、無効なリクエストと空の配列の処理方法を示しています。

  • JSONPath が無効な場合は、エラーがスローされます。
  • JSON 形式の文字列が無効の場合、出力は NULL になります。
  • JSON 形式の文字列に空の配列を含めることは可能です。
-- An error is thrown if you provide an invalid JSONPath.
JSON_EXTRACT_ARRAY('["foo","bar","baz"]','INVALID_JSONPath') as result

-- If a key is not specified when a key is expected, the result is NULL.
JSON_EXTRACT_ARRAY('{"a":"foo"}','$') as result

+--------+
| result |
+--------+
| NULL   |
+--------+

-- If a key that does not exist is specified, the result is NULL.
JSON_EXTRACT_ARRAY('{"a":"foo"}','$.b') as result

+--------+
| result |
+--------+
| NULL   |
+--------+

-- Empty arrays in JSON-formatted strings are supported.
JSON_EXTRACT_ARRAY('{"a":"foo","b":[]}','$.b') as result

+--------+
| result |
+--------+
| []     |
+--------+

JSON_QUERY または JSON_VALUE

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

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

説明

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

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

    {"class" : {"students" : [{"name" : "Jane"}]}}
    
  • json_path_format: JSONpath 形式。これは、JSON 形式の文字列から取得する値を指定します。json_path_format が JSON null を返す場合、これは SQL NULL に変換されます。

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

SELECT JSON_QUERY(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_QUERY(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_QUERY(json_text, '$.class.students[1].name') AS second_student_name
FROM UNNEST([
  '{"class" : {"students" : [{"name" : "Jane"}]}}',
  '{"class" : {"students" : []}}',
  '{"class" : {"students" : [{"name" : "John"}, {"name" : null}]}}',
  '{"class" : {"students" : [{"name" : "John"}, {"name": "Jamie"}]}}'
  ]) AS json_text;

+-------------------+
| second_student    |
+-------------------+
| NULL              |
| NULL              |
| NULL              |
| "Jamie"           |
+-------------------+
SELECT JSON_QUERY(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_QUERY('{ "name" : "Jakob", "age" : "6" }', '$.name') as json_name,
  JSON_VALUE('{ "name" : "Jakob", "age" : "6" }', '$.name') as scalar_name,
  JSON_QUERY('{ "name" : "Jakob", "age" : "6" }', '$.age') as json_age,
  JSON_VALUE('{ "name" : "Jakob", "age" : "6" }', '$.age') as scalar;

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

JSON キーが無効な JSONPath 文字を使用した場合は、二重引用符を使用して、それらの文字をエスケープできます。例:

SELECT JSON_VALUE('{"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_format パラメータで渡されます。json_string_expr パラメータは JSON 形式の文字列で渡され、json_path_format パラメータは JSON 形式の文字列から取得する値を指定します。

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

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

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

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

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

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