[빅쿼리(BigQuery)] JSON을 다루는 함수 (1)

빅쿼리에서 JSON data type 이란?

: JSON을 나타나는 데이터 타입.

 

 

JSON 타입의 값을 생성하면 정규화(canonicalization) 동작이 적용된다. 

- 간단히 말하면 BigQuery가 JSON 형식의 텍스트를 받아 내부적으로 정해진 ‘표준화된 형태’로 파싱하여 저장한다는 뜻

- 즉 사용자가 입력한 원본 문자열(공백, 숫자 표기 방식, 키 순서 등)을 문자 그대로 저장하지 않고, 의미상 동일한 값을 일관된 표현으로 바꿔서 저장한다는 의미.

 

<빅쿼리에서 JSON 타입 정규화 규칙>

• Boolean, 문자열(string), null 값은 그대로 보존된다.
• 공백 문자(whitespace)는 보존되지 않는다. => 포맷팅용 공백이 보존되지 않는다는 것! "hello world"처럼 따옴표 안에 있는 문자열 내부의 공백은 문자열의 일부이므로 정확히 보존된다. 다만 포맷팅용 공백(예: { "a" : 1 , "b" : 2 }에서의 공백)은 의미에 영향이 없으므로 보존되지 않는 것.
• JSON 값은 아래 범위의 정수를 저장할 수 있다:
  최소: -9,223,372,036,854,775,808 (signed 64비트 정수 최소값)
  최대: 18,446,744,073,709,551,615 (unsigned 64비트 정수 최대값)
• 또한 FLOAT64 범위 내의 부동소수점 숫자를 저장할 수 있다.
• 배열(array)의 요소 순서는 정확히 그대로 유지된다.
• 객체(object)의 key 순서는 보장되지 않으며 유지되지 않을 수 있다. => JSON object는 순서를 보장하지 않으므로 배열로 표현하거나 별도 필드를 사용하자.
• 객체에 중복된 key가 있을 경우, 처음 등장한 key만 보존된다.
• 최대 500단계까지 중첩(nesting) 할 수 있다.
• JSON 숫자의 원래 문자열 표현은 그대로 보존되지 않을 수 있다.

 

 

JSON에서 허용되는 데이터 유형

• string(문자열) → "abc"
• number(숫자) → 123, 3.14
• boolean(불리언) → true, false
• null → null
• object(객체) → {"a":1}
• array(배열) → [1,2,3]

 

 

[참고]

BigQuery 공식문서: JSON Data Type

https://docs.cloud.google.com/bigquery/docs/reference/standard-sql/data-types#json_type

Data types  |  BigQuery  |  Google Cloud Documentation

 

 

 

 

 

빅쿼리의 JSON 함수

JSON_VALUE

JSON_QUERY

JSON_KEYS

JSON_TYPE

TO_JSON_STRING

 

 

1. JSON_VALUE

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

- JSON 안에서 "스칼라 값(scalar value)" — 즉 숫자, 문자열, true/false, null 같은 단일 값—을 꺼내서 문자열(STRING)로 바꿔주는 함수.

- json_expr 가 객체나 배열일 경우 인덱싱으로 입력 가능

   js[k]                     -- 객체의 k 키에 해당하는 '값'을 추출
   js['state']              -- 객체의 'state' 키에 해당하는 '값'을 추출
   js[0]                     -- 배열의 첫 번째 요소를 추출

- json_path는 선택 가능(optional) 이므로 생략이 가능함.

<추가 특징>
- 값을 리턴할 때, 따옴표 제거 + 이스케이프 해제
- JSON 안에 "Jakob" 같은 값이 있으면 따옴표를 벗겨서 Jakob만 반환.
- 객체(Object)나 배열(Array) 처럼 '스칼라가 아닌 값'을 선택하면 → SQL NULL 반환.
- JSON 키 이름에 점(.)이나 특수문자가 있어도 자동으로 "로 감싸서 안전하게 처리. (예: "a.b")

 

<반환 타입>

항상 문자열(STRING)

 

