표준 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가 [-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

[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+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"  |
|  }                    |
|}                      |
+-----------------------+
이 페이지가 도움이 되었나요? 평가를 부탁드립니다.

다음에 대한 의견 보내기...

도움이 필요하시나요? 지원 페이지를 방문하세요.