JSON 関数

BigQuery は、JSON データを取得して変換できる次の関数をサポートしています。

関数の概要

次の関数は二重引用符を使用し、無効な JSONPath 文字 "a.b" をエスケープします。

この動作は ANSI 標準を遵守しています。

JSON 関数 説明 戻り値の型
JSON_QUERY JSON 値（配列やオブジェクトなど）または JSON スカラー値（文字列、数値、ブール値など）を抽出します。 JSON 形式の STRING または JSON
JSON_VALUE スカラー値を抽出します。スカラー値は、文字列、数値、またはブール値を表すことができます。最も外側の引用符を削除し、値のエスケープを解除します。スカラー以外の値が選択されている場合、SQL NULL を返します。 STRING
JSON_QUERY_ARRAY 配列やオブジェクトなどの JSON 値の配列と、文字列、数値、ブール値などの JSON スカラー値を抽出します。 ARRAY<JSON-formatted STRING> または ARRAY<JSON>
JSON_VALUE_ARRAY スカラー値の配列を抽出します。スカラー値は、文字列、数値、またはブール値を表すことができます。最も外側の引用符を削除し、値のエスケープを解除します。選択した値が配列でない場合、またはスカラー値のみを含む配列でない場合、SQL NULL を返します。 ARRAY<STRING>

レガシー JSON 抽出関数

次の関数は一重引用符と括弧を使用し、無効な JSONPath 文字 ['a.b'] をエスケープします。

これらの関数は BigQuery でサポートされていますが、上記のテーブルの関数を使用することをおすすめします。

JSON 関数 説明 戻り値の型
JSON_EXTRACT JSON 値（配列やオブジェクトなど）または JSON スカラー値（文字列、数値、ブール値など）を抽出します。 JSON 形式の STRING または JSON
JSON_EXTRACT_SCALAR スカラー値を抽出します。スカラー値は、文字列、数値、またはブール値を表すことができます。最も外側の引用符を削除し、値のエスケープを解除します。スカラー以外の値が選択されている場合、SQL NULL を返します。 STRING
JSON_EXTRACT_ARRAY 配列やオブジェクトなどの JSON 値の配列と、文字列、数値、ブール値などの JSON スカラー値を抽出します。 ARRAY<JSON-formatted STRING> または ARRAY<JSON>
JSON_EXTRACT_STRING_ARRAY スカラー値の配列を抽出します。スカラー値は、文字列、数値、またはブール値を表すことができます。最も外側の引用符を削除し、値のエスケープを解除します。選択した値が配列でない場合、またはスカラー値のみを含む配列でない場合、SQL NULL を返します。 ARRAY<STRING>

その他の JSON 関数

JSON 関数 説明 戻り値の型
PARSE_JSON JSON 形式の文字列を取り、JSON 値を返します。 JSON
TO_JSON SQL 値を受け取って、JSON 値を返します。 JSON
TO_JSON_STRING SQL 値を取り、値の JSON 形式の文字列表現を返します。 JSON 形式の STRING
STRING JSON から文字列を抽出します。 STRING
BOOL JSON からブール値を抽出します。 BOOL
INT64 JSON から 64 ビット整数を抽出します。 INT64
FLOAT64 JSON から 64 ビット浮動小数点数を抽出します。 FLOAT64
JSON_TYPE 最も外側の JSON 値の型を文字列として返します。 STRING

JSON_EXTRACT

JSON_EXTRACT(json_string_expr, json_path)

JSON_EXTRACT(json_expr, json_path)

説明

JSON 値（配列やオブジェクトなど）または JSON スカラー値（文字列、数値、ブール値など）を抽出します。JSON キーが無効な JSONPath 文字を使用する場合は、単一引用符や括弧を使用して、それらの文字をエスケープできます。

  • json_string_expr: JSON 形式の文字列。次に例を示します。

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

    JSON 形式の文字列 null が検出されると、SQL NULL を抽出します。 次に例を示します。

    SELECT JSON_EXTRACT("null", "$") -- Returns a SQL NULL

  • json_expr: JSON。次に例を示します。

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

    JSON null が検出されると、JSON null を抽出します。

    SELECT JSON_EXTRACT(JSON 'null', "$") -- Returns a JSON 'null'

  • json_path: JSONPath。これにより、入力から取得するデータが識別されます。

戻り値の型

  • json_string_expr: JSON 形式の STRING
  • json_expr: JSON

例

次の例では、JSON データが JSON として抽出され返されます。

SELECT
  JSON_EXTRACT(JSON '{"class":{"students":[{"id":5},{"id":12}]}}', '$.class')
  AS json_data;

+-----------------------------------+
| json_data                         |
+-----------------------------------+
| {"students":[{"id":5},{"id":12}]} |
+-----------------------------------+

次の例では、JSON データが抽出され、JSON 形式の文字列として返されます。

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('{"a":null}', "$.a"); -- Returns a SQL NULL
SELECT JSON_EXTRACT('{"a":null}', "$.b"); -- Returns a SQL NULL

SELECT JSON_EXTRACT(JSON '{"a":null}', "$.a"); -- Returns a JSON 'null'
SELECT JSON_EXTRACT(JSON '{"a":null}', "$.b"); -- Returns a SQL NULL

JSON_QUERY

JSON_QUERY(json_string_expr, json_path)

