JSON 쿼리하기 (Query JSON)

원문 보기 위키 갱신 2026.10.07

D1에서 JSON 데이터를 직접 쿼리하고 조작하는 방법을 다루는 문서예요. 저장된 JSON 객체 내에서 값을 추출하고, 배열을 확장하고, 생성된 컬럼을 만드는 방법을 알려줘요.

출처: 문서

본문

Query JSON

저장된 JSON 객체 내의 쿼리 경로를 사용하세요. 예를 들어 명명된 키나 배열 인덱스의 값을 직접 추출할 수 있는데, 특히 큰 JSON 객체에서 유용해요.
객체나 배열 안의 값을 삽입 및/또는 교체하세요.
JSON 객체나 배열의 내용을 여러 행으로 확장하세요. 예를 들어 WHERE ... IN 조건의 일부로 사용할 때요.
삽입한 JSON 객체의 값으로 자동 채워지는 생성된 컬럼을 만드세요.

D1 안에서 JSON을 직접 파싱하는 가장 큰 장점 중 하나는 데이터베이스로의 왕복(쿼리) 횟수를 직접 줄일 수 있다는 점이에요. JSON 객체를 애플리케이션으로 읽어(1) 파싱한 뒤 다시 쓰는(2) 경우를 줄여주거든요.

이를 통해 데이터를 더 정확하게 쿼리하고, 애플리케이션이 추가로 파싱하고 필터링해야 하는 결과 집합을 줄일 수 있어요.

유형 (Types)

JSON 데이터는 D1에서 TEXT 컬럼으로 저장돼요. JSON 유형은 일반적으로 D1과 동일한 유형 변환 규칙을 따르며, 여기에는 다음이 포함돼요.

JSON null은 D1 NULL로 처리돼요.
JSON 숫자는 INTEGER 또는 REAL로 처리돼요.
부울은 INTEGER 값으로 처리돼요. true는 1, false는 0이에요.
객체와 배열 값은 TEXT로 처리돼요.

지원되는 함수 (Supported functions)

다음 표는 D1에 내장된 JSON 함수와 사용 예시를 정리한 것이에요.

json 인자 자리 표시자는 JSON 객체, 배열, 문자열, 숫자 또는 null 값이 될 수 있어요.
value 인자는 문자열 리터럴만 받아들이며, 잘 구성된 JSON이라도 입력을 문자열로 취급해요. 이 규칙의 예외는 json_* 함수를 중첩할 때예요. 바깥(감싸는) 함수는 안쪽(감싸진) 함수의 반환 값을 JSON으로 해석해요.
path 인자는 경로 스타일 순회 구문을 받아들여요. 예를 들어 $는 최상위 객체/배열을, $.key1.key2는 중첩 객체를, $.key[2]는 배열의 인덱스를 가리켜요.

| 함수 | 설명 | 예시 | | json(json) | 제공된 문자열이 JSON인지 검증하고 그 JSON 객체의 축소(minified) 버전을 반환해요. | json('{"hello":["world" ,"there"] }')는 {"hello":["world","there"]}를 반환해요. | | json_array(value1, value2, value3, ...) | 값들로부터 JSON 배열을 반환해요. | json_array(1, 2, 3)는 [1, 2, 3]을 반환해요. | | json_array_length(json) - json_array_length(json, path) | JSON 배열의 길이를 반환해요. | json_array_length('{"data":["x", "y", "z"]}', '$.data')는 3을 반환해요. | | json_extract(json, path) | $.path.to.value 구문을 사용해 주어진 경로의 값(들)을 추출해요. | json_extract('{"temp":"78.3", "sunset":"20:44"}', '$.temp')는 "78.3"을 반환해요. | | json -> path | 경로 구문을 사용해 주어진 경로의 값(들)을 추출하고 JSON으로 반환해요. | | | json ->> path | 경로 구문을 사용해 주어진 경로의 값(들)을 추출하고 SQL 유형으로 반환해요. | | | json_insert(json, path, value) | 주어진 경로에 값을 삽입해요. 기존 값을 덮어쓰지 않아요. | | | json_object(label1, value1, ...) | (키, 값) 쌍들을 받아 JSON 객체를 반환해요. | json_object('temp', 45, 'wind_speed_mph', 13)는 {"temp":45,"wind_speed_mph":13}을 반환해요. | | json_patch(target, patch) | JSON MergePatch ↗︎ 방식을 사용해 제공된 패치를 대상 JSON 객체에 병합해요. | | | json_remove(json, path, ...) | 지정된 경로의 키와 값을 제거해요. | json_remove('[60,70,80,90]', '$[0]')는 70,80,90]을 반환해요. | | json_replace(json, path, value) | 주어진 경로에 값을 삽입해요. 기존 값을 덮어쓰지만, 키가 없으면 새 키를 만들지는 않아요. | | | json_set(json, path, value) | 주어진 경로에 값을 삽입해요. 기존 값을 덮어써요. | | | json_type(json) - json_type(json, path) | 제공된 값 또는 지정된 경로의 값의 유형을 반환해요. null, true, false, integer, real, text, array, object 중 하나를 반환해요. | json_type('{"temperatures":[73.6, 77.8, 80.2]}', '$.temperatures')는 array를 반환해요. | | json_valid(json) | 잘못된 JSON이면 0(false)을, 유효한 JSON이면 1(true)을 반환해요. | json_valid({invalid:json})는 0을 반환해요. | | json_quote(value) | 제공된 SQL 값을 JSON 표현으로 변환해요. | json_quote('[1, 2, 3]')는 [1,2,3]을 반환해요. | | json_group_array(value) | 제공된 값(들)을 JSON 배열로 반환해요. | | | json_each(value) - json_each(value, path) | 객체 안의 각 요소를 개별 행으로 반환해요. 최상위 객체만 순회해요. | | | json_tree(value) - json_tree(value, path) | 객체 안의 각 요소를 개별 행으로 반환해요. 전체 객체를 순회해요. | |

