JSON 함수

컬렉션을 사용해 정리하기 내 환경설정을 기준으로 콘텐츠를 저장하고 분류하세요.
{# END}

BigQuery는 JSON 데이터를 검색하고 변환할 수 있는 다음 함수를 지원합니다.

함수 개요

다음 함수는 큰따옴표를 사용하여 잘못된 JSONPath 문자를 이스케이프 처리합니다. "a.b"

이 동작은 ANSI 표준과 일치합니다.

JSON 함수 설명 반환 유형
JSON_QUERY 배열이나 객체와 같은 JSON 값 또는 문자열, 숫자, 부울과 같은 JSON 스칼라 값을 추출합니다. JSON 형식의 STRING 또는 JSON
JSON_VALUE 스칼라 값을 추출합니다. 스칼라 값은 문자열, 숫자, 부울을 나타낼 수 있습니다. 가장 바깥쪽 따옴표를 삭제하고 값을 이스케이프 취소합니다. 비스칼라 값이 선택되면 SQL NULL을 반환합니다. STRING
JSON_QUERY_ARRAY 배열이나 객체와 같은 JSON 값의 배열과 문자열, 숫자, 부울과 같은 JSON 스칼라 값을 추출합니다. ARRAY<JSON-formatted STRING> 또는 ARRAY<JSON>
JSON_VALUE_ARRAY 스칼라 값의 배열을 추출합니다. 스칼라 값은 문자열, 숫자, 부울을 나타낼 수 있습니다. 가장 바깥쪽 따옴표를 삭제하고 값을 이스케이프 취소합니다. 선택한 값이 배열이 아니거나 스칼라 값만 포함하는 배열이 아닌 경우 SQL NULL을 반환합니다. ARRAY<STRING>

기존 JSON 추출 함수

다음 함수는 작은따옴표와 대괄호를 사용하여 잘못된 JSONPath 문자를 이스케이프 처리합니다. ['a.b']

이러한 함수는 BigQuery에서 지원되지만 앞의 표에 있는 함수를 사용하는 것이 좋습니다.

JSON 함수 설명 반환 유형
JSON_EXTRACT 배열이나 객체와 같은 JSON 값 또는 문자열, 숫자, 부울과 같은 JSON 스칼라 값을 추출합니다. JSON 형식의 STRING 또는 JSON
JSON_EXTRACT_SCALAR 스칼라 값을 추출합니다. 스칼라 값은 문자열, 숫자, 부울을 나타낼 수 있습니다. 가장 바깥쪽 따옴표를 삭제하고 값을 이스케이프 취소합니다. 비스칼라 값이 선택되면 SQL NULL을 반환합니다. STRING
JSON_EXTRACT_ARRAY 배열이나 객체와 같은 JSON 값의 배열과 문자열, 숫자, 부울과 같은 JSON 스칼라 값을 추출합니다. ARRAY<JSON-formatted STRING> 또는 ARRAY<JSON>
JSON_EXTRACT_STRING_ARRAY 스칼라 값의 배열을 추출합니다. 스칼라 값은 문자열, 숫자, 부울을 나타낼 수 있습니다. 가장 바깥쪽 따옴표를 삭제하고 값을 이스케이프 취소합니다. 선택한 값이 배열이 아니거나 스칼라 값만 포함하는 배열이 아닌 경우 SQL NULL을 반환합니다. ARRAY<STRING>

기타 JSON 함수

JSON 함수 설명 반환 유형
PARSE_JSON JSON 형식 문자열을 사용하여 JSON 값을 반환합니다. JSON
TO_JSON SQL 값을 사용하여 JSON 값을 반환합니다. JSON
TO_JSON_STRING SQL 값을 사용하고 해당 값을 JSON 형식의 문자열 표현으로 반환합니다. JSON 형식 STRING
STRING JSON에서 문자열을 추출합니다. STRING
BOOL JSON에서 부울을 추출합니다. BOOL
INT64 JSON에서 64비트 정수를 추출합니다. INT64
FLOAT64 JSON에서 64비트 부동 소수점 숫자를 추출합니다. FLOAT64
JSON_TYPE 가장 바깥쪽 JSON 값의 유형을 문자열로 반환합니다. STRING

JSON_EXTRACT

JSON_EXTRACT(json_string_expr, json_path)
JSON_EXTRACT(json_expr, json_path)

설명

배열이나 객체와 같은 JSON 값 또는 문자열, 숫자, 부울과 같은 JSON 스칼라 값을 추출합니다. JSON 키가 잘못된 JSONPath 문자를 사용하는 경우, 작은 따옴표와 대괄호를 사용하여 해당 문자를 이스케이프 처리할 수 있습니다.

  • json_string_expr: JSON 형식의 문자열입니다. 예를 들면 다음과 같습니다.

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

    JSON 형식 문자열 null이 발견되면 SQL NULL을 추출합니다. 예를 들면 다음과 같습니다.

    SELECT JSON_EXTRACT("null", "$") -- Returns a SQL NULL
    
  • json_expr: JSON입니다. 예를 들면 다음과 같습니다.

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

    JSON null이 발생하면 JSON null을 추출합니다.

    SELECT JSON_EXTRACT(JSON 'null', "$") -- Returns a JSON 'null'
    
  • json_path: JSONPath입니다. 이는 입력에서 가져오려는 데이터를 식별합니다.

반환 유형

  • json_string_expr: JSON 형식의 STRING
  • json_expr: JSON

예시

다음 예시에서는 JSON 데이터가 추출되어 JSON으로 반환됩니다.

SELECT
  JSON_EXTRACT(JSON '{"class":{"students":[{"id":5},{"id":12}]}}', '$.class')
  AS json_data;

+-----------------------------------+
| json_data                         |
+-----------------------------------+
| {"students":[{"id":5},{"id":12}]} |
+-----------------------------------+

다음 예시에서는 JSON 데이터가 추출되어 JSON 형식의 문자열로 반환됩니다.

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('{"a":null}', "$.a"); -- Returns a SQL NULL
SELECT JSON_EXTRACT('{"a":null}', "$.b"); -- Returns a SQL NULL
SELECT JSON_EXTRACT(JSON '{"a":null}', "$.a"); -- Returns a JSON 'null'
SELECT JSON_EXTRACT(JSON '{"a":null}', "$.b"); -- Returns a SQL NULL

JSON_QUERY

JSON_QUERY(json_string_expr, json_path)
JSON_QUERY(json_expr, json_path)

설명

배열이나 객체와 같은 JSON 값 또는 문자열, 숫자, 부울과 같은 JSON 스칼라 값을 추출합니다. JSON 키가 잘못된 JSONPath 문자를 사용하는 경우 큰따옴표를 사용하여 해당 문자를 이스케이프 처리할 수 있습니다.

  • json_string_expr: JSON 형식의 문자열입니다. 예를 들면 다음과 같습니다.

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

    JSON 형식 문자열 null이 발견되면 SQL NULL을 추출합니다. 예를 들면 다음과 같습니다.

    SELECT JSON_QUERY("null", "$") -- Returns a SQL NULL
    
  • json_expr: JSON입니다. 예를 들면 다음과 같습니다.

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

    JSON null이 발생하면 JSON null을 추출합니다.

    SELECT JSON_QUERY(JSON 'null', "$") -- Returns a JSON 'null'
    
  • json_path: JSONPath입니다. 이는 입력에서 가져오려는 데이터를 식별합니다.

반환 유형

  • json_string_expr: JSON 형식의 STRING
  • json_expr: JSON

예시

다음 예시에서는 JSON 데이터가 추출되어 JSON으로 반환됩니다.

SELECT
  JSON_QUERY(JSON '{"class":{"students":[{"id":5},{"id":12}]}}', '$.class')
  AS json_data;

+-----------------------------------+
| json_data                         |
+-----------------------------------+
| {"students":[{"id":5},{"id":12}]} |
+-----------------------------------+

다음 예시에서는 JSON 데이터가 추출되어 JSON 형식의 문자열로 반환됩니다.

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('{"a":null}', "$.a"); -- Returns a SQL NULL
SELECT JSON_QUERY('{"a":null}', "$.b"); -- Returns a SQL NULL
SELECT JSON_QUERY(JSON '{"a":null}', "$.a"); -- Returns a JSON 'null'
SELECT JSON_QUERY(JSON '{"a":null}', "$.b"); -- Returns a SQL NULL

JSON_EXTRACT_SCALAR

JSON_EXTRACT_SCALAR(json_string_expr[, json_path])
JSON_EXTRACT_SCALAR(json_expr[, json_path])

설명

스칼라 값을 추출하여 문자열로 반환합니다. 스칼라 값은 문자열, 숫자, 부울을 나타낼 수 있습니다. 가장 바깥쪽 따옴표를 삭제하고 반환 값을 이스케이프 취소합니다. JSON 키가 잘못된 JSONPath 문자를 사용하는 경우, 작은 따옴표와 대괄호를 사용하여 해당 문자를 이스케이프 처리할 수 있습니다.

  • json_string_expr: JSON 형식의 문자열입니다. 예를 들면 다음과 같습니다.

    '{"class" : {"students" : [{"name" : "Jane"}]}}'
    
  • json_expr: JSON입니다. 예를 들면 다음과 같습니다.

    JSON '{"class" : {"students" : [{"name" : "Jane"}]}}'
    
  • json_path: JSONPath입니다. 이는 입력에서 가져오려는 데이터를 식별합니다. 이 선택적 매개변수를 제공하지 않으면 JSONPath $ 기호가 적용됩니다. 즉, 모든 데이터가 분석됩니다.

    json_path가 JSON null 또는 비스칼라 값(즉, json_path가 객체 또는 배열을 참조하는 경우)을 반환하는 경우 SQL NULL이 반환됩니다.

반환 유형

STRING

예시

다음 예시에서는 age가 추출됩니다.

SELECT JSON_EXTRACT_SCALAR(JSON '{ "name" : "Jakob", "age" : "6" }', '$.age') AS scalar_age;

+------------+
| scalar_age |
+------------+
| 6          |
+------------+

다음 예시에서는 JSON_EXTRACT 함수와 JSON_EXTRACT_SCALAR 함수의 결과를 반환하는 방법을 비교합니다.

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_age;

+-----------+-------------+----------+------------+
| json_name | scalar_name | json_age | scalar_age |
+-----------+-------------+----------+------------+
| "Jakob"   | Jakob       | "6"      | 6          |
+-----------+-------------+----------+------------+
SELECT JSON_EXTRACT('{"fruits": ["apple", "banana"]}', '$.fruits') AS json_extract,
  JSON_EXTRACT_SCALAR('{"fruits": ["apple", "banana"]}', '$.fruits') AS json_extract_scalar;

+--------------------+---------------------+
| json_extract       | json_extract_scalar |
+--------------------+---------------------+
| ["apple","banana"] | NULL                |
+--------------------+---------------------+

JSON 키가 잘못된 JSONPath 문자를 사용하는 경우, 작은 따옴표와 대괄호 [' ']를 사용하여 해당 문자를 이스케이프 처리할 수 있습니다. 예를 들면 다음과 같습니다.

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

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

JSON_VALUE

JSON_VALUE(json_string_expr[, json_path])
JSON_VALUE(json_expr[, json_path])

설명

스칼라 값을 추출하여 문자열로 반환합니다. 스칼라 값은 문자열, 숫자, 부울을 나타낼 수 있습니다. 가장 바깥쪽 따옴표를 삭제하고 반환 값을 이스케이프 취소합니다. JSON 키가 잘못된 JSONPath 문자를 사용하는 경우 큰따옴표를 사용하여 해당 문자를 이스케이프 처리할 수 있습니다.

  • json_string_expr: JSON 형식의 문자열입니다. 예를 들면 다음과 같습니다.

    '{"class" : {"students" : [{"name" : "Jane"}]}}'
    
  • json_expr: JSON입니다. 예를 들면 다음과 같습니다.

    JSON '{"class" : {"students" : [{"name" : "Jane"}]}}'
    
  • json_path: JSONPath입니다. 이는 입력에서 가져오려는 데이터를 식별합니다. 이 선택적 매개변수를 제공하지 않으면 JSONPath $ 기호가 적용됩니다. 즉, 모든 데이터가 분석됩니다.

    json_path가 JSON null 또는 비스칼라 값(즉, json_path가 객체 또는 배열을 참조하는 경우)을 반환하는 경우 SQL NULL이 반환됩니다.

반환 유형

STRING

예시

다음 예시에서는 JSON 데이터가 추출되어 스칼라 값으로 반환됩니다.

SELECT JSON_VALUE(JSON '{ "name" : "Jakob", "age" : "6" }', '$.age') AS scalar_age;

+------------+
| scalar_age |
+------------+
| 6          |
+------------+

다음 예시에서는 JSON_QUERY 함수와 JSON_VALUE 함수의 결과를 반환하는 방법을 비교합니다.

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_age;

+-----------+-------------+----------+------------+
| json_name | scalar_name | json_age | scalar_age |
+-----------+-------------+----------+------------+
| "Jakob"   | Jakob       | "6"      | 6          |
+-----------+-------------+----------+------------+
SELECT JSON_QUERY('{"fruits": ["apple", "banana"]}', '$.fruits') AS json_query,
  JSON_VALUE('{"fruits": ["apple", "banana"]}', '$.fruits') AS json_value;

+--------------------+------------+
| json_query         | json_value |
+--------------------+------------+
| ["apple","banana"] | NULL       |
+--------------------+------------+

JSON 키가 잘못된 JSONPath 문자를 사용하는 경우, 큰따옴표를 사용하여 해당 문자를 이스케이프할 수 있습니다. 예를 들면 다음과 같습니다.

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

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

JSON_EXTRACT_ARRAY

JSON_EXTRACT_ARRAY(json_string_expr[, json_path])
JSON_EXTRACT_ARRAY(json_expr[, json_path])

설명

배열이나 객체와 같은 JSON 값의 배열과 문자열, 숫자, 부울과 같은 JSON 스칼라 값을 추출합니다. JSON 키가 잘못된 JSONPath 문자를 사용하는 경우, 작은 따옴표와 대괄호를 사용하여 해당 문자를 이스케이프 처리할 수 있습니다.

  • json_string_expr: JSON 형식의 문자열입니다. 예를 들면 다음과 같습니다.

    '{"class" : {"students" : [{"name" : "Jane"}]}}'
    
  • json_expr: JSON입니다. 예를 들면 다음과 같습니다.

    JSON '{"class" : {"students" : [{"name" : "Jane"}]}}'
    
  • json_path: JSONPath입니다. 이는 입력에서 가져오려는 데이터를 식별합니다. 이 선택적 매개변수를 제공하지 않으면 JSONPath $ 기호가 적용됩니다. 즉, 모든 데이터가 분석됩니다.

반환 유형

  • json_string_expr: ARRAY<JSON-formatted STRING>
  • json_expr: ARRAY<JSON>

예시

JSON의 항목을 JSON 값의 배열로 추출합니다.

SELECT JSON_EXTRACT_ARRAY(
  JSON '{"fruits":["apples","oranges","grapes"]}','$.fruits'
  ) AS json_array;

+---------------------------------+
| json_array                      |
+---------------------------------+
| ["apples", "oranges", "grapes"] |
+---------------------------------+

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 형식 문자열의 문자열 값을 배열로 추출합니다.

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

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

-- Strips 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}] |
+-------------------------------------------------------+