JSON_QUERY(json_expr, json_path)

説明

JSON 値（配列やオブジェクトなど）または JSON スカラー値（文字列、数値、ブール値など）を抽出します。JSON キーが無効な JSONPath 文字を使用した場合は、二重引用符を使用して、それらの文字をエスケープできます。

  • json_string_expr: JSON 形式の文字列。次に例を示します。

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

    JSON 形式の文字列 null が検出されると、SQL NULL を抽出します。 次に例を示します。

    SELECT JSON_QUERY("null", "$") -- Returns a SQL NULL

  • json_expr: JSON。次に例を示します。

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

    JSON null が検出されると、JSON null を抽出します。

    SELECT JSON_QUERY(JSON 'null', "$") -- Returns a JSON 'null'

  • json_path: JSONPath。これにより、入力から取得するデータが識別されます。

戻り値の型

  • json_string_expr: JSON 形式の STRING
  • json_expr: JSON

例

次の例では、JSON データが JSON として抽出され返されます。

SELECT
  JSON_QUERY(JSON '{"class":{"students":[{"id":5},{"id":12}]}}', '$.class')
  AS json_data;

+-----------------------------------+
| json_data                         |
+-----------------------------------+
| {"students":[{"id":5},{"id":12}]} |
+-----------------------------------+

次の例では、JSON データが抽出され、JSON 形式の文字列として返されます。

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('{"a":null}', "$.a"); -- Returns a SQL NULL
SELECT JSON_QUERY('{"a":null}', "$.b"); -- Returns a SQL NULL

SELECT JSON_QUERY(JSON '{"a":null}', "$.a"); -- Returns a JSON 'null'
SELECT JSON_QUERY(JSON '{"a":null}', "$.b"); -- Returns a SQL NULL

JSON_EXTRACT_SCALAR

JSON_EXTRACT_SCALAR(json_string_expr[, json_path])

JSON_EXTRACT_SCALAR(json_expr[, json_path])

説明

スカラー値を抽出して文字列として返します。スカラー値は、文字列、数値、またはブール値を表すことができます。最も外側の引用符を削除し、戻り値のエスケープを解除します。JSON キーが無効な JSONPath 文字を使用する場合は、単一引用符や括弧を使用して、それらの文字をエスケープできます。

  • json_string_expr: JSON 形式の文字列。次に例を示します。

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

  • json_expr: JSON。次に例を示します。

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

  • json_path: JSONPath。これにより、入力から取得するデータが識別されます。このオプション パラメータが指定されていない場合は、JSONPath $ 記号が適用されます。つまり、すべてのデータが分析されます。

    json_path が JSON null またはスカラーではない値を返す場合（つまり、json_path がオブジェクトまたは配列を参照している場合）、SQL NULL が返されます。

戻り値の型

STRING

例

次の例では、age が抽出されます。

SELECT JSON_EXTRACT_SCALAR(JSON '{ "name" : "Jakob", "age" : "6" }', '$.age') AS scalar_age;

+------------+
| scalar_age |
+------------+
| 6          |
+------------+

次の例では、JSON_EXTRACT 関数と JSON_EXTRACT_SCALAR 関数で結果を返す方法を比較しています。

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_age;

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

SELECT JSON_EXTRACT('{"fruits": ["apple", "banana"]}', '$.fruits') AS json_extract,
  JSON_EXTRACT_SCALAR('{"fruits": ["apple", "banana"]}', '$.fruits') AS json_extract_scalar;

+--------------------+---------------------+
| json_extract       | json_extract_scalar |
+--------------------+---------------------+
| ["apple","banana"] | NULL                |
+--------------------+---------------------+

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

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

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

JSON_VALUE

JSON_VALUE(json_string_expr[, json_path])

JSON_VALUE(json_expr[, json_path])

説明

スカラー値を抽出して文字列として返します。スカラー値は、文字列、数値、またはブール値を表すことができます。最も外側の引用符を削除し、戻り値のエスケープを解除します。JSON キーが無効な JSONPath 文字を使用した場合は、二重引用符を使用して、それらの文字をエスケープできます。

  • json_string_expr: JSON 形式の文字列。次に例を示します。

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

  • json_expr: JSON。次に例を示します。

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

  • json_path: JSONPath。これにより、入力から取得するデータが識別されます。このオプション パラメータが指定されていない場合は、JSONPath $ 記号が適用されます。つまり、すべてのデータが分析されます。

    json_path が JSON null またはスカラーではない値を返す場合（つまり、json_path がオブジェクトまたは配列を参照している場合）、SQL NULL が返されます。

戻り値の型

STRING

例

次の例では、JSON データが抽出され、スカラー値として返されます。

SELECT JSON_VALUE(JSON '{ "name" : "Jakob", "age" : "6" }', '$.age') AS scalar_age;

+------------+
| scalar_age |
+------------+
| 6          |
+------------+

次の例では、JSON_QUERY 関数と JSON_VALUE 関数で結果を返す方法を比較しています。

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_age;

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

SELECT JSON_QUERY('{"fruits": ["apple", "banana"]}', '$.fruits') AS json_query,
  JSON_VALUE('{"fruits": ["apple", "banana"]}', '$.fruits') AS json_value;

+--------------------+------------+
| json_query         | json_value |
+--------------------+------------+
| ["apple","banana"] | NULL       |
+--------------------+------------+

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

