標準 SQL の JSON 関数

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

JSON_EXTRACT または JSON_EXTRACT_SCALAR

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

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

説明

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

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

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

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

戻り値の型

STRING

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

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

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

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

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

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

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

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

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

JSON_EXTRACT_ARRAY

JSON_EXTRACT_ARRAY(json_string_expr[, json_path_format])

説明

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

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

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

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

戻り値の型

ARRAY<STRING>

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

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

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

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

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

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

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

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

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

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

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

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

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

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

次のものは同等です。

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

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

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

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

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

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

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

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

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

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

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

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

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

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

JSON_QUERY または JSON_VALUE

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

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

説明

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

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

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

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

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

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

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

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

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

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

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

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

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

TO_JSON_STRING

TO_JSON_STRING(value[, pretty_print])

説明

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

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

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

-1
0
12345678901
9007199254740992
-9007199254740992
"9007199254740993"

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

NUMERIC

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

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

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

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

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

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

DATE

引用符付きの日付。例:

"2017-03-06"
TIMESTAMP

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

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

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

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

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

"12:34:56.789012"
ARRAY

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