JSON 개요

JSON 개요 (JSON Overview)

DuckDB는 이미 있는 JSON에서 값을 읽고, 새로운 JSON 데이터를 만드는 데 유용한 SQL 함수들을 지원해요. JSON 지원은 json 확장으로 제공되는데, 대부분의 DuckDB 배포판에 포함되어 있고 처음 사용할 때 자동으로 로드돼요. 직접 설치하거나 로드하고 싶다면 "설치와 로드 (Installing and Loading)" 페이지를 참고하세요.

출처: 공식문서

JSON이란

JSON은 읽을 수 있는 텍스트로 데이터를 주고받는 데 쓰는 오픈 표준 포맷이에요. 속성-값 쌍과 배열(또는 직렬화 가능한 값)로 이뤄진 데이터 객체를 저장하고 전송해요. 테이블형 데이터에 아주 효율적인 포맷은 아니지만, 특히 데이터 교환 포맷으로 매우 흔하게 쓰여요.

DuckDB는 JSON 추출을 위해 JSONPath와 JSON Pointer 두 가지 인터페이스를 제공해요. 둘 다 화살표 연산자(->)와 json_extract 함수 호출로 동작해요.

주의할 점은 DuckDB가 JSONPath에서 조회(lookup)만 지원한다는 거예요. 즉 .<key>로 필드를 뽑거나 [<index>]로 배열 요소를 뽑는 방식이에요. 배열은 뒤에서도 인덱싱할 수 있고, 두 방식 모두 와일드카드 *를 지원해요. 추가 변환이 필요하면 SQL을 바로 쓸 수 있기 때문에 DuckDB는 완전한 JSONPath 문법을 지원하지는 않아요.

그래서 JSONPath 문법과 JSON Pointer 문법 중 하나를 골라서 애플리케이션 전체에서 일관되게 쓰는 게 좋아요.

⚠️ PostgreSQL의 관례를 따라 DuckDB는 ARRAY와 LIST 데이터 타입은 1-based 인덱싱을 쓰지만, JSON 데이터 타입은 0-based 인덱싱을 사용해요.

JSON 읽기

디스크에서 JSON 파일을 읽고 옵션을 자동 추론하려면:

SELECT * FROM 'todos.json';

read_json 함수에 커스텀 옵션을 붙여서:

SELECT *
FROM read_json('todos.json',
               format = 'array',
               columns = {userId: 'UBIGINT',
                          id: 'UBIGINT',
                          title: 'VARCHAR',
                          completed: 'BOOLEAN'});

표준 입력(stdin)에서 읽고 옵션 자동 추론:

cat data/json/todos.json | duckdb -c "SELECT * FROM read_json('/dev/stdin')"

JSON 파일을 테이블로 읽으려면:

CREATE TABLE todos (userId UBIGINT, id UBIGINT, title VARCHAR, completed BOOLEAN);
COPY todos FROM 'todos.json' (AUTO_DETECT true);

또는 스키마를 직접 지정하지 않고 CREATE TABLE ... AS SELECT로 만들 수도 있어요:

CREATE TABLE todos AS
    SELECT * FROM 'todos.json';

DuckDB v1.3.0부터 JSON 리더는 filename 가상 컬럼을 반환해요:

SELECT filename, *
FROM 'todos-*.json';

JSON 쓰기

쿼리 결과를 JSON 파일로 쓰려면:

COPY (SELECT * FROM todos) TO 'todos.json';

JSON 데이터 다루기

JSON 데이터를 저장할 컬럼이 있는 테이블을 만들고 데이터를 넣어볼게요:

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

family 키의 값을 가져오기:

SELECT j.family FROM example;

"anatidae"

JSONPath 표현식으로 family 키의 값을 JSON으로 추출:

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

"anatidae"

JSONPath 표현식으로 family 키의 값을 VARCHAR로 추출:

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

anatidae

특수 문자인 [ 와 .가 들어 있는 JSON 객체 키는 큰따옴표(")로 감싸서 사용할 수 있어요:

SELECT '{"d[u]._\"ck":42}'->'$."d[u]._\"ck"' AS v;