標準 SQL の JSON 関数

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

JSON_EXTRACT または JSON_EXTRACT_SCALAR

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

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

説明

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

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

json_path_string_literal パラメータは、JSON 形式の文字列から取得する値を識別します。このパラメータは JSONPath 形式を使用して作成します。このパラメータの書式は $ 記号で開始する必要があります。これは JSON 形式の文字列の最も外側のレベルを指すシンボルです。ドットまたはブラケット表記を使用して子の値を識別できます。JSON オブジェクトが配列の場合は、ブラケットを使用して配列のインデックスを指定できます。

JSONPath	説明
$	ルートのオブジェクトまたは要素
. または []	子の演算子
[]	添字演算子

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

JSONPath が無効な場合はエラーとなります。

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

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

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

例

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": "Jamie"}]}}'
  ]) AS json_text;

上のクエリは、次の結果を生成します。

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

TO_JSON_STRING

TO_JSON_STRING(value[, pretty_print])

説明

value の JSON 形式の文字列表現を返します。この関数はオプションの pretty_print パラメータをサポートします。pretty_print がある場合、戻り値は読みやすいように書式設定されています。

入力データ型	戻り値
任意の型の NULL	`null`
BOOL	`true` または `false`。
INT64	`value` が [-2⁵³, 2⁵³] の範囲内にある場合は、`CAST(value AS STRING)` と同じです。この範囲は IEEE 754 倍精度浮動小数点数としてロスレスに表現できる整数の範囲です。この範囲外となる値は、引用符付きの文字列で表されます。例: `-1` `0` `12345678901` `9007199254740992` `-9007199254740992` `"9007199254740993"` `9007199254740993` は 2⁵³ より大きいため、引用符付きの文字列として表されます。
NUMERIC	`value` が [-2⁵³, 2⁵³] の範囲内にあり、小数部が存在しない場合は、`CAST(value AS STRING)` と同じです。この範囲外となる値は、引用符付きの文字列で表されます。例: `-1` `0` `"9007199254740993"` `"123.56"`
FLOAT64	`+/-inf` および `NaN` は、それぞれ `Infinity`、`-Infinity`、および `NaN` として表されます。それ以外の場合は、`CAST(value AS STRING)` と同じです。
STRING	引用符付きの文字列値。JSON 標準に従ってエスケープされます。具体的には `"`、`\` で、`U+0000` から `U+001F` までの制御文字はエスケープされます。
BYTES	引用符付きの RFC 4648 の base64 エスケープ値。例: `"R29vZ2xl"` はバイト `b"Google"` の base64 表現です。
DATE	引用符付きの日付。例: `"2017-03-06"`
TIMESTAMP	引用符付きの ISO 8601 の日時。T は日付と時刻を区切り、Zulu/UTC はタイムゾーンを表します。例: `"2017-03-06T12:34:56.789012Z"`
DATETIME	引用符付きの ISO 8601 の日時。T は日付と時刻を区切ります。例: `"2017-03-06T12:34:56.789012"`
TIME	引用符付きの ISO 8601 の時刻。例: `"12:34:56.789012"`
ARRAY	`[elem1,elem2,...]`。各 `elem` は要素タイプに従って書式設定されます。書式設定の例: [ elem1, elem2, ... ] 各 elem は、要素の種類に応じて書式設定されます。空の配列は `[]` と表されます。
STRUCT	`{"field_name1":field_value1,"field_name2":field_value2,...}` 各 `field_value` は型に従って書式設定されます。書式設定の例: { "field_name1": field_value1, "field_name2": field_value2, ... } 各 `field_value` は型に従って書式設定されます。`field_value` が空でない ARRAY または STRUCT の場合、要素は適切なレベルにインデントされます。空の STRUCT は `{}` と表されます。名前が重複しているフィールドは、JSON で解析できなくなる可能性があります。匿名のフィールドは `""` と表されます。無効な UTF-8 フィールド名は、JSON で解析できなくなる可能性があります。文字列値は、JSON 標準に従ってエスケープされます。具体的には `"`、`\` で、`U+0000` から `U+001F` までの制御文字はエスケープされます。

戻り値の型

値の JSON 文字列表現。

例

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

WITH Input AS (
  SELECT [1, 2] AS x, 'foo' AS y, STRUCT(true AS a, DATE '2017-04-05' AS b) AS s UNION ALL
  SELECT NULL AS x, '' AS y, STRUCT(false AS a, DATE '0001-01-01' AS b) AS s UNION ALL
  SELECT [3] AS x, 'bar' AS y, STRUCT(NULL AS a, DATE '2016-12-05' AS b) AS s
)
SELECT
  t,
  TO_JSON_STRING(t) AS json_row
FROM Input AS t;

上のクエリは、次の結果を生成します。

+-----------------------------------+-------------------------------------------------------+
| t                                 | json_row                                              |
+-----------------------------------+-------------------------------------------------------+
| {[1, 2], foo, {true, 2017-04-05}} | {"x":[1,2],"y":"foo","s":{"a":true,"b":"2017-04-05"}} |
| {NULL, , {false, 0001-01-01}}     | {"x":null,"y":"","s":{"a":false,"b":"0001-01-01"}}    |
| {[3], bar, {NULL, 2016-12-05}}    | {"x":[3],"y":"bar","s":{"a":null,"b":"2016-12-05"}}   |
+-----------------------------------+-------------------------------------------------------+

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

WITH Input AS (
  SELECT [1, 2] AS x, 'foo' AS y, STRUCT(true AS a, DATE '2017-04-05' AS b) AS s UNION ALL
  SELECT NULL AS x, '' AS y, STRUCT(false AS a, DATE '0001-01-01' AS b) AS s UNION ALL
  SELECT [3] AS x, 'bar' AS y, STRUCT(NULL AS a, DATE '2016-12-05' AS b) AS s
)
SELECT
  TO_JSON_STRING(t, true) AS json_row
FROM Input AS t;

上のクエリは、次の結果を生成します。

+-----------------------+
| json_row              |
+-----------------------+
| {                     |
|  "x": [               |
|    1,                 |
|    2                  |
|  ],                   |
|  "y": "foo",          |
|  "s": {               |
|    "a": true,         |
|    "b": "2017-04-05"  |
|  }                    |
|}                      |
| {                     |
|  "x": null,           |
|  "y": "",             |
|  "s": {               |
|    "a": false,        |
|    "b": "0001-01-01"  |
|  }                    |
|}                      |
| {                     |
|  "x": [               |
|    3                  |
|  ],                   |
|  "y": "bar",          |
|  "s": {               |
|    "a": null,         |
|    "b": "2016-12-05"  |
|  }                    |
|}                      |
+-----------------------+