BigQuery Guide Book - 빅쿼리 가이드북

# JSON 함수

JSON은 Key Value로 이루어진 데이터를 전달하기 위한 표준 포맷입니다. JavaScript Object Notation의 약자입니다.

아래와 같은 형태의 데이터입니다

{
  "id": "zzsza", 
  "name": "변성윤", 
  "blog": "zzsza.github.io",
  "contacts": null
 }

# 표준 JSON 추출 함수

과거엔 존재하지 않았으나, 최근엔 이 표준 JSON 추출 함수를 권장하고 있습니다. 큰 따옴표를 사용해 JSON 문자를 이스케이프 처리합니다.

  • JSON_QUERY : JSON 형태의 문자열 추출
  • JSON_VALUE : 스칼라 값 추출
  • JSON_QUERY_ARRAY : JSON 형태의 문자열의 배열 추출
  • JSON_VALUE_ARRAY : 스칼라 값의 배열 추출

# JSON_QUERY

JSON 형태의 문자열 추출하는 함수로, 배열 같은 비스칼라 값을 표현하려면 해당 함수를 사용해야 합니다.

  • $ : 루트 객체, JSON 하나의 데이터
  • . : 하위 속성
  • [0] : 배열의 첫번째 요소. 혹은 .0으로 사용해도 동작함
SELECT 
  JSON_QUERY(json_text, "$.class.students") AS student_names,
  JSON_QUERY(json_text, "$.class.students[0]") AS student_first_name,
  JSON_QUERY(json_text, "$.class.students.0") AS student_first_name2,
  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": "Jamie"}]}}'
  ]) AS json_text

# JSON_VALUE

스칼라 값을 추출해 문자열로 반환하는 함수입니다. 배열은 추출할 수 없습니다.

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_QUERY만 추출할 수 있습니다.



# 기존 JSON 추출 함수

작은 따옴표와 대괄호를 사용해 JSON 문자를 이스케이프 처리합니다.

  • JSON_EXTRACT : JSON_QUERY와 유사
  • JSON_EXTRACT_SCALAR : JSON_VALUE와 유사
  • JSON_EXTRACT_ARRAY : JSON_QUERY_ARRAY와 유사
  • JSON_EXTRACT_STRING_ARRAY : JSON_VALUE_ARRAY와 유사


# 기타 JSON 함수

  • TO_JSON_STRING(value, prettry_print)
    • 테이블의 Row를 JSON 형식으로 변환합니다
WITH base 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(base, true) AS json_data,
  TO_JSON_STRING(base, false) AS json_data2
FROM base

← 스크립팅 →