다음은 동일합니다.

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

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

-- The queries above produce the following 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.
SELECT JSON_EXTRACT_ARRAY('["foo","bar","baz"]','INVALID_JSONPath') AS result;

-- If the JSONPath does not refer to an array, then NULL is returned.
SELECT JSON_EXTRACT_ARRAY('{"a":"foo"}','$.a') AS result;

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

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

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

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

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

JSON_QUERY_ARRAY

JSON_QUERY_ARRAY(json_string_expr[, json_path])
JSON_QUERY_ARRAY(json_expr[, json_path])

설명

배열이나 객체와 같은 JSON 값의 배열과 문자열, 숫자, 부울과 같은 JSON 스칼라 값을 추출합니다. JSON 키가 잘못된 JSONPath 문자를 사용하는 경우 큰따옴표를 사용하여 해당 문자를 이스케이프 처리할 수 있습니다.

  • json_string_expr: JSON 형식의 문자열입니다. 예를 들면 다음과 같습니다.

    '{"class" : {"students" : [{"name" : "Jane"}]}}'
    
  • json_expr: JSON입니다. 예를 들면 다음과 같습니다.

    JSON '{"class" : {"students" : [{"name" : "Jane"}]}}'
    
  • json_path: JSONPath입니다. 이는 입력에서 가져오려는 데이터를 식별합니다. 이 선택적 매개변수를 제공하지 않으면 JSONPath $ 기호가 적용됩니다. 즉, 모든 데이터가 분석됩니다.

