BigQuery は、JSON データを取得して変換できる次の関数をサポートしています。
関数の概要
標準の 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
が検出されると、SQLNULL
を抽出します。 次に例を示します。SELECT JSON_EXTRACT("null", "$") -- Returns a SQL NULL
json_expr
: JSON。次に例を示します。JSON '{"class" : {"students" : [{"name" : "Jane"}]}}'
JSON
null
が検出されると、JSONnull
を抽出します。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
が検出されると、SQLNULL
を抽出します。 次に例を示します。SELECT JSON_QUERY("null", "$") -- Returns a SQL NULL
json_expr
: JSON。次に例を示します。JSON '{"class" : {"students" : [{"name" : "Jane"}]}}'
JSON
null
が検出されると、JSONnull
を抽出します。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
が JSONnull
またはスカラーではない値を返す場合(つまり、json_path
がオブジェクトまたは配列を参照している場合)、SQLNULL
が返されます。
戻り値の型
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
が JSONnull
またはスカラーではない値を返す場合(つまり、json_path
がオブジェクトまたは配列を参照している場合)、SQLNULL
が返されます。
戻り値の型
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
がある場合は、その JSONnull
の出力として SQLNULL
が生成されます。出力に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
が含まれる場合は、JSONnull
の出力として SQLNULL
が生成されます。出力に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 のみ) 数値または文字列
|
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 のみ) 数値または文字列
|
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 |
数値または文字列
|
SQL 入力: 1.0 JSON 出力: 1 SQL 入力: 9007199254740993 JSON 出力: 9007199254740993 SQL 入力: "+inf" JSON 出力: "Infinity" SQL 入力: "-inf" JSON 出力: "-Infinity" SQL 入力: "NaN" JSON 出力: "NaN" |
STRING |
文字列
JSON 標準に従ってエスケープされた文字列としてエンコードされます。具体的には |
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 ペアを含めることができます。各値は型に従って書式設定されます。
匿名のフィールドは
無効な UTF-8 フィールド名がある場合、JSON が解析不能になる可能性があります。文字列値は、JSON 標準に従ってエスケープされます。具体的には |
SQL 入力: STRUCT(12 AS purchases, TRUE AS inStock) JSON 出力: {"inStock": true,"purchases":12} |
JSONPath 形式
JSONPath 形式を使用すると、JSON 形式の文字列から取得する値を識別できます。JSONPath 形式は、次の演算子をサポートしています。
演算子 | Description | 例 |
---|---|---|
$ |
ルートのオブジェクトまたは要素JSONPath 形式は、JSON 形式の文字列の最も外側のレベルを参照するこの演算子で始まる必要があります。 |
JSON 形式の文字列:
JSON のパス:
JSON の結果: |
. |
子の演算子子の値は、ドット表記を使用して識別できます。 |
JSON 形式の文字列:
JSON のパス:
JSON の結果: |
[] |
添字演算子。JSON オブジェクトが配列の場合は、ブラケットを使用して配列のインデックスを指定できます。 |
JSON 形式の文字列:
JSON のパス:
JSON の結果: |
JSON 関数のキーに JSON 形式の演算子が含まれている場合は、その JSON 関数のエスケープ方法を確認してください。
JSON 関数は、JSONPath 形式が JSON 形式の文字列内の値と一致しない場合に、NULL
を返します。この関数は、スカラー関数に選択した値がオブジェクトや配列などのスカラー値ではない場合、NULL
を返します。JSONPath 形式が無効な場合は、エラーが発生します。