標準 SQL 中的 JSON 函式

BigQuery 支援可協助您擷取以 JSON 格式字串所儲存資料的函式,也支援可協助您將資料轉換成 JSON 格式字串的函式。

JSON_EXTRACT 或 JSON_EXTRACT_SCALAR

JSON_EXTRACT(json_string_expr, json_path_string_literal),以 STRING 傳回 JSON 值。

JSON_EXTRACT_SCALAR(json_string_expr, json_path_string_literal),以 STRING 傳回純量 JSON 值。

說明

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 truefalse
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+0000U+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,元素會縮排至適當的層級。空白結構會表示為 {}

具有重複名稱的欄位可能會導致 JSON 無法剖析。匿名欄位會以 "" 表示。

無效的 UTF-8 欄位名稱可能會導致 JSON 無法剖析。字串值會根據 JSON 標準逸出。具體而言,會逸出 "\ 與從 U+0000U+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"  |
|  }                    |
|}                      |
+-----------------------+
本頁內容對您是否有任何幫助?請提供意見:

傳送您對下列選項的寶貴意見...

這個網頁