반환 유형

  • json_string_expr: ARRAY<JSON-formatted STRING>
  • json_expr: ARRAY<JSON>

예시

JSON의 항목을 JSON 값의 배열로 추출합니다.

SELECT JSON_QUERY_ARRAY(
  JSON '{"fruits":["apples","oranges","grapes"]}','$.fruits'
  ) AS json_array;

+---------------------------------+
| json_array                      |
+---------------------------------+
| ["apples", "oranges", "grapes"] |
+---------------------------------+

JSON 형식 문자열의 항목을 문자열 배열로 추출합니다.

SELECT JSON_QUERY_ARRAY('[1,2,3]') AS string_array;

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

문자열 배열을 추출하고 이를 정수 배열로 변환합니다.

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

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

JSON 형식 문자열의 문자열 값을 배열로 추출합니다.

-- Doesn't strip the double quotes
SELECT JSON_QUERY_ARRAY('["apples","oranges","grapes"]', '$') AS string_array;

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

-- Strips the double quotes
SELECT ARRAY(
  SELECT JSON_VALUE(string_element, '$')
  FROM UNNEST(JSON_QUERY_ARRAY('["apples","oranges","grapes"]','$')) AS string_element
) AS string_array;

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

그러면 fruit 속성의 항목만 배열로 추출합니다.

SELECT JSON_QUERY_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}] |
+-------------------------------------------------------+

다음은 동일합니다.

SELECT JSON_QUERY_ARRAY('{"fruits":["apples","oranges","grapes"]}','$.fruits') AS string_array;

SELECT JSON_QUERY_ARRAY('{"fruits":["apples","oranges","grapes"]}','$."fruits"') AS string_array;

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

JSON 키가 잘못된 JSONPath 문자를 사용하는 경우, 큰따옴표(" ")를 사용하여 해당 문자를 이스케이프할 수 있습니다. 예를 들면 다음과 같습니다.

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

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

다음 예시에서는 잘못된 요청 및 빈 배열의 처리 방법을 살펴봅니다.

-- An error is returned if you provide an invalid JSONPath.
SELECT JSON_QUERY_ARRAY('["foo","bar","baz"]','INVALID_JSONPath') AS result;

-- If the JSONPath does not refer to an array, then NULL is returned.
SELECT JSON_QUERY_ARRAY('{"a":"foo"}','$.a') AS result;

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

-- If a key that does not exist is specified, then the result is NULL.
SELECT JSON_QUERY_ARRAY('{"a":"foo"}','$.b') AS result;

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

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

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

JSON_EXTRACT_STRING_ARRAY

JSON_EXTRACT_STRING_ARRAY(json_string_expr[, json_path])
JSON_EXTRACT_STRING_ARRAY(json_expr[, json_path])

설명