SELECT JSON_VALUE('{"a.b": {"c": "world"}}', '$."a.b".c') AS hello;

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

JSON_EXTRACT_ARRAY

JSON_EXTRACT_ARRAY(json_string_expr[, json_path])

JSON_EXTRACT_ARRAY(json_expr[, json_path])

説明

配列やオブジェクトなどの JSON 値の配列と、文字列、数値、ブール値などの JSON スカラー値を抽出します。JSON キーが無効な JSONPath 文字を使用する場合は、単一引用符や括弧を使用して、それらの文字をエスケープできます。

  • json_string_expr: JSON 形式の文字列。次に例を示します。

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

  • json_expr: JSON。次に例を示します。

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

  • json_path: JSONPath。これにより、入力から取得するデータが識別されます。このオプション パラメータが指定されていない場合は、JSONPath $ 記号が適用されます。つまり、すべてのデータが分析されます。

戻り値の型

  • json_string_expr: ARRAY<JSON-formatted STRING>
  • json_expr: ARRAY<JSON>

例

JSON 内の項目が JSON 値の配列に抽出されます。

SELECT JSON_EXTRACT_ARRAY(
  JSON '{"fruits":["apples","oranges","grapes"]}','$.fruits'
  ) AS json_array;

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

これにより、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, then NULL is returned.
SELECT JSON_EXTRACT_ARRAY('{"a":"foo"}','$.a') AS result;

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

-- If a key that does not exist is specified, then 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_ARRAY

JSON_QUERY_ARRAY(json_string_expr[, json_path])

JSON_QUERY_ARRAY(json_expr[, json_path])

説明

配列やオブジェクトなどの JSON 値の配列と、文字列、数値、ブール値などの JSON スカラー値を抽出します。JSON キーが無効な JSONPath 文字を使用した場合は、二重引用符を使用して、それらの文字をエスケープできます。

  • json_string_expr: JSON 形式の文字列。次に例を示します。

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

  • json_expr: JSON。次に例を示します。

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

  • json_path: JSONPath。これにより、入力から取得するデータが識別されます。このオプション パラメータが指定されていない場合は、JSONPath $ 記号が適用されます。つまり、すべてのデータが分析されます。

戻り値の型

  • json_string_expr: ARRAY<JSON-formatted STRING>
  • json_expr: ARRAY<JSON>

例

JSON 内の項目が JSON 値の配列に抽出されます。

SELECT JSON_QUERY_ARRAY(
  JSON '{"fruits":["apples","oranges","grapes"]}','$.fruits'
  ) AS json_array;

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

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

SELECT JSON_QUERY_ARRAY('[1,2,3]') AS string_array;

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

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

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

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

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

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

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

-- Strips the double quotes
SELECT ARRAY(
  SELECT JSON_VALUE(string_element, '$')
  FROM UNNEST(JSON_QUERY_ARRAY('["apples","oranges","grapes"]','$')) AS string_element
) AS string_array;

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

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

SELECT JSON_QUERY_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_QUERY_ARRAY('{"fruits":["apples","oranges","grapes"]}','$.fruits') AS string_array;

SELECT JSON_QUERY_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_QUERY_ARRAY('{"a.b": {"c": ["world"]}}', '$."a.b".c') AS hello;

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

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

-- An error is returned if you provide an invalid JSONPath.
SELECT JSON_QUERY_ARRAY('["foo","bar","baz"]','INVALID_JSONPath') AS result;

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

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

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

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

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

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

JSON_EXTRACT_STRING_ARRAY

JSON_EXTRACT_STRING_ARRAY(json_string_expr[, json_path])

JSON_EXTRACT_STRING_ARRAY(json_expr[, json_path])

説明

スカラー値の配列を抽出し、文字列形式のスカラー値を返します。スカラー値は、文字列、数値、またはブール値を表すことができます。 JSON キーが無効な JSONPath 文字を使用する場合は、単一引用符や括弧を使用して、それらの文字をエスケープできます。

  • json_string_expr: JSON 形式の文字列。次に例を示します。

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

  • json_expr: JSON。次に例を示します。

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

  • json_path: JSONPath。これにより、入力から取得するデータが識別されます。このオプション パラメータが指定されていない場合は、JSONPath $ 記号が適用されます。つまり、すべてのデータが分析されます。

注意点:

  • 入力配列内に JSON null がある場合は、その JSON null の出力として SQL NULL が生成されます。出力に NULL 配列要素が含まれている場合、最終出力の配列に NULL 値を含めることはできないため、エラーが発生します。
  • JSONPath は、スカラー オブジェクトと JSON null を含む配列と一致する場合、最終的な出力を NULL 値を含む配列にすることはできないため、関数の出力は変換する必要があります。

戻り値の型

ARRAY<STRING>

例

JSON 内の項目が文字列配列に抽出されます。

SELECT JSON_EXTRACT_STRING_ARRAY(
  JSON '{"fruits":["apples","oranges","grapes"]}','$.fruits'
  ) AS string_array;

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

次の例では、JSON_EXTRACT_ARRAY 関数と JSON_EXTRACT_STRING_ARRAY 関数で結果を返す方法を比較しています。

SELECT JSON_EXTRACT_ARRAY('["apples","oranges"]') AS json_array,
JSON_EXTRACT_STRING_ARRAY('["apples","oranges"]') AS string_array;

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

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

