JSON 함수
JSON 함수 (JSON Functions)
ClickHouse에서는 JSON 데이터를 저장하기 위한 특별한 타입이 두 가지 있어요. 첫 번째는 JSON 타입으로, JSON 컬럼으로 저장하는 경우예요. 두 번째는 JSON 문자열이 일반적인 String 타입으로 저장·표현되는 경우예요.
출처: 문서
본문
JSON 타입에 대한 개요와 세부 정보는 [JSON 데이터 타입](/docs/...처리 방식과 마찬가지로 String으로 저장된 JSON에서 값을 추출하는 함수들이에요.
JSON 문서에서 값 추출 (Extracting values from a JSON)
이 함수들의 일반적인 사용법은 JSON에서 값을 추출하는 거예요. 가장 간단한 형태는 이런 모양이에요:
SELECT JSONExtractInt('{"key":1294}', 'key');
┌─JSONExtractInt('{"key":1294}', 'key')─┐
│ 1294 │
└─────────────────────────────────────────┘
첫 번째 인자는 JSON 문자열이고, 두 번째 인자는 추출할 키예요. 값을 추출할 수 없는 경우 0, "", 또는 [](어떤 데이터 타입이냐에 따라) 또는 NULL이 반환돼요. 반환 타입은 함수명의 접미사에 따라 결정돼요(예: Int64의 경우 JSONExtractInt, Float64의 경우 JSONExtractFloat 등).
중첩된 구조를 파싱하기 위해 키를 점(dot)으로 구분해 사용할 수 있어요:
SELECT JSONExtractInt('{"key":{"nestedKey":42}}', 'key.nestedKey');
┌─JSONExtractInt('{"key":{"nestedKey":42}}', 'key.nestedKey')─┐
│ 42 │
└──────────────────────────────────────────────────────────┘
키가 숫자로 시작하거나 점을 포함하는 등 특수한 경우에는 (점으로 구분한) 순서 목록에 있는 인덱스를 대신 사용할 수도 있어요:
SELECT JSONExtractInt('{"1key":{"nested.key":42}}', ['1key','nested.key']);
┌─JSONExtractInt('{"1key":{"nested.key":42}}', ['1key','nested.key'])─┐
│ 42 │
└──────────────────────────────────────────────────────────────────────┘
기본 구분자가 아닌 다른 구분자를 사용하려면 JSONExtractInt('{...}', 'two.fields', 'two,fields')처럼 세 번째 문자열 인자로 구분자를 지정할 수 있어요.
JSONExtract(doc, [path], return_type) — JSON 숫자·문자열·불리언을 저장된 문자열에서 파싱하고 반환 타입으로 변환해요. 같은 JSON 문자열에서 여러 값을 추출하려면 네 번째 인자로 return_type을 지정할 수도 있어요. 이 경우 콜론으로 구분된 반환 타입 목록을 전달하는데, 똑같이 /docs/reference/functions/type-conversion-functions에 설명된 모든 가능한 데이터 타입이 지원돼요. 예시:
SELECT JSONExtract('{"a": 12, "b": "0"}', 'a', 'Int8', 'b', 'Nullable(UInt16)') AS ab;
┌─ab──────────────┐
│ (12, '0') │
└─────────────────┘
또한 네 번째 인자로 누락된 값에 NULL을 반환할지 지정할 수 있어요. return_type에 NULL이라는 단어를 포함하면 누락된 값에 NULL을 반환해요, 예: 'Nullable(UInt16)'.
JSONExtractBool(doc, [path]) — JSON에서 불리언 값을 파싱하고 Bool로 반환해요.
JSONExtractInt — JSON에서 정수를 파싱하고 Int로 반환해요.
JSONExtractFloat — JSON에서 부동소수점을 파싱하고 Float64로 반환해요.
JSONExtractUInt — JSON에서 부호 없는 정수를 파싱하고 UInt로 반환해요.
JSONExtractString — JSON에서 문자열을 파싱하고 String로 반환해요. 파싱할 수 없으면 ""이 반환돼요.
JSONExtractArrayRaw(doc, [path]) — JSON에서 배열을 파싱하고 원본 JSON의 하위 문자열 배열(Array(String))로 반환해요. 파싱할 수 없으면 []이 반환돼요.
JSONExtractKeysAndValues(doc, [path], keys_value_type) — JSON에서 키-값 쌍을 파싱하고 Map(key_type, value_type)으로 반환해요. 파싱할 수 없으면 빈 맵이 반환돼요.
JSONExtractKeysAndValuesRaw(doc, [path]) — JSON에서 키-값 쌍을 파싱하고 원본 JSON의 하위 문자열 키-값 쌍(Map(String, String))으로 반환해요. 파싱할 수 없으면 빈 맵이 반환돼요.
JSONExtractRaw(doc, [path]) — JSON에서 값을 파싱하고 원본 JSON의 하위 문자열(String)로 반환해요. 파싱할 수 없으면 ""이 반환돼요.
JSONExtractKeysAndValues와 동일하지만 특정 반환 타입 없이 원본 형태를 유지해요.
JSONHas(doc, [path]) — JSON에 특정 키가 있는지 확인해요. 참이면 1, 거짓이면 0을 반환해요.
JSONLength — JSON 문서의 길이를 반환해요. 배열이면 요소 수, 객체면 키 수, null이면 0, 다른 값이면 1이에요. 잘못된 경로면 0이 반환돼요.
JSONType(doc, [path]) — JSON 값의 타입을 반환해요. 값이 Array면 'Array', Object면 'Object', String이면 'String', null이면 'Null', 부동소수점이면 'Float64', UInt면 'UInt64', Int면 'Int64', 불리언이면 'Bool'이에요. 경로가 잘못되거나 잘못된 JSON이면 빈 문자열 ''을 반환해요.
JSONExists — JSON에 특정 키가 있는지 확인해요. 있으면 1, 없으면 0을 반환해요. 경로가 없으면 NULL을 반환해요.
JSONValue — JSON 값을 반환하되, 반환 타입에 맞게 변환해요. JSONExtract와 비슷하지만 함수명의 접미사 없이 return_type 인자를 받아요.
JSONQuery — JSON 값을 반환하되, 결과에서 따옴표·중괄호 등 구분 문자를 그대로 보존해요. JSONExtractRaw와 비슷하지만 return_type 인자를 받는다는 점이 달라요.
문자열이 아닌 저장 형태(예: JSON 타입)를 다루는 함수는 [JSON 타입 함수](/docs/...에서 확인할 수 있어요.
JSON 문자열 생성 (Generating a JSON string)
오브젝트·배열·스를 바탕으로 JSON 문자열을 만들 수 있어요.
JSON_ARRAY(values...) — 값들을 콤마로 구분해 JSON 배열 문자열을 생성해요. 문자열 값은 따옴표로 감싸지고, 숫자·불리언·null은 그대로 들어가요.
JSON_OBJECT(keys and values...) — 키-값 쌍을 받아 JSON 오브젝트 문자열을 생성해요. 키가 문자열이어야 하며, 값은 어떤 타입도 가능해요.
JSON_OBJECT_IMPLICIT(values...) — 값을 나열하면 "key": value 형태의 JSON 오브젝트를 생성해요. 짝이 맞지 않으면 오류가 나요.
ClickHouse는 JSON 문자열을 다루는 기본 함수 외에도 JSON 관련 고급 함수들을 제공해요. 이 함수들은 JSON 경로 식(JSONPath), 배열 집계, 키-값 추출 등을 다루며, 기존 json 상위 디렉터리의 함수들과 관련이 있어요.
JSONPath 사용법 (JSONPath)
json 확장 함수들은 문자열이나 SQL 경로 대신 JSONPath 식을 받아들일 수 있어요. 예:
SELECT * FROM t WHERE j @> '$.a.b';
$.a.b 형태의 JSONPath 식으로 값을 추출·확인·타입 확인할 수 있어요.
jsonExists — SQL 숫자·문자열 경로를 지원하며, 존재 여부를 확인해요.
jsonValue — SQL 숫자·문자열 경로와 함께 JSONPath를 지원하며, 변환된 값을 반환해요.
jsonQuery — SQL 숫자·문자열 경로와 함께 JSONPath를 지원하며, 원본 하위 문자열을 반환해요.
jsonExtract — SQL 숫자·문자열 경로와 함께 JSONPath를 지원하며, 지정한 타입으로 변환해요.
jsonLength — SQL 숫자·문자열 경로와 함께 JSONPath를 지원하며, 길이를 반환해요.
jsonType — SQL 숫자·문자열 경로와 함께 JSONPath를 지원하며, 타입 문자열을 반환해요.
jsonArrayLength — JSON 배열의 길이를 반환하고, T 인자는 배열로 반환할 키들을 지정해요.
jsonObjectKeys — JSON 오브젝트의 키 목록을 반환해요. KEY_PATH는 추출할 키, RETURN_TYPE은 반환할 키 타입이에요.
jsonArrayElements — JSON 배열의 요소를 각 행으로 반환해요. ARRAY_PATH는 배열 경로, INDEX_COLUMN_NAME은 인덱스를 담을 컬럼 이름이에요.