스칼라 값의 배열을 추출하고 문자열 형식 스칼라 값의 배열을 반환합니다. 스칼라 값은 문자열, 숫자, 부울을 나타낼 수 있습니다. JSON 키가 잘못된 JSONPath 문자를 사용하는 경우, 작은 따옴표와 대괄호를 사용하여 해당 문자를 이스케이프 처리할 수 있습니다.

  • json_string_expr: JSON 형식의 문자열입니다. 예를 들면 다음과 같습니다.

    '{"class" : {"students" : [{"name" : "Jane"}]}}'
    
  • json_expr: JSON입니다. 예를 들면 다음과 같습니다.

    JSON '{"class" : {"students" : [{"name" : "Jane"}]}}'
    
  • json_path: JSONPath입니다. 이는 입력에서 가져오려는 데이터를 식별합니다. 이 선택적 매개변수를 제공하지 않으면 JSONPath $ 기호가 적용됩니다. 즉, 모든 데이터가 분석됩니다.

주의사항:

  • 입력 배열의 JSON null은 SQL NULL을 해당 JSON null의 출력으로 생성합니다. 출력에 NULL 배열 요소가 포함된 경우 최종 출력이 NULL 값이 있는 배열이 될 수 없으므로 오류가 발생합니다.
  • JSONPath는 스칼라 객체와 JSON null이 포함된 배열을 대조하는 경우 최종 출력이 NULL 값이 있는 배열이 될 수 없으므로 함수의 출력을 변환해야 합니다.

반환 유형

ARRAY<STRING>

예시

JSON의 항목을 문자열 배열로 추출합니다.

SELECT JSON_EXTRACT_STRING_ARRAY(
  JSON '{"fruits":["apples","oranges","grapes"]}','$.fruits'
  ) AS string_array;

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

다음 예시에서는 JSON_EXTRACT_ARRAY 함수와 JSON_EXTRACT_STRING_ARRAY 함수의 결과를 반환하는 방법을 비교합니다.

SELECT JSON_EXTRACT_ARRAY('["apples","oranges"]') AS json_array,
JSON_EXTRACT_STRING_ARRAY('["apples","oranges"]') AS string_array;

+-----------------------+-------------------+
| json_array            | string_array      |
+-----------------------+-------------------+
| ["apples", "oranges"] | [apples, oranges] |
+-----------------------+-------------------+

JSON 형식 문자열의 항목을 문자열 배열로 추출합니다.

-- Strips the double quotes
SELECT JSON_EXTRACT_STRING_ARRAY('["foo","bar","baz"]','$') AS string_array;

+-----------------+
| string_array    |
+-----------------+
| [foo, bar, baz] |
+-----------------+

문자열 배열을 추출하고 이를 정수 배열로 변환합니다.

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

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

다음은 동일합니다.

SELECT JSON_EXTRACT_STRING_ARRAY('{"fruits":["apples","oranges","grapes"]}','$[fruits]') AS string_array;

SELECT JSON_EXTRACT_STRING_ARRAY('{"fruits":["apples","oranges","grapes"]}','$.fruits') AS string_array;

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

JSON 키가 잘못된 JSONPath 문자를 사용하는 경우, 작은 따옴표와 대괄호([' '])를 사용하여 해당 문자를 이스케이프 처리할 수 있습니다. 예를 들면 다음과 같습니다.

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

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

다음 예시에서는 잘못된 요청 및 빈 배열의 처리 방법을 살펴봅니다.

-- An error is thrown if you provide an invalid JSONPath.
SELECT JSON_EXTRACT_STRING_ARRAY('["foo","bar","baz"]','INVALID_JSONPath') AS result;

-- If the JSON formatted string is invalid, then NULL is returned.
SELECT JSON_EXTRACT_STRING_ARRAY('}}','$') AS result;

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

-- If the JSON document is NULL, then NULL is returned.
SELECT JSON_EXTRACT_STRING_ARRAY(NULL,'$') AS result;

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

-- If a JSONPath does not match anything, then the output is NULL.
SELECT JSON_EXTRACT_STRING_ARRAY('{"a":["foo","bar","baz"]}','$.b') AS result;

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

-- If a JSONPath matches an object that is not an array, then the output is NULL.
SELECT JSON_EXTRACT_STRING_ARRAY('{"a":"foo"}','$') AS result;

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

-- If a JSONPath matches an array of non-scalar objects, then the output is NULL.
SELECT JSON_EXTRACT_STRING_ARRAY('{"a":[{"b":"foo","c":1},{"b":"bar","c":2}],"d":"baz"}','$.a') AS result;

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

-- If a JSONPath matches an array of mixed scalar and non-scalar objects, then the output is NULL.
SELECT JSON_EXTRACT_STRING_ARRAY('{"a":[10, {"b": 20}]','$.a') AS result;

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

-- If a JSONPath matches an empty JSON array, then the output is an empty array instead of NULL.
SELECT JSON_EXTRACT_STRING_ARRAY('{"a":"foo","b":[]}','$.b') AS result;

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

-- The following query produces and error because the final output cannot be an
-- array with NULLs.
SELECT JSON_EXTRACT_STRING_ARRAY('["world", 1, null]') AS result;

JSON_VALUE_ARRAY

JSON_VALUE_ARRAY(json_string_expr[, json_path])
JSON_VALUE_ARRAY(json_expr[, json_path])

설명

스칼라 값의 배열을 추출하고 문자열 형식 스칼라 값의 배열을 반환합니다. 스칼라 값은 문자열, 숫자, 부울을 나타낼 수 있습니다. JSON 키가 잘못된 JSONPath 문자를 사용하는 경우 큰따옴표를 사용하여 해당 문자를 이스케이프 처리할 수 있습니다.

  • json_string_expr: JSON 형식의 문자열입니다. 예를 들면 다음과 같습니다.

    '{"class" : {"students" : [{"name" : "Jane"}]}}'
    
  • json_expr: JSON입니다. 예를 들면 다음과 같습니다.

    JSON '{"class" : {"students" : [{"name" : "Jane"}]}}'
    
  • json_path: JSONPath입니다. 이는 입력에서 가져오려는 데이터를 식별합니다. 이 선택적 매개변수를 제공하지 않으면 JSONPath $ 기호가 적용됩니다. 즉, 모든 데이터가 분석됩니다.

주의사항:

  • 입력 배열의 JSON null은 JSON null의 출력으로 SQL NULL을 생성합니다. 출력에 NULL 배열 요소가 포함된 경우 최종 출력이 NULL 값이 있는 배열이 될 수 없으므로 오류가 발생합니다.
  • JSONPath는 스칼라 객체와 JSON null이 포함된 배열을 대조하는 경우 최종 출력이 NULL 값이 있는 배열이 될 수 없으므로 함수의 출력을 변환해야 합니다.

반환 유형

ARRAY<STRING>

예시

JSON의 항목을 문자열 배열로 추출합니다.

SELECT JSON_VALUE_ARRAY(
  JSON '{"fruits":["apples","oranges","grapes"]}','$.fruits'
  ) AS string_array;

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