D1이 기반으로 하는 SQLite JSON 확장 ↗︎에 추가적인 사용 예시가 있어요.

오류 처리 (Error Handling)

JSON 함수는 JSON이 아니거나 유효한 JSON이 아닌 데이터에 대해 작업할 때 malformed JSON 오류를 반환해요. D1은 유효한 JSON을 RFC 7159 ↗︎ 준수로 간주해요.

다음 예시에서 문자열(유효한 JSON이 아님)에 json_extract를 호출하면 쿼리가 malformed JSON 오류를 반환해요.

SELECT json_extract('not valid JSON: just a string', '$')

이것은 오류를 반환해요.

ERROR 9015: SQL engine error: query error: Error code 1: SQL error or missing database (malformed
  JSON)`

생성된 컬럼 (Generated columns)

D1의 생성된 컬럼 지원을 사용하면 다른 컬럼의 값(추출하거나 계산한 JSON 데이터 값 포함)을 기반으로 생성되는 동적 컬럼을 만들 수 있어요.

이 컬럼은 다른 컬럼처럼 쿼리할 수 있고 인덱스를 정의할 수 있어요. 자주 쿼리하고 필터링하는 JSON 데이터가 있다면 생성된 컬럼과 인덱스를 만드는 것이 쿼리 성능을 크게 향상시킬 수 있어요.

예를 들어 큰 JSON 객체 안의 값을 기반으로 컬럼을 정의하려면 AS 키워드와 JSON 함수를 결합해 유형이 지정된 컬럼을 생성해요.

CREATE TABLE some_table (
    -- other columns omitted
    raw_data TEXT -- JSON: {"measurement":{"aqi":[21,42,58],"wind_mph":"13","location":"US-NY"}}
    location AS (json_extract(raw_data, '$.measurement.location')) STORED
)

컬럼 생성 방법에 대한 자세한 내용은 생성된 컬럼을 참조하세요.

사용 예시 (Example usage)

값 추출하기 (Extract values)

D1에서 JSON 객체에서 값을 추출하는 방법은 세 가지가 있어요.

json_extract() 함수 - 예: json_extract(text_column_containing_json, '$.path.to.value).
-> 연산자 - 값의 JSON 표현을 반환해요.
->> 연산자 - 값의 SQL 표현을 반환해요.

->와 ->> 연산자는 PostgreSQL과 MySQL/MariaDB의 동일한 연산자와 비슷하게 작동해요.

sensor_reading라는 컬럼에 다음 JSON 객체가 있다고 가정하면, 여기서 값을 직접 추출할 수 있어요.

{
    "measurement": {
        "temp_f": "77.4",
        "aqi": [21, 42, 58],
        "o3": [18, 500],
        "wind_mph": "13",
        "location": "US-NY"
    }
}

-- Extract the temperature value
json_extract(sensor_reading, '$.measurement.temp_f')-- returns "77.4" as TEXT

-- Extract the maximum PM2.5 air quality reading
sensor_reading -> '$.measurement.aqi[3]' -- returns 58 as a JSON number

-- Extract the o3 (ozone) array in full
sensor_reading ->> '$.measurement.o3' -- returns '[18, 500]' as TEXT

배열의 길이 구하기 (Get the length of an array)

JSON 배열의 길이는 두 가지 방법으로 구할 수 있어요.

json_array_length(value)를 직접 호출하기
json_array_length(value, path)를 호출해 객체나 바깥 배열 안의 배열 경로를 지정하기

예를 들어 login_history라는 컬럼에 다음 JSON 객체가 저장되어 있다면 마지막 로그인 횟수를 직접 얻을 수 있어요.

{
    "user_id": "abc12345",
    "previous_logins": ["2023-03-31T21:07:14-05:00", "2023-03-28T08:21:02-05:00", "2023-03-28T05:52:11-05:00"]
}

json_array_length(login_history, '$.previous_logins') --> returns 3 as an INTEGER

json_array_length를 더 복잡한 쿼리의 조건으로도 사용할 수 있어요. 예를 들어 WHERE json_array_length(some_column, '$.path.to.value') >= 5처럼요.

기존 객체에 값 삽입하기 (Insert a value into an existing object)

json_insert()로 기존 JSON 객체나 배열에 값을 삽입할 수 있어요. 예를 들어 users 테이블의 login_history라는 TEXT 컬럼에 다음 객체가 있다고 가정해요.

{"history": ["2023-05-13T15:13:02+00:00", "2023-05-14T07:11:22+00:00", "2023-05-15T15:03:51+00:00"]}

login_history 컬럼 안의 history 배열에 새 타임스탬프를 추가하려면 다음과 같은 쿼리를 작성하세요.

UPDATE users
SET login_history = json_insert(login_history, '$.history[#]', '2023-05-15T20:33:06+00:00')
WHERE user_id = 'aba0e360-1e04-41b3-91a0-1f2263e1e0fb'

json_insert에 세 개의 인자를 제공해요.

수정하려는 JSON이 들어 있는 컬럼의 이름.
수정할 객체 안의 키 경로.
삽입할 JSON 값. [#]를 사용하면 json_insert가 배열의 끝에 추가하도록 지시해요.

기존 값을 교체하려면 json_replace()를 사용해요. 이미 존재하는 키-값 쌍이 있다면 덮어써요. 존재 여부와 관계없이 값을 설정하려면 json_set()을 사용하세요.

IN 쿼리를 위해 배열 확장하기 (Expand arrays for IN queries)

json_each를 사용해 배열을 여러 행으로 확장하세요. 여러 값에 대한 WHERE column IN (?) 쿼리를 구성할 때 유용해요. 예를 들어 정수 id로 사용자 목록을 업데이트하려면 json_each를 사용해 각 값을 value라는 컬럼으로 가진 테이블을 반환하세요.

UPDATE users
SET last_audited = '2023-05-16T11:24:08+00:00'
WHERE id IN (SELECT value FROM json_each('[183183, 13913, 94944]'))

이렇게 하면 json_each가 반환한 테이블에서 value 컬럼만 추출하며, 각 행은 배열로 전달한 사용자 ID를 나타내요.

json_each는 실제로 여러 컬럼이 있는 테이블을 반환하며, 가장 관련 있는 것은 다음과 같아요.

key - 키(또는 인덱스).
value - json_each가 파싱한 각 요소의 리터럴 값.
type - 값의 유형: null, true, false, integer, real, text, array, object 중 하나.
fullkey - 요소의 전체 경로: 배열의 두 번째 요소면 $[1], 중첩 객체면 $.path.to.key처럼요.
path - 최상위 경로 - fullkey가 $[0]인 요소의 경로는 $이에요.

이 예시에서 SELECT * FROM json_each('[183183, 13913, 94944]')은 아래와 같은 테이블을 반환해요.

key|value|type|id|fullkey|path
0|183183|integer|1|$[0]|$
1|13913|integer|2|$[1]|$
2|94944|integer|3|$[2]|$

Worker 안에서 D1 Workers Binding API와 함께 json_each를 사용하려면 문(statement)을 만들고 JSON.stringify로 배열을 바인딩된 매개변수로 전달하면 돼요.

const stmt = context.env.DB
    .prepare("UPDATE users SET last_audited = ? WHERE id IN (SELECT value FROM json_each(?1))")
const resp = await stmt.bind(
    "2023-05-16T11:24:08+00:00",
    JSON.stringify([183183, 13913, 94944])
    ).run()

이렇게 하면 id가 제공된 세 개 중 하나와 일치하는 users 테이블의 행만 업데이트돼요.