-- Strips the double quotes
SELECT JSON_EXTRACT_STRING_ARRAY('["foo","bar","baz"]','$') AS string_array;

+-----------------+
| string_array    |
+-----------------+
| [foo, bar, baz] |
+-----------------+

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

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

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

次のものは同等です。

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

SELECT JSON_EXTRACT_STRING_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_STRING_ARRAY('{"a.b": {"c": ["world"]}}', "$['a.b'].c") AS hello;

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

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

-- An error is thrown if you provide an invalid JSONPath.
SELECT JSON_EXTRACT_STRING_ARRAY('["foo","bar","baz"]','INVALID_JSONPath') AS result;

-- If the JSON formatted string is invalid, then NULL is returned.
SELECT JSON_EXTRACT_STRING_ARRAY('}}','$') AS result;

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

-- If the JSON document is NULL, then NULL is returned.
SELECT JSON_EXTRACT_STRING_ARRAY(NULL,'$') AS result;

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

-- If a JSONPath does not match anything, then the output is NULL.
SELECT JSON_EXTRACT_STRING_ARRAY('{"a":["foo","bar","baz"]}','$.b') AS result;

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

-- If a JSONPath matches an object that is not an array, then the output is NULL.
SELECT JSON_EXTRACT_STRING_ARRAY('{"a":"foo"}','$') AS result;

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

-- If a JSONPath matches an array of non-scalar objects, then the output is NULL.
SELECT JSON_EXTRACT_STRING_ARRAY('{"a":[{"b":"foo","c":1},{"b":"bar","c":2}],"d":"baz"}','$.a') AS result;

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

-- If a JSONPath matches an array of mixed scalar and non-scalar objects, then the output is NULL.
SELECT JSON_EXTRACT_STRING_ARRAY('{"a":[10, {"b": 20}]','$.a') AS result;

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

-- If a JSONPath matches an empty JSON array, then the output is an empty array instead of NULL.
SELECT JSON_EXTRACT_STRING_ARRAY('{"a":"foo","b":[]}','$.b') AS result;

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

-- The following query produces and error because the final output cannot be an
-- array with NULLs.
SELECT JSON_EXTRACT_STRING_ARRAY('["world", 1, null]') AS result;

JSON_VALUE_ARRAY

JSON_VALUE_ARRAY(json_string_expr[, json_path])

JSON_VALUE_ARRAY(json_expr[, json_path])

説明

スカラー値の配列を抽出し、文字列形式のスカラー値を返します。スカラー値は、文字列、数値、またはブール値を表すことができます。 JSON キーが無効な JSONPath 文字を使用した場合は、二重引用符を使用して、それらの文字をエスケープできます。

  • json_string_expr: JSON 形式の文字列。次に例を示します。

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

  • json_expr: JSON。次に例を示します。

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

  • json_path: JSONPath。これにより、入力から取得するデータが識別されます。このオプション パラメータが指定されていない場合は、JSONPath $ 記号が適用されます。つまり、すべてのデータが分析されます。

注意点:

  • 入力配列に JSON null が含まれる場合は、JSON null の出力として SQL NULL が生成されます。出力に NULL 配列要素が含まれている場合、最終出力の配列に NULL 値を含めることはできないため、エラーが発生します。
  • JSONPath は、スカラー オブジェクトと JSON null を含む配列と一致する場合、最終的な出力を NULL 値を含む配列にすることはできないため、関数の出力は変換する必要があります。

戻り値の型

ARRAY<STRING>

例

JSON 内の項目が文字列配列に抽出されます。

SELECT JSON_VALUE_ARRAY(
  JSON '{"fruits":["apples","oranges","grapes"]}','$.fruits'
  ) AS string_array;

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

次の例では、JSON_QUERY_ARRAY 関数と JSON_VALUE_ARRAY 関数で結果を返す方法を比較しています。

SELECT JSON_QUERY_ARRAY('["apples","oranges"]') AS json_array,
       JSON_VALUE_ARRAY('["apples","oranges"]') AS string_array;

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

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

-- Strips the double quotes
SELECT JSON_VALUE_ARRAY('["foo","bar","baz"]','$') AS string_array;

+-----------------+
| string_array    |
+-----------------+
| [foo, bar, baz] |
+-----------------+

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

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

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

次のものは同等です。

SELECT JSON_VALUE_ARRAY('{"fruits":["apples","oranges","grapes"]}','$.fruits') AS string_array;
SELECT JSON_VALUE_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_VALUE_ARRAY('{"a.b": {"c": ["world"]}}', '$."a.b".c') AS hello;

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

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

-- An error is thrown if you provide an invalid JSONPath.
SELECT JSON_VALUE_ARRAY('["foo","bar","baz"]','INVALID_JSONPath') AS result;

-- If the JSON-formatted string is invalid, then NULL is returned.
SELECT JSON_VALUE_ARRAY('}}','$') AS result;

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

-- If the JSON document is NULL, then NULL is returned.
SELECT JSON_VALUE_ARRAY(NULL,'$') AS result;

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

-- If a JSONPath does not match anything, then the output is NULL.
SELECT JSON_VALUE_ARRAY('{"a":["foo","bar","baz"]}','$.b') AS result;

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

-- If a JSONPath matches an object that is not an array, then the output is NULL.
SELECT JSON_VALUE_ARRAY('{"a":"foo"}','$') AS result;

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