다음 예시에서는 JSON_QUERY_ARRAY 함수와 JSON_VALUE_ARRAY 함수의 결과를 반환하는 방법을 비교합니다.

SELECT JSON_QUERY_ARRAY('["apples","oranges"]') AS json_array,
       JSON_VALUE_ARRAY('["apples","oranges"]') AS string_array;

+-----------------------+-------------------+
| json_array            | string_array      |
+-----------------------+-------------------+
| ["apples", "oranges"] | [apples, oranges] |
+-----------------------+-------------------+

JSON 형식 문자열의 항목을 문자열 배열로 추출합니다.

-- Strips the double quotes
SELECT JSON_VALUE_ARRAY('["foo","bar","baz"]','$') AS string_array;

+-----------------+
| string_array    |
+-----------------+
| [foo, bar, baz] |
+-----------------+

문자열 배열을 추출하고 이를 정수 배열로 변환합니다.

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

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

다음은 동일합니다.

SELECT JSON_VALUE_ARRAY('{"fruits":["apples","oranges","grapes"]}','$.fruits') AS string_array;
SELECT JSON_VALUE_ARRAY('{"fruits":["apples","oranges","grapes"]}','$."fruits"') AS string_array;

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

JSON 키가 잘못된 JSONPath 문자를 사용하는 경우, 큰따옴표(" ")를 사용하여 해당 문자를 이스케이프할 수 있습니다. 예를 들면 다음과 같습니다.

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

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

다음 예시에서는 잘못된 요청 및 빈 배열의 처리 방법을 살펴봅니다.

-- An error is thrown if you provide an invalid JSONPath.
SELECT JSON_VALUE_ARRAY('["foo","bar","baz"]','INVALID_JSONPath') AS result;

-- If the JSON-formatted string is invalid, then NULL is returned.
SELECT JSON_VALUE_ARRAY('}}','$') AS result;

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

-- If the JSON document is NULL, then NULL is returned.
SELECT JSON_VALUE_ARRAY(NULL,'$') AS result;

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

-- If a JSONPath does not match anything, then the output is NULL.
SELECT JSON_VALUE_ARRAY('{"a":["foo","bar","baz"]}','$.b') AS result;

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

-- If a JSONPath matches an object that is not an array, then the output is NULL.
SELECT JSON_VALUE_ARRAY('{"a":"foo"}','$') AS result;

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

-- If a JSONPath matches an array of non-scalar objects, then the output is NULL.
SELECT JSON_VALUE_ARRAY('{"a":[{"b":"foo","c":1},{"b":"bar","c":2}],"d":"baz"}','$.a') AS result;

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

-- If a JSONPath matches an array of mixed scalar and non-scalar objects,
-- then the output is NULL.
SELECT JSON_VALUE_ARRAY('{"a":[10, {"b": 20}]','$.a') AS result;

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

-- If a JSONPath matches an empty JSON array, then the output is an empty array instead of NULL.
SELECT JSON_VALUE_ARRAY('{"a":"foo","b":[]}','$.b') AS result;

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

-- The following query produces and error because the final output cannot be an
-- array with NULLs.
SELECT JSON_VALUE_ARRAY('["world", 1, null]') AS result;

PARSE_JSON

PARSE_JSON(json_string_expr[, wide_number_mode=>{ 'exact' | 'round' }])

설명

SQL STRING 값을 사용하여 SQL JSON 값을 반환합니다. STRING 값은 문자열 형식의 JSON 값을 나타냅니다.

이 함수는 정밀도의 손실 없이 JSON 값에 저장할 수 없는 숫자를 처리하는 방법을 결정하는 wide_number_mode라는 필수 이름이 지정된 인수를 지원합니다(선택사항). 사용할 경우 wide_number_mode는 다음 값 중 하나를 포함해야 합니다.

  • exact: 정밀도를 유지하면서 저장할 수 있는 숫자만 허용합니다. 정밀도 손실 없이 저장할 수 없는 숫자가 발생하면 함수에서 오류가 발생합니다.
  • round: 정밀도 손실 없이 저장할 수 없는 숫자는 정밀도 손실 없이 저장할 수 있는 수로 반올림합니다. 숫자를 반올림할 수 없으면 함수에서 오류가 발생합니다.

wide_number_mode를 사용하지 않으면 이 함수에 암시적으로 wide_number_mode=>'exact'가 포함됩니다. JSON 객체 또는 배열에 숫자가 나타나면 객체 또는 배열의 번호에 wide_number_mode 인수가 적용됩니다.

다음 도메인의 숫자를 정밀도의 손실 없이 JSON에 저장할 수 있습니다.

  • 부호 있는/부호 없는 64비트 정수(예: INT64)
  • FLOAT64

반환 유형

JSON

예시

다음 예시에서는 JSON 형식의 문자열이 JSON으로 변환됩니다.

SELECT PARSE_JSON('{"coordinates":[10,20],"id":1}') AS json_data;

+--------------------------------+
| json_data                      |
+--------------------------------+
| {"coordinates":[10,20],"id":1} |
+--------------------------------+

다음과 같은 이유로 쿼리가 실패합니다.

  • 전달된 수는 정밀도 손실 없이 저장할 수 없습니다.
  • wide_number_mode=>'exact'는 첫 번째 쿼리에서 암시적으로 사용되며 두 번째 쿼리에서 명시적으로 사용됩니다.
SELECT PARSE_JSON('{"id":922337203685477580701}') AS json_data; -- fails
SELECT PARSE_JSON('{"id":922337203685477580701}', wide_number_mode=>'exact') AS json_data; -- fails

다음 쿼리는 숫자를 JSON에 저장할 수 있는 숫자로 반올림합니다.

SELECT PARSE_JSON('{"id":922337203685477580701}', wide_number_mode=>'round') AS json_data;

+--------------------------------+
| json_data                      |
+--------------------------------+
| {"id":9.223372036854776e+20}   |
+--------------------------------+

TO_JSON

TO_JSON(sql_value[, stringify_wide_numbers=>{ TRUE | FALSE }])

설명

SQL 값을 사용하여 JSON 값을 반환합니다. 이 값은 지원되는 BigQuery 데이터 유형이어야 합니다. 이 함수에서 지원하는 BigQuery 데이터 유형과 해당 JSON 인코딩을 여기에서 검토할 수 있습니다.

이 함수는 stringify_wide_numbers라는 선택적 필수 이름 인수를 지원합니다.

  • 이 인수가 TRUE이면 FLOAT64 유형 도메인 외부의 숫자 값은 문자열로 인코딩됩니다.
  • 이 인수가 사용되지 않거나 FALSE이면 FLOAT64 유형 도메인 외부의 숫자 값은 문자열로 인코딩되지 않고 JSON 숫자로 저장됩니다. 숫자 값을 정밀도 손실 없이 JSON에 저장할 수 없으면 오류가 발생합니다.