SELECT JSON_VALUE('{"name": "Jakob", "age": "6"}', '$.name') AS scalar_name
     , JSON_QUERY('{"name": "Jakob", "age": "6"}', '$.name') AS json_name
     , JSON_VALUE('{"name": "Jakob", "age": "6"}', '$.age') AS scalar_age
     , JSON_QUERY('{"name": "Jakob", "age": "6"}', '$.age') AS json_age;

-- OUTPUT:
/*-------------+-----------+------------+----------*
 | scalar_name | json_name | scalar_age | json_age |
 +-------------+-----------+------------+----------+
 | Jakob       | "Jakob"   | 6          | "6"      |
 *-------------+-----------+------------+----------*/

 

 

 

2. JSON_QUERY

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

- JSON 안에서 원하는 값을 꺼내서, 그것을 SQL에서 쓸 수 있는 JSON 형식의 문자열(SQL JSON-formatted STRING) 또는 JSON 값으로 변환해주는 함수

- json_expr 가 객체나 배열일 경우 인덱싱으로 입력 가능

   js[k]                     -- 객체의 k 키에 해당하는 '값'을 추출
   js['state']              -- 객체의 'state' 키에 해당하는 '값'을 추출
   js[0]                     -- 배열의 첫 번째 요소를 추출

- json_path는 필수 입력 (예: '$.state', '$', '$.user.name')

키 이름에 특수 문자나 점(.) 같은 게 들어가면, 자동으로 큰따옴표(" ")로 감싸서 JSONPath 구문 오류를 막아줍니다.
예: "a.b"

 

 

json_string_expr: JSON 형식의 문자열 (SQL JSON-formatted string)

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

 

 

만약 JSON 안에 "null"이라는 문자열이 있으면 SQL의 NULL로 반환돼요.

SELECT JSON_QUERY("null", "$") -- 결과: SQL NULL

 

 

json_expr: JSON 값

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

 

 

JSON 안에 null 값이 있으면 그대로 JSON null을 반환해요

SELECT JSON_QUERY(JSON 'null', "$") -- 결과: JSON null

 

 

json_path

- JSON Path 표현식 (JSON에서 꺼낼 위치를 지정하는 방법)

- 예: "$"는 전체 JSON, "$.class.students[0].name"은 첫 번째 학생 이름

 

 

<반환 타입>
- json_string_expr → JSON-formatted STRING
- json_expr → JSON 값

 

<SQL의 NULL 이 나오는 경우>

1. JSON 객체에 그 키가 정의조차 안 되어 있는 경우 → 예: {}
2. 값 자체가 NULL인 경우

(JSON 객체에 키는 있는데 값이 null인 경우는 JSON NULL이다 → 예: {"duration_ms": null})

 

 

3. JSON_VALUE vs. JSON_QUERY 비교

공통점

- 둘 다 JSON에서 데이터를 꺼낼 때 사용

- JSON Path($, $.key 등) 문법 사용

- JSON 문자열('{"a":1}') 또는 JSON 타입(JSON '{"a":1}') 입력 가능

- 잘못된 경로이거나 없는 값이면 NULL 반환

 

차이점

항목	JSON_VALUE	JSON_QUERY
추출 대상	스칼라 값 (숫자, 문자열, true/false, null) 👉 단일 값 전용	스칼라가 아닌 값 (객체, 배열) 👉 JSON 덩어리 전용
반환 타입	항상 문자열(STRING)	- JSON 문자열 입력 → JSON 포맷의 STRING - JSON 입력 → JSON 타입
특징	- 반환시 따옴표 제거 & 이스케이프 해제 - 객체나 배열을 선택하면 SQL NULL 반환	- 객체/배열 그대로 반환 - 문자열 "null"이면 SQL NULL 반환 - JSON null이면 JSON null 반환
예시	sql SELECT JSON_VALUE( '{"name":"Jane"}', "$.name" ); -- 결과: Jane	sql SELECT JSON_QUERY( '{"class":{"students":[{"name":"Jane"}]}}', "$.class" ); -- 결과: {"students":[{"name":"Jane"}]}

 

JSON_VALUE → 숫자/문자/불리언 같은 단일 값 뽑기용
JSON_QUERY → 객체/배열 같은 덩어리 뽑기용

 

 

