Mysql에서 Json Extract & group by & CAST로 스마트 컨트렉트 이벤트 처리하기

상황 : 트랜잭션 데이터 중 이벤트 로그 같은 것들은 json 형태로 조회된다. 이런 자료형들을 DB에서 접근해와서 api를 제작해야 하는 상황

 

1. json_extract 사용하기

먼저 mysql에서 JSON 형태로 된 자료형의 특정 요소를 뽑아서 쿼리를 하려면 json_extract 를 사용하면 된다.

json_extract(event_data,'$.KEY') as borrower

만약 블록체인 이벤트를 조회하는 event_data가 DB에 저장 되어 있다고 하면

이와 같은 문구에서 KEY에 JSON의 키 값을 넣어주면 된다.

 

2. grop by 사용하기

여러 데이터들을 합산하기 위해서는 grop by를 사용하면 된다.

다만 이때 select 문에서 선택되는 값들은 합산을 하지 않는 값들을 모두 grop by 에 넣어주어야 한다.

SELECT json_extract(event_data,'$.KEY1') as A, json_extract(event_data,'$.KEY2') as B, sum(json_extract(event_data, '$.KEY3')) as C 
FROM 데이터베이스이름.테이블이름 where 조건 group by A, B;

이런식으로 나오게 되면 각각 key값 1, 2 번을 통해 조건을 걸고 해당 조건에 만족하는 C를 합산해서 그룹해버리는 것이다.

이때 A, B 2개의 값이 동일하면 합산이 되고 아니라면 합산이 되지 않는다.

 

3. db에서의 exponential 해결

토큰과 관련된 사항일 경우 당연히 숫자가 커져서 exponential 형태로 숫자가 표시되게 된다.

이에 따라서 데이터베이스에서도 bignumber 처리처럼 자료형을 변환해서 계산해 주어야 한다.

CAST(json_extract(data,'$.KEY') AS FLOAT) / CAST(POW(10, 18) AS FLOAT)

이런식으로 CAST를 써서 자료형을 float으로 맞춰 준 후에 나누어 주면 우리가 원하는 소수점 처리된 결과가 나오게 된다.

 

2안은 데이터를 가져와서 백엔드에서 bigInt 처리를 해주어서 계산을 하고 그 결과값을 가져오는 것이다.

 

하지만 리소스의 절약을 위해서는 쿼리문으로 최대한 처리를 해서 백엔드로 던져주는 것이 좋기 때문에 exponential까지 해결하고 리턴하는 것이 더 좋다.