[빅쿼리(BigQuery)] JSON을 다루는 함수(2) TO_JSON_STRING 함수 (모든 컬럼 값이 100% 중복인 행 찾는 방법)

 

TO_JSON_STRING

SQL 값(STRUCT, ARRAY 등)을 JSON 형식의 문자열로 변환해주는 표준 SQL 함수로, 레코드(구조체)나 배열 같은 복잡한 데이터를 JSON 형식으로 만들 때 사용 (※ 주의: 리턴 값의 데이터 타입은 문자열(string)이다. 문자열이 JSON 포맷을 하고 있는 것일뿐 데이터 타입이 JSON인 것은 아님. 만약 데이터 타입도 JSON으로 변환하고 싶다면 TO_JSON() 함수를 사용해야 한다.) 

 

 

예시

아래와 같은 테이블이 있다고 하자. 이 테이블의 각 행을 JSON 포맷으로 표현하여 한 줄로 나타낼 수 있다. => 행 전체 직렬화

 

 

WITH e AS (
  SELECT 'user_001' AS user_id, 'search' AS event_name, 10 AS cnt, JSON '{"query": "iPhone", "results_cnt": 5, "has_result": true}' AS event_properties
  UNION ALL
  SELECT 'user_002', 'click', 5, JSON '{"page": "home", "button_id": "login", "position": 3}'
  UNION ALL
  SELECT 'user_002', 'click', 5, JSON '{"page": "home", "button_id": "login", "position": 3}'
)

SELECT user_id
     , event_name
     , cnt
     , event_properties
     , TO_JSON_STRING(e) AS row_as_json
FROM e

 

OUTPUT:

 

 

 

 

모든 컬럼 값이 100% 중복인 행 찾는 방법

WITH e AS (
  SELECT 'user_001' AS user_id, 'search' AS event_name, 10 AS cnt, JSON '{"query": "iPhone", "results_cnt": 5, "has_result": true}' AS event_properties
  UNION ALL
  SELECT 'user_002', 'click', 5, JSON '{"page": "home", "button_id": "login", "position": 3}'
  UNION ALL
  SELECT 'user_002', 'click', 5, JSON '{"page": "home", "button_id": "login", "position": 3}'
)
, tmp AS (
  SELECT user_id
       , event_name
       , cnt
       , event_properties
       , TO_JSON_STRING(e) AS row_as_json
  FROM e
)

SELECT COUNT(*) AS cnt_rows
     , COUNT(DISTINCT row_as_json) AS cnt_rows_distinct
FROM tmp

 

위 예시의 결과를 CTE로 만든 후, 전체 행 개수와 행 전체를 직렬화한 내용의 고유 값 개수를 세서 비교하면 된다.

전체 행은 3개지만 모든 내용이 동일한 중복 행이 존재하므로 고유 값 개수는 2개다. 

 

OUTPUT:

 

 

 

 

 

[참고] BigQuery 공식문서

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

JSON functions  |  BigQuery  |  Google Cloud Documentation