다음 숫자 데이터 유형은 stringify_wide_numbers 인수의 영향을 받습니다.

  • INT64
  • NUMERIC
  • BIGNUMERIC

이러한 숫자 데이터 유형 중 하나가 ARRAY 또는 STRUCT와 같은 컨테이너 데이터 유형에 표시되면 stringify_wide_numbers 인수가 컨테이너 데이터 유형의 숫자 데이터 유형에 적용됩니다. 에 명시된 할인율을 적용합니다.

반환 유형

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 TO_JSON(t) AS json_objects
FROM CoordinatesTable AS t;

+--------------------------------+
| json_objects                   |
+--------------------------------+
| {"coordinates":[10,20],"id":1} |
| {"coordinates":[30,40],"id":2} |
| {"coordinates":[50,60],"id":3} |
+--------------------------------+

다음 예시의 쿼리는 큰 숫자 값을 JSON 문자열로 반환합니다.

SELECT TO_JSON(9007199254740993, stringify_wide_numbers=>TRUE) as stringify_on

+--------------------+
| stringify_on       |
+--------------------+
| "9007199254740993" |
+--------------------+

다음 예시에서 두 쿼리는 모두 큰 숫자 값을 JSON 숫자로 반환합니다.

SELECT TO_JSON(9007199254740993, stringify_wide_numbers=>FALSE) as stringify_off
SELECT TO_JSON(9007199254740993) as stringify_off

+------------------+
| stringify_off    |
+------------------+
| 9007199254740993 |
+------------------+

다음 예시에서는 큰 숫자 값만 JSON 문자열로 변환됩니다.

With T1 AS (
  (SELECT 9007199254740993 AS id) UNION ALL
  (SELECT 2 AS id))
SELECT TO_JSON(t, stringify_wide_numbers=>TRUE) AS json_objects
FROM T1 AS t;

+---------------------------+
| json_objects              |
+---------------------------+
| {"id":"9007199254740993"} |
| {"id":2}                  |
+---------------------------+

이 예시에서 9007199254740993(INT64) 및 2.1(FLOAT64) 값은 stringify_wide_numbers 인수의 영향을 받지 않는 공통 상위 유형 FLOAT64로 변환됩니다.

With T1 AS (
  (SELECT 9007199254740993 AS id) UNION ALL
  (SELECT 2.1 AS id))
SELECT TO_JSON(t, stringify_wide_numbers=>TRUE) AS json_objects
FROM T1 AS t;

+------------------------------+
| json_objects                 |
+------------------------------+
| {"id":9.007199254740992e+15} |
| {"id":2.1}                   |
+------------------------------+

TO_JSON_STRING

TO_JSON_STRING(value[, pretty_print])

설명

SQL 값을 사용하고 해당 값을 JSON 형식의 문자열 표현으로 반환합니다. 이 값은 지원되는 BigQuery 데이터 유형이어야 합니다. 이 함수가 지원하는 BigQuery 데이터 유형 및 해당 JSON 인코딩을 여기에서 검토할 수 있습니다.

이 함수는 pretty_print라는 선택적 부울 매개변수를 지원합니다. pretty_printtrue면 반환되는 값이 읽기 쉬운 형식으로 지정됩니다.

반환 유형

JSON 형식의 STRING

예시

테이블의 행을 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             |
|    |             |   ]                |
|    |             | }                  |
+----+-------------+--------------------+

문자열

STRING(json_expr)

설명

JSON 표현식을 가져와서 JSON 문자열을 추출하고 이 값을 SQL STRING으로 반환합니다. 표현식이 SQL NULL이면 함수가 SQL NULL을 반환합니다. 추출된 JSON 값이 문자열이 아니면 오류가 발생합니다.

  • json_expr: JSON입니다. 예를 들면 다음과 같습니다.

    JSON '{"name": "sky", "color" : "blue"}'
    

반환 유형

STRING

예시

SELECT STRING(JSON '"purple"') AS color;

+--------+
| color  |
+--------+
| purple |
+--------+
SELECT STRING(JSON_QUERY(JSON '{"name": "sky", "color": "blue"}', "$.color")) AS color;

+-------+
| color |
+-------+
| blue  |
+-------+

다음 예시에서는 잘못된 요청을 처리하는 방법을 보여줍니다.

-- An error is thrown if the JSON is not of type string.
SELECT STRING(JSON '123') AS result; -- Throws an error
SELECT STRING(JSON 'null') AS result; -- Throws an error
SELECT SAFE.STRING(JSON '123') AS result; -- Returns a SQL NULL

BOOL

BOOL(json_expr)

설명

JSON 표현식을 가져와서 JSON 부울을 추출하고 이 값을 SQL BOOL로 반환합니다. 표현식이 SQL NULL이면 함수가 SQL NULL을 반환합니다. 추출된 JSON 값이 부울이 아니면 오류가 발생합니다.

  • json_expr: JSON입니다. 예를 들면 다음과 같습니다.

    JSON '{"name": "sky", "color" : "blue"}'
    

반환 유형

BOOL

예시

SELECT BOOL(JSON 'true') AS vacancy;

+---------+
| vacancy |
+---------+
| true    |
+---------+
SELECT BOOL(JSON_QUERY(JSON '{"hotel class": "5-star", "vacancy": true}', "$.vacancy")) AS vacancy;

+---------+
| vacancy |
+---------+
| true    |
+---------+

다음 예시에서는 잘못된 요청을 처리하는 방법을 보여줍니다.

-- An error is thrown if JSON is not of type bool.
SELECT BOOL(JSON '123') AS result; -- Throws an error
SELECT BOOL(JSON 'null') AS result; -- Throw an error
SELECT SAFE.BOOL(JSON '123') AS result; -- Returns a SQL NULL

INT64

INT64(json_expr)

설명

JSON 표현식을 가져와서 JSON 숫자를 추출하고 이 값을 SQL INT64로 반환합니다. 표현식이 SQL NULL이면 함수가 SQL NULL을 반환합니다. 추출된 JSON 숫자에 소수 부분이 있거나 INT64 도메인 범위를 벗어나면 오류가 발생합니다.

  • json_expr: JSON입니다. 예를 들면 다음과 같습니다.

    JSON '{"name": "sky", "color" : "blue"}'
    

반환 유형

INT64

예시

SELECT INT64(JSON '2005') AS flight_number;

+---------------+
| flight_number |
+---------------+
| 2005          |
+---------------+
SELECT INT64(JSON_QUERY(JSON '{"gate": "A4", "flight_number": 2005}', "$.flight_number")) AS flight_number;

+---------------+
| flight_number |
+---------------+
| 2005          |
+---------------+
SELECT INT64(JSON '10.0') AS score;

