標準 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              |
| {"first":"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"  |
|  }                    |
|}                      |
+-----------------------+

このページは役立ちましたか？評価をお願いいたします。

Except as otherwise noted, the content of this page is licensed under the Creative Commons Attribution 4.0 License, and code samples are licensed under the Apache 2.0 License. For details, see our Site Policies. Java is a registered trademark of Oracle and/or its affiliates.

最終更新日: 7月 9, 2019

標準 SQL の JSON 関数

JSON_EXTRACT または JSON_EXTRACT_SCALAR

TO_JSON_STRING

フィードバックを送信...