-- If a JSONPath matches an array of non-scalar objects, then the output is NULL.
SELECT JSON_VALUE_ARRAY('{"a":[{"b":"foo","c":1},{"b":"bar","c":2}],"d":"baz"}','$.a') AS result;

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

-- If a JSONPath matches an array of mixed scalar and non-scalar objects,
-- then the output is NULL.
SELECT JSON_VALUE_ARRAY('{"a":[10, {"b": 20}]','$.a') AS result;

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

-- If a JSONPath matches an empty JSON array, then the output is an empty array instead of NULL.
SELECT JSON_VALUE_ARRAY('{"a":"foo","b":[]}','$.b') AS result;

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

-- The following query produces and error because the final output cannot be an
-- array with NULLs.
SELECT JSON_VALUE_ARRAY('["world", 1, null]') AS result;

PARSE_JSON

PARSE_JSON(json_string_expr[, wide_number_mode=>{ 'exact' | 'round' }])

Description

SQL STRING 値を取り、SQL JSON 値を返します。 STRING 値は文字列形式の JSON 値を表します。

この関数では、wide_number_mode という省略可能な必須名付き引数を使用できます。この引数を使用すると、精度を低下させずに JSON 値に保存できない数値の処理方法が決まります。使用する場合は、wide_number_mode に次のいずれかの値を含める必要があります。

  • exact: 精度を失うことなく格納できる数値のみを受け入れます。精度を失うことなく格納できない数値が存在する場合、関数はエラーをスローします。
  • round: 精度を失うことなく格納できる数値が存在する場合は、精度を失うことなく格納できる数値に丸めます。数値を丸められない場合は、エラーをスローします。

wide_number_mode を使用しなかった場合、この関数には暗黙的に wide_number_mode=>'exact' が含まれます。番号が JSON オブジェクトまたは配列にある場合、オブジェクトまたは配列内の番号には wide_number_mode 引数が適用されます。

次のドメインの数値は、精度を失うことなく JSON に保存できます。

  • 64 ビット符号付き整数と符号なし整数（INT64 など）
  • FLOAT64

戻り値の型

JSON

例

次の例では、JSON 形式の文字列が JSON に変換されます。

SELECT PARSE_JSON('{"coordinates":[10,20],"id":1}') AS json_data;

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

次のクエリは以下の理由で失敗します

  • 精度を失うことなく、渡された数値を保存することはできません。
  • 最初のクエリで wide_number_mode=>'exact' が暗黙的に使用され、2 番目のクエリで明示的に使用されます。
SELECT PARSE_JSON('{"id":922337203685477580701}') AS json_data; -- fails
SELECT PARSE_JSON('{"id":922337203685477580701}', wide_number_mode=>'exact') AS json_data; -- fails

次のクエリは、数値を丸めて JSON に格納できる数値にします。

SELECT PARSE_JSON('{"id":922337203685477580701}', wide_number_mode=>'round') AS json_data;

+--------------------------------+
| json_data                      |
+--------------------------------+
| {"id":9.223372036854776e+20}   |
+--------------------------------+

TO_JSON

TO_JSON(sql_value[, stringify_wide_numbers=>{ TRUE | FALSE }])

Description

SQL 値を受け取って、JSON 値を返します。値は、サポートされている BigQuery のデータ型にする必要があります。この関数がサポートする BigQuery データ型と JSON エンコードについては、こちらをご覧ください。

この関数は、stringify_wide_numbers というオプションの必須名前付き引数をサポートします。

  • この引数が TRUE の場合、FLOAT64 型ドメイン以外の数値は文字列としてエンコードされます。
  • この引数を使用しない場合、または FALSE である場合、FLOAT64 型ドメイン外の数値は文字列としてエンコードされませんが、JSON 番号として保存されます。数値を精度を失わずに JSON に保存できない場合は、エラーがスローされます。

次の数値データ型は stringify_wide_numbers 引数の影響を受けます。

  • INT64
  • NUMERIC
  • BIGNUMERIC

これらの数値データ型のいずれかが ARRAY や STRUCT などのコンテナデータ型である場合、stringify_wide_numbers 引数はコンテナデータ型の数値データ型に適用されます。

戻り値の型

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 TO_JSON(t) AS json_objects
FROM CoordinatesTable AS t;

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

次の例のクエリは、大きな数値を JSON 文字列として返します。

SELECT TO_JSON(9007199254740993, stringify_wide_numbers=>TRUE) as stringify_on

+--------------------+
| stringify_on       |
+--------------------+
| "9007199254740993" |
+--------------------+

次の例では、両方のクエリが大きな数値を JSON 数値として返します。

SELECT TO_JSON(9007199254740993, stringify_wide_numbers=>FALSE) as stringify_off
SELECT TO_JSON(9007199254740993) as stringify_off

+------------------+
| stringify_off    |
+------------------+
| 9007199254740993 |
+------------------+

次の例では、大きい数値のみが JSON 文字列に変換されます。

With T1 AS (
  (SELECT 9007199254740993 AS id) UNION ALL
  (SELECT 2 AS id))
SELECT TO_JSON(t, stringify_wide_numbers=>TRUE) AS json_objects
FROM T1 AS t;