+-------+
| score |
+-------+
| 10    |
+-------+

다음 예시에서는 잘못된 요청을 처리하는 방법을 보여줍니다.

-- An error is thrown if JSON is not a number or cannot be converted to a 64-bit integer.
SELECT INT64(JSON '10.1') AS result;  -- Throws an error
SELECT INT64(JSON '"strawberry"') AS result; -- Throws an error
SELECT INT64(JSON 'null') AS result; -- Throws an error
SELECT SAFE.INT64(JSON '"strawberry"') AS result;  -- Returns a SQL NULL

FLOAT64

FLOAT64(json_expr[, wide_number_mode=>{ 'exact' | 'round' }])

설명

JSON 표현식을 가져와서 JSON 숫자를 추출하고 이 값을 SQL FLOAT64로 반환합니다. 표현식이 SQL NULL이면 함수가 SQL NULL을 반환합니다. 추출된 JSON 값이 숫자가 아니면 오류가 발생합니다.

  • json_expr: JSON입니다. 예를 들면 다음과 같습니다.

    JSON '{"name": "sky", "color" : "blue"}'
    

이 함수는 정밀도 손실 없이 FLOAT64로 표시될 수 없는 숫자에 발생하는 결과를 정의하는 wide_number_mode라는 선택적인 필수 이름 지정 인수를 지원합니다.

이 인수는 두 개의 대소문자 구분 값 중 하나를 허용합니다.

  • ‘exact’: 정밀도 손실 없이 결과를 FLOAT64로 표현할 수 없으면 함수가 실패합니다.
  • ‘round’: JSON에 저장된 숫자 값이 FLOAT64로 반올림됩니다. 반올림할 수 없으면 함수가 실패합니다. 인수를 지정하지 않으면 이 값이 기본값입니다.

반환 유형

FLOAT64

예시

SELECT FLOAT64(JSON '9.8') AS velocity;

+----------+
| velocity |
+----------+
| 9.8      |
+----------+
SELECT FLOAT64(JSON_QUERY(JSON '{"vo2_max": 39.1, "age": 18}', "$.vo2_max")) AS vo2_max;

+---------+
| vo2_max |
+---------+
| 39.1    |
+---------+
SELECT FLOAT64(JSON '18446744073709551615', wide_number_mode=>'round') as result;

+------------------------+
| result                 |
+------------------------+
| 1.8446744073709552e+19 |
+------------------------+
SELECT FLOAT64(JSON '18446744073709551615') as result;

+------------------------+
| result                 |
+------------------------+
| 1.8446744073709552e+19 |
+------------------------+

다음 예시에서는 잘못된 요청을 처리하는 방법을 보여줍니다.

-- An error is thrown if JSON is not of type FLOAT64.
SELECT FLOAT64(JSON '"strawberry"') AS result;
SELECT FLOAT64(JSON 'null') AS result;

-- An error is thrown because `wide_number_mode` is case-sensitive and not "exact" or "round".
SELECT FLOAT64(JSON '123.4', wide_number_mode=>'EXACT') as result;
SELECT FLOAT64(JSON '123.4', wide_number_mode=>'exac') as result;

-- An error is thrown because the number cannot be converted to DOUBLE without loss of precision
SELECT FLOAT64(JSON '18446744073709551615', wide_number_mode=>'exact') as result;

-- Returns a SQL NULL
SELECT SAFE.FLOAT64(JSON '"strawberry"') AS result;

JSON_TYPE

JSON_TYPE(json_expr)

설명

JSON 표현식을 가져와서 가장 바깥쪽 JSON 값의 유형을 SQL STRING으로 반환합니다. 다음과 같은 JSON 유형의 이름을 반환할 수 있습니다.

  • object
  • array
  • string
  • number
  • boolean
  • null

표현식이 SQL NULL이면 함수가 SQL NULL를 반환합니다. 추출된 JSON 값이 유효한 JSON 유형이 아니면 오류가 발생합니다.

  • json_expr: JSON입니다. 예를 들면 다음과 같습니다.

    JSON '{"name": "sky", "color" : "blue"}'
    

반환 유형

STRING

예시

SELECT json_val, JSON_TYPE(json_val) AS type
FROM
  UNNEST(
    [
      JSON '"apple"',
      JSON '10',
      JSON '3.14',
      JSON 'null',
      JSON '{"city": "New York", "State": "NY"}',
      JSON '["apple", "banana"]',
      JSON 'false'
    ]
  ) AS json_val;

+----------------------------------+---------+
| json_val                         | type    |
+----------------------------------+---------+
| "apple"                          | string  |
| 10                               | number  |
| 3.14                             | number  |
| null                             | null    |
| {"State":"NY","city":"New York"} | object  |
| ["apple","banana"]               | array   |
| false                            | boolean |
+----------------------------------+---------+

JSON 인코딩

다음 표에는 SQL 값이 TO_JSON_STRING 또는 TO_JSON 함수를 통해 JSON 값으로 인코딩될 때 사용되는 일반적인 인코딩이 포함되어 있습니다.

From SQL To JSON 예시
NULL

null

SQL 입력: NULL
JSON 출력: null
BOOL 부울 SQL 입력: TRUE
JSON 출력: true

SQL 입력: FALSE
JSON 출력: false
INT64

(TO_JSON_STRING만 해당)

숫자 또는 문자열

값이 [-253, 253] 범위에 있을 때 숫자로 인코딩됩니다. 이 범위는 IEEE 754 배정밀도 부동 소수점 숫자로 느슨하게 표현될 수 있는 정수 범위입니다. 이 범위를 벗어나는 값은 문자열로 인코딩됩니다.

SQL 입력: 9007199254740992
JSON 출력: 9007199254740992

SQL 입력: 9007199254740993
JSON 출력: "9007199254740993"
INT64

(TO_JSON만 해당)

숫자 또는 문자열

stringify_wide_numbers 인수가 TRUE이고 값이 FLOAT64 유형 도메인 외부에 있으면 값은 문자열로 인코딩됩니다. 정밀도를 유지하면서 값을 JSON으로 저장할 수 없는 경우 함수가 실패합니다. 그렇지 않으면 값이 숫자로 인코딩됩니다.

stringify_wide_numbers가 사용되지 않거나 FALSE이면 `FLOAT64` 유형 도메인 외부의 숫자 값은 문자열로 인코딩되지 않지만 JSON 숫자로 저장됩니다. 숫자 값을 정밀도 손실 없이 JSON에 저장할 수 없으면 오류가 발생합니다.

SQL 입력: 9007199254740992
JSON 출력: 9007199254740992

SQL 입력: 9007199254740993
JSON 출력: 9007199254740993

