표준 SQL의 JSON 함수

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

함수 개요

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

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

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

기존 JSON 추출 함수

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

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

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

기타 JSON 함수

JSON 함수 설명 반환 유형
TO_JSON_STRING SQL 값을 사용하고 해당 값을 JSON 형식의 문자열 표현으로 반환합니다. JSON 형식 STRING

JSON_EXTRACT

JSON_EXTRACT(json_string_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_path: JSONPath입니다. 이는 입력에서 가져오려는 데이터를 식별합니다. 이 선택적 매개변수를 제공하지 않으면 JSONPath $ 기호가 적용됩니다. 즉, 모든 데이터가 분석됩니다.

    SELECT JSON_EXTRACT('{"a":null}', "$.a"); -- Returns a SQL NULL
    SELECT JSON_EXTRACT('{"a":null}', "$.b"); -- Returns a SQL NULL
    

추출에 배열과 같은 비스칼라 값을 포함하려면 JSON_EXTRACT를 사용합니다. 문자열, 숫자, 부울과 같은 스칼라 값만 추출하려면 JSON_EXTRACT_SCALAR를 사용합니다.

반환 유형

JSON 형식의 STRING

예시

다음 예시에서는 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"}] |
+------------------------------------+

JSON_QUERY

JSON_QUERY(json_string_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_path: JSONPath입니다. 이는 입력에서 가져오려는 데이터를 식별합니다. 이 선택적 매개변수를 제공하지 않으면 JSONPath $ 기호가 적용됩니다. 즉, 모든 데이터가 분석됩니다.

    SELECT JSON_QUERY('{"a":null}', "$.a"); -- Returns a SQL NULL
    SELECT JSON_QUERY('{"a":null}', "$.b"); -- Returns a SQL NULL
    

추출에 배열과 같은 비스칼라 값을 포함하려면 JSON_QUERY를 사용합니다. 문자열, 숫자, 부울과 같은 스칼라 값만 추출하려면 JSON_VALUE를 사용합니다.

반환 유형

JSON 형식의 STRING

예시

다음 예시에서는 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"}] |
+------------------------------------+

JSON_EXTRACT_SCALAR

JSON_EXTRACT_SCALAR(json_string_expr[, json_path])

설명

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

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

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

    json_path가 JSON null 또는 비스칼라 값(즉, json_path가 객체 또는 배열을 참조하는 경우)을 반환하는 경우 SQL NULL이 반환됩니다. 이 선택적 매개변수를 제공하지 않으면 JSONPath $ 기호가 적용됩니다. 즉, 전체 JSON 형식 문자열이 분석됩니다.

문자열, 숫자, 부울과 같은 스칼라 값만 추출하려면 JSON_EXTRACT_SCALAR를 사용합니다. 추출에 배열과 같은 비스칼라 값을 포함하려면 JSON_EXTRACT를 사용합니다.

반환 유형

STRING

예시

다음 예시에서는 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 키가 잘못된 JSONPath 문자를 사용하는 경우 큰따옴표를 사용하여 해당 문자를 이스케이프 처리할 수 있습니다.

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

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

    json_path가 JSON null 또는 비스칼라 값(즉, json_path가 객체 또는 배열을 참조하는 경우)을 반환하는 경우 SQL NULL이 반환됩니다. 이 선택적 매개변수를 제공하지 않으면 JSONPath $ 기호가 적용됩니다. 즉, 전체 JSON 형식 문자열이 분석됩니다.

문자열, 숫자, 부울과 같은 스칼라 값만 추출하려면 JSON_VALUE를 사용합니다. 추출에 배열과 같은 비스칼라 값을 포함하려면 JSON_QUERY를 사용합니다.

반환 유형

STRING

예시

다음 예시에서는 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 값의 배열과 문자열, 숫자, 부울과 같은 JSON 스칼라 값을 추출합니다. JSON 키가 잘못된 JSONPath 문자를 사용하는 경우, 작은 따옴표와 대괄호를 사용하여 해당 문자를 이스케이프 처리할 수 있습니다.

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

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

    이 선택적 매개변수를 제공하지 않으면 JSONPath $ 기호가 적용됩니다. 즉, 전체 JSON 형식 문자열이 분석됩니다.

반환 유형

ARRAY<JSON-formatted STRING>

예시

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

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

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

반환 유형

ARRAY<JSON-formatted STRING>

예시

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 키가 잘못된 JSONPath 문자를 사용하는 경우, 작은 따옴표와 대괄호를 사용하여 해당 문자를 이스케이프 처리할 수 있습니다.

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

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

반환 유형

ARRAY<STRING>

예시

다음 예시에서는 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 |
+--------+
| []     |
+--------+

-- If a JSONPath matches an array that contains scalar values and a JSON null,
-- then the output of the JSON_EXTRACT_STRING_ARRAY function must be transformed
-- because the final output cannot be an array with NULL values. This example
-- uses the UNNEST operator to convert the output array into a table as the final output.
SELECT string_value FROM UNNEST(JSON_EXTRACT_STRING_ARRAY('["world", 1, null]')) AS string_value;

+--------------+
| string_value |
+--------------+
| world        |
| 1            |
| NULL         |
+--------------+

JSON_VALUE_ARRAY

JSON_VALUE_ARRAY(json_string_expr[, json_path])

설명

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

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

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

반환 유형

ARRAY<STRING>

예시

다음 예시에서는 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 |
+--------+
| []     |
+--------+

-- If a JSONPath matches an array that contains scalar objects and a JSON null,
-- then the output of the JSON_VALUE_ARRAY function must be transformed
-- because the final output cannot be an array with NULL values. This example
-- uses the UNNEST operator to convert the output array into a table as the final output.
SELECT string_value FROM UNNEST(JSON_VALUE_ARRAY('["world", 1, null]')) AS string_value;

+--------------+
| string_value |
+--------------+
| world        |
| 1            |
| NULL         |
+--------------+

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

JSON 인코딩

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

From SQL To JSON 예시
NULL

null

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

SQL 입력: FALSE
JSON 출력: false
INT64

숫자 또는 문자열

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

SQL 입력: 9007199254740992
JSON 출력: 9007199254740992

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

숫자 또는 문자열

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

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

SQL 입력: 0
JSON 출력: 0

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

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"
STRING

문자열

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"
시간

문자열

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

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

배열

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

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

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

객체

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

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

대부분의 JSON 함수는 json_string_exprjson_path 매개 변수를 전달합니다. json_string_expr 매개 변수는 JSON 형식의 문자열로 전달되며 json_path 매개 변수는 JSON 형식의 문자열에서 가져오려는 값을 식별합니다.

json_string_expr 매개 변수는 다음과 같은 형식의 JSON 문자열이어야 합니다.

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

JSONPath 형식을 사용하여 json_path 매개 변수를 구성합니다. 이 형식의 일부로 이 매개변수는 JSON 형식 문자열의 가장 바깥쪽 레벨을 참조하는 $ 기호로 시작해야 합니다. 하위 값은 점을 사용하여 식별할 수 있습니다. JSON 객체가 배열인 경우에는 대괄호를 사용하여 배열 색인을 지정할 수 있습니다. 키에 $, 점, 대괄호가 있는 경우 이를 이스케이프하는 방법은 각 JSON 함수를 참조하세요.

JSONPath 설명 위의 json_string_expr를 사용한 결과
$ 루트 객체 또는 요소 '$' {"class":{"students":[{"name":"Jane"}]}}
. 하위 연산자 '$.class.students' [{"name":"Jane"}]
[] 아래 첨자 연산자 '$.class.students[0]' {"name":"Jane"}

json_path 매개 변수가 json_string_expr 값과 일치하지 않으면 JSON 함수에서 NULL을 반환합니다. 스칼라 함수에 선택된 값이 스칼라가 아닌 경우(예: 객체 또는 배열) 이 함수는 NULL를 반환합니다.

JSONPath가 잘못된 경우 함수에서 오류가 발생합니다.