+---------------------------+
| json_objects              |
+---------------------------+
| {"id":"9007199254740993"} |
| {"id":2}                  |
+---------------------------+

この例では、値 9007199254740993（INT64）と 2.1（FLOAT64）が共通のスーパータイプ FLOAT64 に変換されます。これは stringify_wide_numbers 引数の影響を受けません。

With T1 AS (
  (SELECT 9007199254740993 AS id) UNION ALL
  (SELECT 2.1 AS id))
SELECT TO_JSON(t, stringify_wide_numbers=>TRUE) AS json_objects
FROM T1 AS t;

+------------------------------+
| json_objects                 |
+------------------------------+
| {"id":9.007199254740992e+15} |
| {"id":2.1}                   |
+------------------------------+

TO_JSON_STRING

TO_JSON_STRING(value[, pretty_print])

説明

SQL 値を取り、値の JSON 形式の文字列表現を返します。値は、サポートされている BigQuery のデータ型にする必要があります。この関数がサポートする BigQuery データ型と JSON エンコードについては、こちらをご覧ください。

この関数では、pretty_print という省略可能なブール値パラメータを使用できます。pretty_print が true の場合、戻り値は読みやすいように書式設定されています。

戻り値の型

JSON 形式の STRING

例

テーブル内の行を 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             |
|    |             |   ]                |
|    |             | }                  |
+----+-------------+--------------------+

STRING

STRING(json_expr)

Description

JSON 式を取得して JSON 文字列を抽出し、その値を SQL STRING として返します。式が SQL NULL の場合、この関数は SQL NULL を返します。抽出された JSON 値が文字列でない場合は、エラーが発生します。

  • json_expr: JSON。次に例を示します。

    JSON '{"name": "sky", "color" : "blue"}'

戻り値の型

STRING

例

SELECT STRING(JSON '"purple"') AS color;

+--------+
| color  |
+--------+
| purple |
+--------+

SELECT STRING(JSON_QUERY(JSON '{"name": "sky", "color": "blue"}', "$.color")) AS color;

+-------+
| color |
+-------+
| blue  |
+-------+

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

-- An error is thrown if the JSON is not of type string.
SELECT STRING(JSON '123') AS result; -- Throws an error
SELECT STRING(JSON 'null') AS result; -- Throws an error
SELECT SAFE.STRING(JSON '123') AS result; -- Returns a SQL NULL

BOOL

BOOL(json_expr)

Description

JSON 式を取得して JSON ブール値を抽出し、その値を SQL BOOL として返します。式が SQL NULL の場合、この関数は SQL NULL を返します。抽出された JSON 値がブール値でない場合は、エラーが発生します。

  • json_expr: JSON。次に例を示します。

    JSON '{"name": "sky", "color" : "blue"}'

戻り値の型

BOOL

例

SELECT BOOL(JSON 'true') AS vacancy;

+---------+
| vacancy |
+---------+
| true    |
+---------+

SELECT BOOL(JSON_QUERY(JSON '{"hotel class": "5-star", "vacancy": true}', "$.vacancy")) AS vacancy;

+---------+
| vacancy |
+---------+
| true    |
+---------+

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

-- An error is thrown if JSON is not of type bool.
SELECT BOOL(JSON '123') AS result; -- Throws an error
SELECT BOOL(JSON 'null') AS result; -- Throw an error
SELECT SAFE.BOOL(JSON '123') AS result; -- Returns a SQL NULL

INT64

INT64(json_expr)

Description

JSON 式を取得して JSON 番号を抽出し、その値を SQL INT64 として返します。式が SQL NULL の場合、この関数は SQL NULL を返します。抽出された JSON 数値に小数部分があるか、INT64 ドメイン外にある場合は、エラーが発生します。

  • json_expr: JSON。次に例を示します。

    JSON '{"name": "sky", "color" : "blue"}'

戻り値の型

INT64

例

SELECT INT64(JSON '2005') AS flight_number;

+---------------+
| flight_number |
+---------------+
| 2005          |
+---------------+

SELECT INT64(JSON_QUERY(JSON '{"gate": "A4", "flight_number": 2005}', "$.flight_number")) AS flight_number;

+---------------+
| flight_number |
+---------------+
| 2005          |
+---------------+

SELECT INT64(JSON '10.0') AS score;

+-------+
| score |
+-------+
| 10    |
+-------+

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

-- An error is thrown if JSON is not a number or cannot be converted to a 64-bit integer.
SELECT INT64(JSON '10.1') AS result;  -- Throws an error
SELECT INT64(JSON '"strawberry"') AS result; -- Throws an error
SELECT INT64(JSON 'null') AS result; -- Throws an error
SELECT SAFE.INT64(JSON '"strawberry"') AS result;  -- Returns a SQL NULL

FLOAT64

FLOAT64(json_expr[, wide_number_mode=>{ 'exact' | 'round' }])

Description

JSON 式を取得して JSON 番号を抽出し、その値を SQL FLOAT64 として返します。式が SQL NULL の場合、この関数は SQL NULL を返します。抽出された JSON 値が番号でない場合は、エラーが発生します。

  • json_expr: JSON。次に例を示します。

    JSON '{"name": "sky", "color" : "blue"}'

この関数は、wide_number_mode というオプションの必須名前付き引数をサポートします。この引数は、精度を失わずに FLOAT64 として表現できない数値について処理を定義します。

