JSON 처리 함수

JSON 처리 함수 (JSON Processing Functions)

DuckDB는 저장된 JSON 값에서 원하는 부분을 뽑아내고, 구조를 살펴보고, 집계하고, 중첩 타입으로 변환하는 다양한 JSON 함수를 제공합니다. 추출 함수는 연산자 표기(->, ->>)도 지원해서 SQL을 간결하게 쓸 수 있어요. 위치를 지정할 때는 JSON Pointer(/duck/0)와 JSONPath($.duck[0]) 두 가지 표기법을 모두 쓸 수 있습니다.

출처: 공식문서

JSON 추출 함수 (JSON Extraction Functions)

추출 함수는 두 가지가 있고, 각각 대응하는 연산자가 있습니다. 연산자는 문자열이 JSON 논리 타입으로 저장되어 있을 때만 사용할 수 있어요. 두 함수 모두 JSON 스칼라 함수와 동일한 두 가지 위치 표기법을 지원합니다.

함수 별칭 연산자 설명
json_exists(json, path) 주어진 path가 json에 존재하면 true, 아니면 false 반환
json_extract(json, path) json_extract_path -> 주어진 path의 json을 추출. path가 LIST면 결과도 JSON의 LIST
json_extract_string(json, path) json_extract_path_text ->> 주어진 path에서 VARCHAR 추출. path가 LIST면 결과도 VARCHAR의 LIST
json_value(json, path) 주어진 path의 json을 추출. path의 값이 스칼라가 아니면 NULL 반환

JSON 추출에 쓰는 화살표 연산자 ->는 람다 함수에서도 쓰이기 때문에 우선순위가 낮다는 점을 주의하세요. 등식 비교(=) 같은 연산과 함께 쓸 때는 -> 연산자를 괄호로 감싸야 합니다.

SELECT ((JSON '{"field": 42}')->'field') = 42;

낮은 우선순위는 WHERE 절에서 추출을 다른 조건과 결합할 때도 적용됩니다. AND와 OR는 화살표 연산자보다 더 강하게 결합하므로, 추출 앞의 조건이 화살표 연산자의 왼쪽 피연산자로 흡수되면서 우선순위를 언급하지 않는 오류 메시지와 함께 쿼리가 실패해요. 예를 들면:

SELECT count(*) FROM (VALUES ('{}'::JSON)) t(j)
WHERE 1 = 1 AND j->>'field' IS NOT NULL;

Conversion Error:
Failed to cast value to numerical: {} when casting from source column j

여기서 조건은 ((1 = 1 AND j)->>'field') IS NOT NULL로 파싱됩니다. 추출을 괄호로 감싸거나((j->>'field') IS NOT NULL), 연산자 대신 동등한 함수를 쓰면(json_extract_string(j, 'field') IS NOT NULL) 문제를 피할 수 있어요. DuckDB v2.0.0 이상에서는 화살표 연산자가 더 강하게 결합하므로 이 예시는 괄호 없이도 동작합니다.

⚠️ DuckDB의 JSON 데이터 타입은 0-based 인덱싱을 사용합니다.

예제를 볼게요.

CREATE TABLE example (j JSON);
INSERT INTO example VALUES
    ('{ "family": "anatidae", "species": [ "duck", "goose", "swan", null ] }');

SELECT json_extract(j, '$.family') FROM example;

"anatidae"

SELECT j->'$.family' FROM example;

"anatidae"

SELECT j->'$.species[0]' FROM example;

"duck"

SELECT j->'$.species[*]' FROM example;

["duck", "goose", "swan", null]

SELECT j->>'$.species[*]' FROM example;

[duck, goose, swan, null]

SELECT j->'$.species'->0 FROM example;

"duck"

SELECT j->'species'->['/0', '/1'] FROM example;

['"duck"', '"goose"']

SELECT json_extract_string(j, '$.family') FROM example;

anatidae

SELECT j->>'$.family' FROM example;

anatidae

SELECT j->>'$.species[0]' FROM example;

duck

SELECT j->'species'->>0 FROM example;

