標準 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 形式文字列の文字列の値が配列に抽出されます。

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

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

-- Strips 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}] |
+-------------------------------------------------------+

次のものは同等です。

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

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

-- The queries above produce the following 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.
SELECT JSON_EXTRACT_ARRAY('["foo","bar","baz"]','INVALID_JSONPath') as result;

-- If the JSONPath does not refer to an array, NULL is returned.
SELECT JSON_EXTRACT_ARRAY('{"a":"foo"}','$.a') as result;

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

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

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

-- Empty arrays in JSON-formatted strings are supported.
SELECT 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 が true の場合、戻り値は読みやすいように書式設定されています。

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

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

-1
0
12345678901
9007199254740992
-9007199254740992
"9007199254740993"

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

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

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

それ以外は、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

0 個以上の要素の配列。各要素は型に従って書式設定されます。

書式設定なしの例:


["red", "blue", "green"]

書式設定付きの例:


[
  "red",
  "blue",
  "green"
]
STRUCT

0 個以上の Key-Value ペアを含むオブジェクト。各値は型に従って書式設定されます。

書式設定なしの例:


{"colors":["red","blue"],"purchases":12,"inStock": true}

書式設定付きの例:


{
  "color":[
    "red",
    "blue"
   ]
  "purchases":12,
  "inStock": true
}

名前が重複するフィールドがある場合、JSON が解析不能になる可能性があります。匿名のフィールドは "" と表されます。フィールドが空の配列またはオブジェクトである場合、要素またはフィールドは適切なレベルにインデントされます。

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

戻り値の型

値の JSON 文字列表現。

例

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

With CoordinatesTable AS (
    (SELECT 1 AS id, [10,20] AS coordinates) UNION ALL
    (SELECT 2 AS id, [30,40] AS coordinates) UNION ALL
    (SELECT 3 AS id, [50,60] AS coordinates))
SELECT id, coordinates, TO_JSON_STRING(t) AS json_data
FROM CoordinatesTable as t;

+----+-------------+--------------------------------+
| id | coordinates | json_data                      |
+----+-------------+--------------------------------+
| 1  | [10, 20]    | {"id":1,"coordinates":[10,20]} |
| 2  | [30, 40]    | {"id":2,"coordinates":[30,40]} |
| 3  | [50, 60]    | {"id":3,"coordinates":[50,60]} |
+----+-------------+--------------------------------+

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

With CoordinatesTable AS (
    (SELECT 1 AS id, [10,20] AS coordinates) UNION ALL
    (SELECT 2 AS id, [30,40] AS coordinates))
SELECT id, coordinates, TO_JSON_STRING(t, true) AS json_data
FROM CoordinatesTable as t;

+----+-------------+--------------------+
| id | coordinates | json_data          |
+----+-------------+--------------------+
| 1  | [10, 20]    | {                  |
|    |             |   "id": 1,         |
|    |             |   "coordinates": [ |
|    |             |     10,            |
|    |             |     20             |
|    |             |   ]                |
|    |             | }                  |
+----+-------------+--------------------+
| 2  | [30, 40]    | {                  |
|    |             |   "id": 2,         |
|    |             |   "coordinates": [ |
|    |             |     30,            |
|    |             |     40             |
|    |             |   ]                |
|    |             | }                  |
+----+-------------+--------------------+

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 が無効な場合、この関数はエラーとなります。