JSON 함수

JSON 함수 (JSON Functions)

Apache Pinot의 JSON 함수는 JSON 문서에서 값을 추출·변환·조회하는 다양한 기능을 제공해요. 변환 함수와 스칼라 함수로 나뉘어요.

출처: 문서

본문

변환 함수 (Transform Functions)

이 함수들은 Pinot SQL 쿼리에서만 사용할 수 있어요.

스칼라 함수 (Scalar Functions)

이 함수들은 테이블 수집 설정에서 컬럼 변환에 사용할 수 있어요.

추가 참고 페이지 (Additional Reference Pages)

더 많은 예시 (More Examples)

JSON 데이터를 어떻게 쿼리하는지 보려면, 다음 테이블이 있다고 가정해요:

Table myTable:
  id        INTEGER
  jsoncolumn    JSON 

Table data:
101,{"name":{"first":"daffy"\, "last":"duck"}\,"score":101\,"data":["a"\, "b"\, "c"\, "d"]}
102,{"name":{"first":"donald"\, "last":"duck"}\,"score":102\,"data":["a"\, "b"\, "e"\, "f"]}
103,{"name":{"first":"mickey"\, "last":"mouse"}\,"score":103\,"data":["a"\, "b"\, "g"\, "h"]}
104,{"name":{"first":"minnie"\, "last":"mouse"}\,"score":104\,"data":["a"\, "b"\, "i"\, "j"]}
105,{"name":{"first":"goofy"\, "last":"dwag"}\,"score":104\,"data":["a"\, "b"\, "i"\, "j"]}
106,{"person":{"name":"daffy duck"\, "companies":[{"name":"n1"\, "title":"t1"}\, {"name":"n2"\, "title":"t2"}]}}
107,{"person":{"name":"scrooge mcduck"\, "companies":[{"name":"n1"\, "title":"t1"}\, {"name":"n2"\, "title":"t2"}]}}

또한 "jsoncolumn"에 Json Index가 있다고 가정해요. 테이블의 마지막 두 행은 나머지 행과 구조가 다르다는 점에 주의하세요. JSON 명세에 따라 JSON 컬럼은 유효한 JSON 데이터를 포함할 수 있으며 미리 정의된 스키마를 따를 필요가 없어요. 각 행의 전체 JSON 문서를 가져오려면 아래 쿼리를 실행할 수 있어요:

SELECT id, jsoncolumn 
  FROM myTable

JSON 컬럼 안의 특정 키를 파고들어 추출하려면, 해당 키의 JsonPath 표현식을 컬럼 이름 끝에 추가하면 돼요.

SELECT id,
       json_extract_scalar(jsoncolumn, '$.name.last', 'STRING', 'null') last_name,
       json_extract_scalar(jsoncolumn, '$.name.first', 'STRING', 'null') first_name
       json_extract_scalar(jsoncolumn, '$.data[1]', 'STRING', 'null') value
  FROM myTable

세 번째 컬럼(value)은 id 106과 107의 행에 대해 null이라는 점에 주의하세요. 이는 이 행들의 JSON 문서에 JsonPath $.data\[1]인 키가 없기 때문이에요. 이 행들을 필터링할 수 있어요.

SELECT id,
       json_extract_scalar(jsoncolumn, '$.name.last', 'STRING', 'null') last_name,
       json_extract_scalar(jsoncolumn, '$.name.first', 'STRING', 'null') first_name,
       json_extract_scalar(jsoncolumn, '$.data[1]', 'STRING', 'null') value
  FROM myTable
 WHERE JSON_MATCH(jsoncolumn, '"$.data[1]" IS NOT NULL')

위 데이터에는 특정 성(예: duck과 mouse)이 반복돼요. JsonPath 표현식에 GROUP BY 쿼리를 실행해 각 성의 개수를 구할 수 있어요.

  SELECT json_extract_scalar(jsoncolumn, '$.name.last', 'STRING', 'null') last_name,
         count(*)
    FROM myTable
   WHERE JSON_MATCH(jsoncolumn, '"$.data[1]" IS NOT NULL')
GROUP BY json_extract_scalar(jsoncolumn, '$.name.last', 'STRING', 'null')
ORDER BY 2 DESC