duck

SELECT j->'species'->>['/0', '/1'] FROM example;

[duck, goose]

DuckDB의 JSON 데이터 타입은 0-based 인덱싱을 사용한다는 점을 기억하세요.

같은 JSON에서 여러 값을 뽑아야 한다면 경로의 리스트를 추출하는 편이 더 효율적입니다. 다음 쿼리는 JSON을 두 번 파싱하게 되어, 더 느리고 메모리도 더 씁니다:

SELECT
    json_extract(j, 'family') AS family,
    json_extract(j, 'species') AS species
FROM example;
family species
"anatidae" ["duck","goose","swan",null]

다음 쿼리는 같은 결과를 내지만 더 빠르고 메모리 효율적이에요:

WITH extracted AS (
    SELECT json_extract(j, ['family', 'species']) AS extracted_list
    FROM example
)
SELECT
    extracted_list[1] AS family,
    extracted_list[2] AS species
FROM extracted;

JSON 스칼라 함수 (JSON Scalar Functions)

다음 스칼라 JSON 함수들로 저장된 JSON 값에 대한 정보를 얻을 수 있습니다. json_valid(json)을 제외한 모든 JSON 함수는 잘못된 JSON이 들어오면 오류를 냅니다. JSON 안에서 위치를 표현하는 표기법은 JSON Pointer와 JSONPath 두 가지를 지원해요.

함수 설명
json_array_length(json[, path]) JSON 배열 json의 원소 개수를 반환. 배열이 아니면 0. path가 지정되면 그 경로의 배열 길이, path가 LIST면 길이의 LIST를 반환
json_contains(json_haystack, json_needle) json_needle이 json_haystack에 포함되면 true. 두 파라미터 모두 JSON 타입이나, json_needle은 숫자나 문자열일 수도 있음(문자열은 큰따옴표로 감싸야 함)
json_keys(json[, path]) json이 JSON 객체면 키를 VARCHAR의 LIST로 반환. path가 지정되면 그 경로 객체의 키, path가 LIST면 LIST of LIST of VARCHAR
json_structure(json) json의 구조를 반환. 구조가 일관되지 않으면 기본값 JSON (예: 배열 안 타입 불일치)
json_type(json[, path]) json의 타입을 반환: ARRAY, BIGINT, BOOLEAN, DOUBLE, OBJECT, UBIGINT, VARCHAR, NULL 중 하나. path가 지정되면 그 경로 요소의 타입, path가 LIST면 타입의 LIST
json_valid(json) json이 유효한 JSON인지 반환
json(json) json을 파싱하고 압축(minify)

JSONPointer 문법은 각 필드를 /로 구분합니다. 키가 duck인 배열의 첫 원소를 뽑으려면:

SELECT json_extract('{"duck": [1, 2, 3]}', '/duck/0');

1

JSONPath 문법은 필드를 .로 구분하고 배열 원소는 [i]로 접근하며, 항상 $로 시작합니다. 같은 예시를 JSONPath로:

SELECT json_extract('{"duck": [1, 2, 3]}', '$.duck[0]');

1

DuckDB의 JSON 데이터 타입은 0-based 인덱싱을 사용합니다. JSONPath는 더 표현력이 뛰어나서 리스트의 뒤쪽에도 접근할 수 있어요:

SELECT json_extract('{"duck": [1, 2, 3]}', '$.duck[#-1]');

3

JSONPath는 큰따옴표로 문법 토큰을 이스케이프할 수도 있습니다:

SELECT json_extract('{"duck.goose": [1, 2, 3]}', '$."duck.goose"[1]');

2

anatidae(오리과) 생물학적 분류를 활용한 예제입니다:

CREATE TABLE example (j JSON);
INSERT INTO example VALUES
    ('{ "family": "anatidae", "species": [ "duck", "goose", "swan", null ] }');

SELECT json(j) FROM example;

{"family":"anatidae","species":["duck","goose","swan",null]}

SELECT j.family FROM example;

"anatidae"

SELECT j.species[0] FROM example;