この引数には、次の 2 つの値のいずれかを指定できます。この値は、大文字と小文字を区別します。

  • 「exact」: 精度を失わずに結果を FLOAT64 として表現できない場合、関数は失敗します。
  • 「round」: JSON に保存されている数値は、FLOAT64 に丸められます。このような丸めができない場合、関数は失敗します。これは、引数が指定されていない場合のデフォルト値です。

戻り値の型

FLOAT64

例

SELECT FLOAT64(JSON '9.8') AS velocity;

+----------+
| velocity |
+----------+
| 9.8      |
+----------+

SELECT FLOAT64(JSON_QUERY(JSON '{"vo2_max": 39.1, "age": 18}', "$.vo2_max")) AS vo2_max;

+---------+
| vo2_max |
+---------+
| 39.1    |
+---------+

SELECT FLOAT64(JSON '18446744073709551615', wide_number_mode=>'round') as result;

+------------------------+
| result                 |
+------------------------+
| 1.8446744073709552e+19 |
+------------------------+

SELECT FLOAT64(JSON '18446744073709551615') as result;

+------------------------+
| result                 |
+------------------------+
| 1.8446744073709552e+19 |
+------------------------+

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

-- An error is thrown if JSON is not of type FLOAT64.
SELECT FLOAT64(JSON '"strawberry"') AS result;
SELECT FLOAT64(JSON 'null') AS result;

-- An error is thrown because `wide_number_mode` is case-sensitive and not "exact" or "round".
SELECT FLOAT64(JSON '123.4', wide_number_mode=>'EXACT') as result;
SELECT FLOAT64(JSON '123.4', wide_number_mode=>'exac') as result;

-- An error is thrown because the number cannot be converted to DOUBLE without loss of precision
SELECT FLOAT64(JSON '18446744073709551615', wide_number_mode=>'exact') as result;

-- Returns a SQL NULL
SELECT SAFE.FLOAT64(JSON '"strawberry"') AS result;

JSON_TYPE

JSON_TYPE(json_expr)

Description

JSON 式を取得して、最も外側の JSON 値の型を SQL STRING として返します。これらの JSON 型の名前を返すことができます。

  • object
  • array
  • string
  • number
  • boolean
  • null

式が SQL NULL の場合、この関数は SQL NULL を返します。抽出された JSON 値が有効な JSON 型でない場合、エラーが発生します。

  • json_expr: JSON。次に例を示します。

    JSON '{"name": "sky", "color" : "blue"}'

戻り値の型

STRING

例

SELECT json_val, JSON_TYPE(json_val) AS type
FROM
  UNNEST(
    [
      JSON '"apple"',
      JSON '10',
      JSON '3.14',
      JSON 'null',
      JSON '{"city": "New York", "State": "NY"}',
      JSON '["apple", "banana"]',
      JSON 'false'
    ]
  ) AS json_val;

+----------------------------------+---------+
| json_val                         | type    |
+----------------------------------+---------+
| "apple"                          | string  |
| 10                               | number  |
| 3.14                             | number  |
| null                             | null    |
| {"State":"NY","city":"New York"} | object  |
| ["apple","banana"]               | array   |
| false                            | boolean |
+----------------------------------+---------+

JSON エンコード

次の表に、SQL 値を TO_JSON_STRING または TO_JSON 関数で JSON 値としてエンコードする際の一般的なエンコードを示します。

SQL JSON 例
NULL

null

 SQL 入力: NULL
JSON 出力: null
BOOL ブール値 SQL 入力: TRUE
JSON 出力: true
SQL 入力: FALSE
JSON 出力: false
INT64

（TO_JSON_STRING のみ）

数値または文字列

値が [-253, 253] の範囲内にある場合は数値としてエンコードされます。この範囲は IEEE 754 倍精度浮動小数点数としてロスレスに表現できる整数の範囲です。この範囲外の値は、文字列としてエンコードされます。

 SQL 入力: 9007199254740992
JSON 出力: 9007199254740992
SQL 入力: 9007199254740993
JSON 出力: "9007199254740993"
INT64

（TO_JSON のみ）

数値または文字列

stringify_wide_numbers 引数が TRUE で、値が FLOAT64 型ドメイン以外の場合、値は文字列としてエンコードされます。精度を失わずに値を JSON に保存できない場合、関数は失敗します。それ以外の場合、値は数値としてエンコードされます。

stringify_wide_numbers が使用されないか、FALSE の場合、「FLOAT64」型ドメイン外の数値は文字列としてエンコードされませんが、JSON 番号として保存されます。数値を精度を失わずに JSON に格納できない場合は、エラーがスローされます。

 SQL 入力: 9007199254740992
JSON 出力: 9007199254740992
SQL 入力: 9007199254740993
JSON 出力: 9007199254740993
stringify_wide_numbers=>TRUE の SQL 入力: 9007199254740992
JSON 出力: 9007199254740992
stringify_wide_numbers=>TRUE の SQL 入力: 9007199254740993
JSON 出力: "9007199254740993"
NUMERIC
BIGNUMERIC

（TO_JSON_STRING のみ）

数値または文字列

値が [-253, 253] の範囲内にあり、小数部が存在しない場合は、数値としてエンコードされます。この範囲外の値は、文字列としてエンコードされます。

 SQL 入力: -1