또한 JSON 문서 안에는 숫자 정보(jsconcolumn.$.id)가 포함돼 있어요. 아래 쿼리를 사용해 JSON 데이터에서 이러한 숫자 값을 SQL로 추출하고 합산할 수 있어요.

  SELECT json_extract_scalar(jsoncolumn, '$.name.last', 'STRING', 'null') last_name,
         sum(json_extract_scalar(jsoncolumn, '$.id', 'INT', 0)) total
    FROM myTable
   WHERE JSON_MATCH(jsoncolumn, '"$.name.last" IS NOT NULL')
GROUP BY json_extract_scalar(jsoncolumn, '$.name.last', 'STRING', 'null')

JSON_MATCH와 JSON_EXTRACT_SCALAR

JSON_MATCH 함수는 JsonIndex를 활용하며, JSON 컬럼에 JsonIndex가 이미 존재하는 경우에만 사용할 수 있어요. 위 예시에서 보듯이 JSON_MATCH 연산자의 두 번째 인자는 조건문(predicate)을 받아요. 이 조건문은 JsonIndex에 대해 평가되며 =, !=, IS NULL, IS NOT NULL, IN과 >, <, >=, <=, BETWEEN 같은 관계 연산자를 지원해요.

JSON_MATCH 함수는 전체 JsonPath 표현식을 지원하지 않지만 와일드카드 * JsonPath 표현식은 사용할 수 있어요.

  SELECT json_extract_scalar(jsoncolumn, '$.name.last', 'STRING', 'null') last_name,
         json_extract_scalar(jsoncolumn, '$.id', 'INT', 0) total
    FROM myTable
   WHERE JSON_MATCH(jsoncolumn, '"$.data[*]" = ''f''')
GROUP BY json_extract_scalar(jsoncolumn, '$.name.last', 'STRING', 'null')

JSON_MATCH는 IS NULL과 IS NOT NULL 연산자를 지원하지만, 이 연산자는 리프 레벨 경로 요소에만 적용되어야 해요. 즉 조건 JSON_MATCH(jsoncolumn, '"$.data[*]" IS NOT NULL')는 "$.data[*]"가 경로의 "리프" 요소를 가리키지 않으므로 유효하지 않아요. 반면 "$.data[0]" IS NOT NULL'는 "$.data[0]"가 경로의 리프 요소를 명확히 식별하므로 유효해요.

JSON_EXTRACT_SCALAR는 JsonIndex를 활용하지 않으므로, JsonIndex를 사용하는 JSON_MATCH보다 느려요. 그러나 JSON_EXTRACT_SCALAR는 더 넓은 범위의 JsonPath 표현식과 연산자를 지원해요. 빠른 인덱스 접근(JSON_MATCH)과 JsonPath 표현식(JSON_EXTRACT_SCALAR)을 모두 최대한 활용하려면 WHERE 절에서 이 두 함수를 결합해 사용할 수 있어요.

JSON_MATCH 문법 (JSON_MATCH syntax)

JSON_MATCH 함수의 두 번째 인자는 문자열 형태의 불리언 표현식이에요. 이 섹션은 JSON_MATCH의 두 번째 인자를 올바르게 작성하는 방법을 보여줘요. JSON 배열 data에서 값 k와 j를 검색한다고 가정해 봐요. 이는 다음 조건문으로 할 수 있어요:

data[0] IN ('k', 'j')

이 조건문을 JSON_MATCH에서 사용할 문자열 형태로 변환하려면, 먼저 조건의 왼쪽을 큰따옴표로 묶어 식별자로 바꿔요:

"data[0]" IN ('k', 'j')

다음으로, 조건의 리터럴도 '로 묶어야 해요. 기존의 '도 이스케이프해야 해요. 이렇게 하면:

"data[0]" IN (''k'', ''j'')

마지막으로 위 전체 표현식을 '로 묶어 문자열로 만들어야 해요:

'"data[0]" IN (''k'', ''j'')'

이제 원래 조건의 문자열 표현을 얻었고, 이를 JSON_MATCH 함수에 사용할 수 있어요:

   WHERE JSON_MATCH(jsoncolumn, '"data[0]" IN (''k'', ''j'')')

더 많은 JSON_MATCH 예시는 JSON Index를 참고하세요.

더 알아보기 (Learn more)

• JSONEXTRACTSCALAR
• JSONPATHSTRING
• JSONPATHLONG
• JSONPATHEXISTS