"duck"

SELECT json_valid(j) FROM example;

true

SELECT json_valid('{');

false

SELECT json_array_length('["duck", "goose", "swan", null]');

4

SELECT json_array_length(j, 'species') FROM example;

4

SELECT json_array_length(j, '/species') FROM example;

4

SELECT json_array_length(j, '$.species') FROM example;

4

SELECT json_array_length(j, ['$.species']) FROM example;

[4]

SELECT json_type(j) FROM example;

OBJECT

SELECT json_keys(j) FROM example;

[family, species]

SELECT json_structure(j) FROM example;

{"family":"VARCHAR","species":["VARCHAR"]}

SELECT json_structure('["duck", {"family": "anatidae"}]');

["JSON"]

SELECT json_contains('{"key": "value"}', '"value"');

true

SELECT json_contains('{"key": 1}', '1');

true

SELECT json_contains('{"top_key": {"key": "value"}}', '{"key": "value"}');

true

JSON 집계 함수 (JSON Aggregate Functions)

JSON 집계 함수는 세 가지가 있습니다.

함수 설명
json_group_array(any) 집계 안의 모든 any 값을 가진 JSON 배열 반환
json_group_object(key, value) 집계 안의 모든 key, value 쌍을 가진 JSON 객체 반환
json_group_structure(json) 집계 안의 모든 json의 결합된 json_structure 반환

예제:

CREATE TABLE example1 (k VARCHAR, v INTEGER);
INSERT INTO example1 VALUES ('duck', 42), ('goose', 7);

SELECT json_group_array(v) FROM example1;

[42, 7]

SELECT json_group_object(k, v) FROM example1;

{"duck":42,"goose":7}

CREATE TABLE example2 (j JSON);
INSERT INTO example2 VALUES
    ('{"family": "anatidae", "species": ["duck", "goose"], "coolness": 42.42}'),
    ('{"family": "canidae", "species": ["labrador", "bulldog"], "hair": true}');

SELECT json_group_structure(j) FROM example2;

{"family":"VARCHAR","species":["VARCHAR"],"coolness":"DOUBLE","hair":"BOOLEAN"}

JSON을 중첩 타입으로 변환 (Transforming JSON to Nested Types)

대부분의 경우 JSON에서 값을 하나씩 뽑는 건 비효율적입니다. 대신 모든 값을 한 번에 "추출"해서 JSON을 LIST와 STRUCT 중첩 타입으로 변환할 수 있어요.

함수 설명
json_transform(json, structure) 지정한 structure에 따라 json 변환
from_json(json, structure) json_transform의 별칭
json_transform_strict(json, structure) json_transform과 동일하나 타입 캐스팅 실패 시 오류 발생
from_json_strict(json, structure) json_transform_strict의 별칭

structure 인자는 json_structure가 반환하는 것과 같은 형태의 JSON입니다. 이 구조를 수정하면 JSON을 원하는 구조와 타입으로 변환할 수 있어요. JSON 안에 있는 것보다 적은 키/값 쌍을 추출할 수도 있고, 더 많이 추출할 수도 있습니다 — 누락된 키는 NULL이 돼요.

예제:

CREATE TABLE example (j JSON);
INSERT INTO example VALUES
    ('{"family": "anatidae", "species": ["duck", "goose"], "coolness": 42.42}'),
    ('{"family": "canidae", "species": ["labrador", "bulldog"], "hair": true}');

SELECT json_transform(j, '{"family": "VARCHAR", "coolness": "DOUBLE"}') FROM example;

{'family': anatidae, 'coolness': 42.420000}
{'family': canidae, 'coolness': NULL}

SELECT json_transform(j, '{"family": "TINYINT", "coolness": "DECIMAL(4, 2)"}') FROM example;

{'family': NULL, 'coolness': 42.42}
{'family': NULL, 'coolness': NULL}

SELECT json_transform_strict(j, '{"family": "TINYINT", "coolness": "DOUBLE"}') FROM example;

Invalid Input Error:
Failed to cast value: "anatidae"