📌 빅쿼리에 직접 쿼리 작성해서 확인해보기

WITH sample AS (
  SELECT JSON '{}' AS js UNION ALL
  SELECT JSON '{"state": null}' AS js UNION ALL
  SELECT JSON '{"state": 123}' AS js UNION ALL
  SELECT JSON '{"state": "123"}' AS js UNION ALL
  SELECT JSON '{"state": {"value":123}}' AS js UNION ALL
  SELECT JSON '{"state": "granted"}' AS js UNION ALL
  SELECT JSON '{"state": ""}' AS js UNION ALL
  SELECT JSON '{"state": {"value":"granted"}}' AS js UNION ALL
  SELECT JSON '{"state": true}' AS js UNION ALL
  SELECT JSON '{"state": "true"}' AS js
)

SELECT
  js,
  ARRAY_TO_STRING(JSON_KEYS(js), ', ') AS key_result,
  JSON_VALUE(js, '$.state') AS value_result,
  JSON_QUERY(js, '$.state') AS query_result,
  JSON_TYPE(JSON_QUERY(js, '$.state'))  AS query_type_result
FROM sample

 

 

WITH sample AS (
  SELECT 1  AS num, JSON '{}' AS js UNION ALL
  SELECT 2  AS num, JSON '{"state": null}' AS js UNION ALL
  SELECT 3  AS num, JSON '{"state": 123}' AS js UNION ALL
  SELECT 4  AS num, JSON '{"state": "123"}' AS js UNION ALL
  SELECT 5  AS num, JSON '{"state": {"value":123}}' AS js UNION ALL
  SELECT 6  AS num, JSON '{"state": "granted"}' AS js UNION ALL
  SELECT 7  AS num, JSON '{"state": "denied", "tags_cnt": 8}' AS js UNION ALL
  SELECT 8  AS num, JSON '{"state": ""}' AS js UNION ALL
  SELECT 9  AS num, JSON '{"state": " "}' AS js UNION ALL
  SELECT 10  AS num, JSON '{"state": {"value":"granted"}}' AS js UNION ALL
  SELECT 11 AS num, JSON '{"state": true}' AS js UNION ALL
  SELECT 12 AS num, JSON '{"state": "true"}' AS js
), tmp AS (
  SELECT
    num,
    k AS property_key,
    -- 동적 키 접근
    JSON_VALUE(js[k])      AS property_value,
    JSON_QUERY(js[k], '$') AS property_query,
    JSON_TYPE(js[k])       AS property_type_of_value,
    CASE WHEN JSON_TYPE(js[k]) = 'null' THEN 1 ELSE 0 END AS check_json_null,
    CASE WHEN REGEXP_CONTAINS(JSON_VALUE(js[k]), r'^\s*$') THEN 1 ELSE 0 END AS check_json_empty,

  FROM sample
  LEFT JOIN UNNEST(JSON_KEYS(js)) AS k
)

SELECT *
FROM tmp
ORDER BY num

 

 

 

 

4. JSON_TYPE

JSON_TYPE(json_expr)

- JSON 값이 어떤 “자료형”인지 알려주는 함수. (조금 더 정확히는, JSON 전체에서 가장 바깥(루트)에 있는 값의 타입을 알려줌)

- 반환값: 항상 SQL STRING 타입 (즉 "string", "number", "object" 같은 글자)

 

BigQuery에서 인식하는 JSON의 최상위 타입은 아래 6가지:

1. { "key": "value", ... }  → object
2. [ 1, 2, 3 ] → array 
3. "hello" → string 
4. 10 → number
5. true | false → boolean
6. null → null

 

특징

• 인자로 받는 건 반드시 JSON 값이어야 함.
• 만약 SQL NULL을 넣으면 → 결과도 SQL NULL이 나옴.
• 잘못된 JSON이면 → 에러 발생.
• 반환되는 건 JSON 자체가 아니라, 그 타입 이름이 문자열(SQL STRING)로 나오는 것임.

 

 

[참고]

BigQuery 공식문서: JSON Function

https://cloud.google.com/bigquery/docs/reference/standard-sql/json_functions

JSON functions  |  BigQuery  |  Google Cloud Documentation