stringify_wide_numbers=>TRUE로 SQL 입력: 9007199254740992
JSON 출력: 9007199254740992

stringify_wide_numbers=>TRUE가 포함된 SQL 입력: 9007199254740993
JSON 출력: "9007199254740993"
NUMERIC
BIGNUMERIC

(TO_JSON_STRING만 해당)

숫자 또는 문자열

값이 [-253, 253] 범위 내에 있고 소수 부분이 없을 때 숫자로 인코딩됩니다. 이 범위를 벗어나는 값은 문자열로 인코딩됩니다.

SQL 입력: -1
JSON 출력: -1

SQL 입력: 0
JSON 출력: 0

SQL 입력: 9007199254740993
JSON 출력: "9007199254740993"

SQL 입력: 123.56
JSON 출력: "123.56"
NUMERIC
BIGNUMERIC

(TO_JSON만 해당)

숫자 또는 문자열

stringify_wide_numbers 인수가 TRUE이고 값이 FLOAT64 유형 도메인 외부에 있으면 문자열로 인코딩됩니다. 그렇지 않으면 숫자로 인코딩됩니다.

SQL input: -1
JSON 출력: -1

SQL 입력: 0
JSON 출력: 0

SQL 입력: 9007199254740993
JSON 출력: 9007199254740993

SQL 입력: 123.56
JSON 출력: 123.56

stringify_wide_numbers=>TRUE로 SQL 입력: 9007199254740993
JSON 출력: "9007199254740993"

stringify_wide_numbers=>TRUE로 SQL 입력: 123.56
JSON 출력: 123.56
FLOAT64

숫자 또는 문자열

+/-infNaNInfinity, -Infinity, NaN으로 인코딩됩니다. 그렇지 않으면 이 값이 숫자로 인코딩됩니다.

SQL 입력: 1.0
JSON 출력: 1

SQL 입력: 9007199254740993
JSON 출력: 9007199254740993

SQL 입력: "+inf"
JSON 출력: "Infinity"

SQL 입력: "-inf"
JSON 출력: "-Infinity"

SQL 입력: "NaN"
JSON 출력: "NaN"
문자열

문자열

JSON 표준에 따라 이스케이프 처리된 문자열로 인코딩됩니다. 구체적으로 말해 ", \, 그리고 U+0000부터 U+001F까지의 제어 문자가 이스케이프 처리됩니다.

SQL 입력: "abc"
JSON 출력: "abc"

SQL 입력: "\"abc\""
JSON 출력: "\"abc\""
BYTES

문자열

RFC 4648 Base64 데이터 인코딩을 사용합니다.

SQL 입력: b"Google"
JSON 출력: "R29vZ2xl"
DATE 문자열 SQL 입력: DATE '2017-03-06'
JSON 출력: "2017-03-06"
TIMESTAMP

문자열

ISO 8601 날짜 및 시간으로 인코딩됩니다. 여기서 T는 날짜와 시간을 분리하고 Z(Zulu/UTC)는 시간대를 나타냅니다.

SQL 입력: TIMESTAMP '2017-03-06 12:34:56.789012'
JSON 출력: "2017-03-06T12:34:56.789012Z"
DATETIME

문자열

ISO 8601 날짜 및 시간으로 인코딩됩니다. 여기서 T는 날짜와 시간을 구분합니다.

SQL 입력: DATETIME '2017-03-06 12:34:56.789012'
JSON 출력: "2017-03-06T12:34:56.789012"
TIME

문자열

ISO 8601 시간으로 인코딩됩니다.

SQL 입력: TIME '12:34:56.789012'
JSON 출력: "12:34:56.789012"
JSON

입력 JSON 데이터

SQL 입력: JSON '{"item": "pen", "price": 10}'
JSON 출력: {"item":"pen", "price":10}

SQL 입력:[1, 2, 3]
JSON 출력:[1, 2, 3]
배열

배열

0개 이상의 요소를 포함할 수 있습니다.

SQL 입력: ["red", "blue", "green"]
JSON 출력: ["red","blue","green"]

SQL 입력:[1, 2, 3]
JSON 출력:[1,2,3]
STRUCT

객체

객체에는 0개 또는 그 이상의 키-값 쌍이 포함될 수 있습니다. 각 값은 유형에 따라 형식이 지정됩니다.

TO_JSON의 경우 필드가 출력 문자열에 포함되고 이 필드의 중복은 생략됩니다. TO_JSON_STRING의 경우 필드와 이 필드의 모든 중복 항목이 출력 문자열에 포함됩니다.

익명 필드는 ""로 표시됩니다.

UTF-8 필드 이름이 유효하지 않으면 JSON을 파싱하지 못할 수 있습니다. 문자열 값은 JSON 표준에 따라 이스케이프 처리됩니다. 구체적으로 말해 ", \, 그리고 U+0000부터 U+001F까지의 제어 문자가 이스케이프 처리됩니다.

SQL 입력: STRUCT(12 AS purchases, TRUE AS inStock)
JSON 출력: {"inStock": true,"purchases":12}

JSONPath 형식

JSONPath 형식을 사용하면 JSON 형식 문자열에서 가져오려는 값을 식별할 수 있습니다. JSONPath 형식에서는 다음 연산자를 지원합니다.

연산자 설명 예시
$ 루트 객체 또는 요소입니다. JSONPath 형식은 JSON 형식 문자열의 가장 바깥 수준을 참조하는 이 연산자로 시작해야 합니다.

JSON 형식 문자열:
'{"class" : {"students" : [{"name" : "Jane"}]}}'

JSON 경로:
"$"

JSON 결과:
{"class":{"students":[{"name":"Jane"}]}}

. 하위 연산자. 점 표기법을 사용하여 하위 값을 식별할 수 있습니다.

JSON 형식 문자열:
'{"class" : {"students" : [{"name" : "Jane"}]}}'

JSON 경로:
"$.class.students"

JSON 결과:
[{"name":"Jane"}]

[] 아래 첨자 연산자입니다. JSON 객체가 배열인 경우에는 대괄호를 사용하여 배열 색인을 지정할 수 있습니다.

JSON 형식 문자열:
'{"class" : {"students" : [{"name" : "Jane"}]}}'

JSON 경로:
"$.class.students[0]"

JSON 결과:
{"name":"Jane"}

JSON 함수의 키에 JSON 형식 연산자가 포함된 경우 이스케이프 처리 방법은 각 JSON 함수를 참조하세요.

JSONPath 형식이 JSON 형식 문자열의 값과 일치하지 않으면 JSON 함수에서 NULL을 반환합니다. 스칼라 함수에 선택된 값이 스칼라가 아닌 경우(예: 객체 또는 배열) 함수에서 NULL을 반환합니다. JSONPath 형식이 잘못된 경우 오류가 발생합니다.