JSON 테이블 함수 (JSON Table Functions)

DuckDB는 JSON 값을 받아 테이블로 만들어 주는 두 가지 JSON 테이블 함수를 구현합니다.

함수 설명
json_each(json[, path]) json을 순회하며 최상위 배열이나 객체의 각 원소마다 한 행씩 반환
json_tree(json[, path]) json을 깊이 우선(depth-first)으로 순회하며 구조의 각 원소마다 한 행씩 반환

원소가 배열이나 객체가 아니면 그 원소 자체가 반환됩니다. 선택적 path 인자를 주면 루트 대신 해당 경로의 원소에서 순회를 시작해요. 결과 테이블의 컬럼은 다음과 같습니다.

필드 타입 설명
key VARCHAR 부모에 상대적인 원소 키
value JSON 원소 값
type VARCHAR 이 원소의 json_type (함수)
atom JSON 이 원소의 json_value (함수)
id UBIGINT 파싱 순서대로 매겨진 원소 식별자
parent UBIGINT 부모 원소의 id
fullkey VARCHAR 원소까지의 JSON 경로
path VARCHAR 부모 원소까지의 JSON 경로
json JSON (Virtual) json 파라미터
root TEXT (Virtual) path 파라미터
rowid BIGINT (Virtual) 행 식별자

이 함수들은 동일한 이름을 가진 SQLite의 함수와 유사합니다. json_each와 json_tree는 같은 FROM 절의 이전 서브쿼리를 참조하기 때문에 lateral join이라는 점에 주의하세요.

예제:

CREATE TABLE example (j JSON);
INSERT INTO example VALUES
    ('{"family": "anatidae", "species": ["duck", "goose"], "coolness": 42.42}'),
    ('{"family": "canidae", "species": ["labrador", "bulldog"], "hair": true}');

SELECT je.*, je.rowid
FROM example AS e, json_each(e.j) AS je;
key value type atom id parent fullkey path rowid
family "anatidae" VARCHAR "anatidae" 2 NULL $.family $ 0
species ["duck","goose"] ARRAY NULL 4 NULL $.species $ 1
coolness 42.42 DOUBLE 42.42 8 NULL $.coolness $ 2
family "canidae" VARCHAR "canidae" 2 NULL $.family $ 0
species ["labrador","bulldog"] ARRAY NULL 4 NULL $.species $ 1
hair true BOOLEAN true 8 NULL $.hair $ 2 
SELECT je.*, je.rowid
FROM example AS e, json_each(e.j, '$.species') AS je;
key value type atom id parent fullkey path rowid
0 "duck" VARCHAR "duck" 5 NULL $.species[0] $.species 0
1 "goose" VARCHAR "goose" 6 NULL $.species[1] $.species 1
0 "labrador" VARCHAR "labrador" 5 NULL $.species[0] $.species 0
1 "bulldog" VARCHAR "bulldog" 6 NULL $.species[1] $.species 1 
SELECT je.key, je.value, je.type, je.id, je.parent, je.fullkey, je.rowid
FROM example AS e, json_tree(e.j) AS je;
key value type id parent fullkey rowid
NULL {"family":"anatidae","species":["duck","goose"],"coolness":42.42} OBJECT 0 NULL $ 0
family "anatidae" VARCHAR 2 0 $.family 1
species ["duck","goose"] ARRAY 4 0 $.species 2
0 "duck" VARCHAR 5 4 $.species[0] 3
1 "goose" VARCHAR 6 4 $.species[1] 4
coolness 42.42 DOUBLE 8 0 $.coolness 5
NULL {"family":"canidae","species":["labrador","bulldog"],"hair":true} OBJECT 0 NULL $ 0
family "canidae" VARCHAR 2 0 $.family 1
species ["labrador","bulldog"] ARRAY 4 0 $.species 2
0 "labrador" VARCHAR 5 4 $.species[0] 3
1 "bulldog" VARCHAR 6 4 $.species[1] 4
hair true BOOLEAN 8 0 $.hair 5

더 알아보기 (Learn more)