JSON 出力: -1
SQL 入力: 0
JSON 出力: 0
SQL 入力: 9007199254740993
JSON 出力: "9007199254740993"
SQL 入力: 123.56
JSON 出力: "123.56"
NUMERIC
BIGNUMERIC

（TO_JSON のみ）

数値または文字列

stringify_wide_numbers 引数が TRUE で、値が FLOAT64 型ドメイン以外の場合、文字列としてエンコードされます。それ以外の場合は、数値としてエンコードされます。

 SQL 入力: -1
JSON 出力: -1
SQL 入力: 0
JSON 出力: 0
SQL 入力: 9007199254740993
JSON 出力: 9007199254740993
SQL 入力: 123.56
JSON 出力: 123.56
stringify_wide_numbers=>TRUE: SQL の入力: 9007199254740993
JSON 出力: "9007199254740993"
stringify_wide_numbers=>TRUE: の入力: 123.56
JSON 出力: 123.56
FLOAT64

数値または文字列

+/-inf と NaN は、Infinity、-Infinity、NaN としてエンコードされます。それ以外の場合、この値は数値としてエンコードされます。

 SQL 入力: 1.0
JSON 出力: 1
SQL 入力: 9007199254740993
JSON 出力: 9007199254740993
SQL 入力: "+inf"
JSON 出力: "Infinity"
SQL 入力: "-inf"
JSON 出力: "-Infinity"
SQL 入力: "NaN"
JSON 出力: "NaN"
STRING

文字列

JSON 標準に従ってエスケープされた文字列としてエンコードされます。具体的には "、\、制御文字（U+0000 から U+001F）がエスケープされます。

 SQL 入力: "abc"
JSON 出力: "abc"
SQL 入力: "\"abc\""
JSON 出力: "\"abc\""
BYTES

文字列

RFC 4648 の Base64 データ エンコードを使用します。

 SQL 入力: b"Google"
JSON 出力: "R29vZ2xl"
DATE 文字列 SQL 入力: DATE '2017-03-06'
JSON 出力: "2017-03-06"
TIMESTAMP

文字列

ISO 8601 の日時としてエンコードされます。T は日付と時刻を区切り、Z（Zulu/UTC）はタイムゾーンを表します。

 SQL 入力: TIMESTAMP '2017-03-06 12:34:56.789012'
JSON 出力: "2017-03-06T12:34:56.789012Z"
DATETIME

文字列

ISO 8601 の日時としてエンコードされます（T は日付と時刻を区切ります）。

 SQL 入力: DATETIME '2017-03-06 12:34:56.789012'
JSON 出力: "2017-03-06T12:34:56.789012"
TIME

文字列

ISO 8601 の時刻でエンコードされます。

 SQL 入力: TIME '12:34:56.789012'
JSON 出力: "12:34:56.789012"
JSON

入力 JSON のデータ

 SQL 入力: JSON '{"item": "pen", "price": 10}'
JSON 出力: {"item":"pen", "price":10}
SQL 入力:[1, 2, 3]
JSON 出力:[1, 2, 3]
ARRAY

配列

0 個以上の要素を含めることができます。

 SQL 入力: ["red", "blue", "green"]
JSON 出力: ["red","blue","green"]
SQL 入力:[1, 2, 3]
JSON 出力:[1,2,3]
STRUCT

オブジェクト

このオブジェクトには、0 個以上の Key-Value ペアを含めることができます。各値は型に従って書式設定されます。

TO_JSON では、フィールドは出力文字列に含まれ、このフィールドの重複は省略されます。 TO_JSON_STRING の場合、フィールドとこのフィールドの重複は、出力文字列に含まれます。

匿名のフィールドは "" と表されます。

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

 SQL 入力: STRUCT(12 AS purchases, TRUE AS inStock)
JSON 出力: {"inStock": true,"purchases":12}

JSONPath 形式

JSONPath 形式を使用すると、JSON 形式の文字列から取得する値を識別できます。JSONPath 形式は、次の演算子をサポートしています。

演算子 Description 例
$ ルートのオブジェクトまたは要素JSONPath 形式は、JSON 形式の文字列の最も外側のレベルを参照するこの演算子で始まる必要があります。

JSON 形式の文字列:
'{"class" : {"students" : [{"name" : "Jane"}]}}'

JSON のパス:
"$"

JSON の結果:
{"class":{"students":[{"name":"Jane"}]}}
. 子の演算子子の値は、ドット表記を使用して識別できます。

JSON 形式の文字列:
'{"class" : {"students" : [{"name" : "Jane"}]}}'

JSON のパス:
"$.class.students"

JSON の結果:
[{"name":"Jane"}]
[] 添字演算子。JSON オブジェクトが配列の場合は、ブラケットを使用して配列のインデックスを指定できます。

JSON 形式の文字列:
'{"class" : {"students" : [{"name" : "Jane"}]}}'

JSON のパス:
"$.class.students[0]"

JSON の結果:
{"name":"Jane"}

JSON 関数のキーに JSON 形式の演算子が含まれている場合は、その JSON 関数のエスケープ方法を確認してください。

JSON 関数は、JSONPath 形式が JSON 形式の文字列内の値と一致しない場合に、NULL を返します。この関数は、スカラー関数に選択した値がオブジェクトや配列などのスカラー値ではない場合、NULL を返します。JSONPath 形式が無効な場合は、エラーが発生します。