JSON 함수

JSON 함수 (JSON Functions In SQLite)

1. 개요 (Overview)

기본적으로 SQLite는 JSON 값을 다루는 30개의 함수와 2개의 연산자를 지원해요. 또한 JSON 문자열을 분해하는 데 사용할 수 있는 4개의 테이블 값 함수 (table-valued functions)도 있어요. 아래에 나열된 모든 함수는 SQLITE_INNOCUOUS와 SQLITE_DETERMINISTIC 플래그를 가지고 있어요.

출처: JSON Functions In SQLite

본문

스칼라 함수와 연산자는 28개가 있어요:

json(_json_)
jsonb(_json_)
json_array(_value1_ ,_value2_ ,...)
jsonb_array(_value1_ ,_value2_ ,...)
jsonb_array_insert(_json_ ,_path_ ,_value_ ,...)
json_array_insert(_json_ ,_path_ ,_value_ ,...)
json_array_length(_json_) / json_array_length(_json_ ,_path_)
json_error_position(_json_)
json_extract(_json_ ,_path_ ,...)
jsonb_extract(_json_ ,_path_ ,...)
_json_ -> _path_
_json_ ->> _path_
json_insert(_json_ ,_path_ ,_value_ ,...)
jsonb_insert(_json_ ,_path_ ,_value_ ,...)
json_object(_label1_ ,_value1_ ,...)
jsonb_object(_label1_ ,_value1_ ,...)
json_patch(_json_ 1,json2)
jsonb_patch(_json_ 1,json2)
json_pretty(_json_)
json_quote(_value_)
json_remove(_json_ ,_path_ ,...)
jsonb_remove(_json_ ,_path_ ,...)
json_replace(_json_ ,_path_ ,_value_ ,...)
jsonb_replace(_json_ ,_path_ ,_value_ ,...)
json_set(_json_ ,_path_ ,_value_ ,...)
jsonb_set(_json_ ,_path_ ,_value_ ,...)
json_type(_json_) / json_type(_json_ ,_path_)
json_valid(_json_) / json_valid(_json_ ,flags)

집계 SQL 함수 (aggregate SQL functions)는 4개가 있어요:

json_group_array(_value_)
jsonb_group_array(_value_)
json_group_object(_label_ ,_value_)
jsonb_group_object(_label_ ,_value_)

테이블 값 함수 (table-valued functions)는 4개가 있어요:

json_each(_json_) / json_each(_json_ ,_path_)
json_tree(_json_) / json_tree(_json_ ,_path_)
jsonb_each(_json_) / jsonb_each(_json_ ,_path_)
jsonb_tree(_json_) / jsonb_tree(_json_ ,_path_)

2. JSON 지원 컴파일하기 (Compiling in JSON Support)

JSON 함수와 연산자는 SQLite 버전 3.38.0 (2022-02-22)부터 기본적으로 SQLite에 내장돼요. -DSQLITE_OMIT_JSON 컴파일 타임 옵션을 추가하면 생략할 수 있어요. 3.38.0 이전에는 JSON 함수가 확장이었으며 -DSQLITE_ENABLE_JSON1 컴파일 타임 옵션이 포함된 경우에만 빌드에 포함되었어요. 다시 말해 JSON 함수는 SQLite 버전 3.37.2 이하에서는 opt-in(선택 포함)이었다가 SQLite 버전 3.38.0 이상에서는 opt-out(선택 제외)으로 바뀌었어요.

3. 인터페이스 개요 (Interface Overview)

SQLite는 JSON을 일반 텍스트로 저장해요. 하위 호환성 제약 때문에 SQLite는 NULL, 정수, 부동 소수점 숫자, 텍스트, BLOB인 값만 저장할 수 있어요. 새로운 "JSON" 타입을 추가하는 것은 불가능해요.

3.1. JSON 인자 (JSON arguments)

JSON을 첫 번째 인자로 받는 함수들의 경우, 그 인자는 JSON 객체, 배열, 숫자, 문자열 또는 null일 수 있어요. SQLite 숫자 값과 NULL 값은 각각 JSON 숫자와 null로 해석돼요. SQLite 텍스트 값은 JSON 객체, 배열 또는 문자열로 이해될 수 있어요. 잘 구성된 JSON 객체, 배열 또는 문자열이 아닌 SQLite 텍스트 값이 JSON 함수에 전달되면, 그 함수는 보통 오류를 던져요. (이 규칙의 예외는 json_valid(), json_quote(), json_error_position()이에요.)

이 루틴들은 모든 rfc-8259 JSON 구문과 JSON5 확장을 이해해요. 이 루틴들이 생성한 JSON 텍스트는 항상 정식 JSON 정의 (canonical JSON definition)를 엄격히 따르며 JSON5나 다른 확장을 포함하지 않아요. JSON5를 읽고 이해하는 능력은 버전 3.42.0 (2023-05-16)에서 추가되었어요. 이전 SQLite 버전은 정식 JSON만 읽었어요.

3.2. JSONB

버전 3.45.0 (2024-01-15)부터 SQLite는 JSON의 내부 "파스 트리" 표현을 BLOB으로서 디스크에 "JSONB"라고 부르는 형식으로 저장할 수 있게 해요. SQLite의 내부 이진 JSON 표현을 데이터베이스에 직접 저장함으로써, 애플리케이션은 JSON 값을 읽고 업데이트할 때 JSON을 파싱하고 렌더링하는 오버헤드를 우회할 수 있어요. 내부 JSONB 형식은 또한 텍스트 JSON보다 디스크 공간을 약간 덜 사용해요.

텍스트 JSON을 입력으로 받는 모든 SQL 함수 매개변수는 JSONB 형식의 BLOB도 받아들여요. 함수는 두 경우 모두 동일하게 동작하며, 입력이 JSONB일 때 JSON 파서를 실행할 필요가 없으므로 더 빠르게 실행될 뿐이에요.

JSON 텍스트를 반환하는 대부분의 SQL 함수는 동등한 JSONB를 반환하는 대응 함수를 가져요. 텍스트 형식으로 JSON을 반환하는 함수는 "json_"로 시작하고 이진 JSONB 형식을 반환하는 함수는 "jsonb_"로 시작해요.

3.2.1. JSONB 형식 (The JSONB format)

JSONB는 SQLite가 사용하는 JSON의 이진 표현이며 SQLite 전용의 내부 사용을 위한 것이에요. 애플리케이션은 SQLite 밖에서 JSONB를 사용하거나 JSONB 형식을 역공학하려고 시도해서는 안 돼요.

"JSONB" 이름은 PostgreSQL에서 영감을 받았지만, SQLite의 JSONB 디스크 형식은 PostgreSQL의 것과 같지 않아요. 두 형식은 이름이 같지만 이진 호환은 되지 않아요. PostgreSQL JSONB 형식은 객체와 배열의 요소에 대해 O(1) 조회를 제공한다고 주장해요. SQLite의 JSONB 형식은 그런 주장을 하지 않아요. SQLite의 JSONB는 텍스트 JSON과 마찬가지로 SQLite의 대부분의 연산에서 O(N) 시간 복잡도를 가져요. SQLite에서 JSONB의 장점은 텍스트 JSON보다 더 작고 더 빠르다는 점이에요 - 잠재적으로 몇 배 더 빠르다는 뜻이에요. 디스크 JSONB 형식에는 향상을 추가할 공간이 있으며 SQLite의 향후 버전은 JSONB에서 요소의 O(1) 조회를 제공하는 옵션을 포함할 수도 있지만, 현재는 그런 기능이 없어요.

3.2.2. 잘못된 JSONB 처리 (Handling of malformed JSONB)

SQLite가 생성하는 JSONB는 항상 잘 구성되어 있을 거예요. 권장 관행을 따라 JSONB를 불투명한 BLOB으로 취급하면 아무 문제가 없을 거예요. 하지만 JSONB는 그냥 BLOB이므로, 장난치는 프로그래머가 JSONB와 유사하지만 기술적으로 잘못된 BLOB을 고안할 수 있어요. 잘못된 형식의 JSONB가 JSON 함수에 공급되면 다음 중 어떤 일이든 일어날 수 있어요:

SQL 문이 "malformed JSON" 오류로 중단될 수 있어요.
JSONB blob의 잘못된 부분이 답에 영향을 주지 않으면 올바른 답이 반환될 수 있어요.
이상하거나 무의미한 답이 반환될 수 있어요.

SQLite가 잘못된 JSONB를 처리하는 방식은 SQLite 버전마다 바뀔 수 있어요. 시스템은 garbage-in/garbage-out 규칙을 따르는데: JSON 함수에 잘못된 JSONB를 공급하면 잘못된 답을 돌려받아요. JSONB의 유효성이 의심되면 json_valid() 함수를 사용해 검증하세요.

우리는 이 한 가지를 약속해요: 잘못된 JSONB는 취약점으로 이어질 수 있는 메모리 오류나 유사한 문제를 절대 일으키지 않아요. 잘못된 JSONB는 터무니없는 답으로 이어지거나 쿼리가 중단되게 할 수 있지만, 크래시를 일으키지는 않아요.

3.3. PATH 인자 (PATH arguments)

PATH 인자를 받는 함수들의 경우, 그 PATH는 잘 구성되어야 하며 그렇지 않으면 함수가 오류를 던져요. 잘 구성된 PATH는 정확히 하나의 '$' 문자로 시작하고 그 뒤에 "objectlabel" 또는 "[arrayindex]" 형태의 인스턴스가 0개 이상 오는 텍스트 값이에요.

_arrayindex_는 보통 음이 아닌 정수 _N_이에요. 이 경우 선택되는 배열 요소는 왼쪽에서 0으로 시작하는 배열의 N 번째 요소예요. _arrayindex_는 또한 "#-N" 형태일 수 있으며, 이 경우 선택되는 요소는 오른쪽에서 N 번째예요. 배열의 마지막 요소는 "#-1**"이에요. "#" 문자를 "배열의 요소 수"로 생각하세요. 그러면 "#-1" 표현식은 배열의 마지막 항목에 해당하는 정수로 평가돼요. 예를 들어 기존 JSON 배열에 값을 추가할 때처럼 배열 인덱스가 # 문자 그 자체인 것이 유용할 때가 있어요:

json_set('[0,1,2]','$[#]','new') → '[0,1,2,"new"]'

3.4. VALUE 인자 (VALUE arguments)

"value" 인자("value1"과 "value2"로도 표시됨)를 받는 함수들의 경우, 그 인자들은 보통 따옴표가 붙어 결과에서 JSON 문자열 값이 되는 리터럴 문자열로 이해돼요. 입력 value 문자열이 잘 구성된 JSON처럼 보여도, 여전히 결과에서 리터럴 문자열로 해석돼요.

하지만 value 인자가 다른 JSON 함수의 결과나 -> 연산자의 결과(->> 연산자는 아님)에서 직접 온 경우, 그 인자는 실제 JSON으로 이해되어 따옴표 붙은 문자열 대신 완전한 JSON이 삽입돼요.

예를 들어 다음 json_object() 호출에서 value 인자는 잘 구성된 JSON 배열처럼 보여요. 하지만 그것은 그냥 일반 SQL 텍스트이므로 리터럴 문자열로 해석되어 따옴표 붙은 문자열로 결과에 추가돼요:

json_object('ex','[52,3.14159]') → '{"ex":"[52,3.14159]"}'
json_object('ex',('[52,3.14159]'->>'$')) → '{"ex":"[52,3.14159]"}'

하지만 외부 json_object() 호출의 value 인자가 json() 또는 json_array() 같은 다른 JSON 함수의 결과이면, 그 값은 실제 JSON으로 이해되어 그대로 삽입돼요:

json_object('ex',json('[52,3.14159]')) → '{"ex":[52,3.14159]}'
json_object('ex',json_array(52,3.14159)) → '{"ex":[52,3.14159]}'
json_object('ex','[52,3.14159]'->'$') → '{"ex":[52,3.14159]}'

명확히 하자면: "json" 인자는 그 값이 어디서 오든 항상 JSON으로 해석돼요. 하지만 "value" 인자는 그 인자들이 다른 JSON 함수나 -> 연산자에서 직접 오는 경우에만 JSON으로 해석돼요.

JSON 문자열로 해석되는 JSON 값 인자 내에서 유니코드 이스케이프 시퀀스는 표현된 유니코드 코드 포인트가 나타내는 문자나 이스케이프된 제어 문자와 동등하게 취급되지 않아요. 그런 이스케이프 시퀀스는 번역되거나 특별히 취급되지 않으며, SQLite의 JSON 함수에 의해 일반 텍스트로 취급돼요.

3.5. 호환성 (Compatibility)

이 JSON 라이브러리의 현재 구현은 재귀 하강 파서를 사용해요. 과도한 스택 공간 사용을 피하기 위해 중첩 수준이 1000개보다 많은 모든 JSON 입력은 잘못된 것으로 간주돼요. 중첩 깊이 제한은 RFC-8259 섹션 9에서 호환 가능한 JSON 구현에 대해 허용돼요.

3.6. JSON5 확장 (JSON5 Extensions)

버전 3.42.0 (2023-05-16)부터 이 루틴들은 JSON5 확장을 포함하는 입력 JSON 텍스트를 읽고 해석해요. 하지만 이 루틴들이 생성하는 JSON 텍스트는 항상 JSON의 정식 정의 (canonical definition of JSON)를 엄격히 준수할 거예요.

JSON5 확장의 요약은 다음과 같아요 (JSON5 스펙에서 각색):

객체 키는 따옴표가 없는 식별자일 수 있어요.
객체는 단일 trailing comma를 가질 수 있어요.
배열은 단일 trailing comma를 가질 수 있어요.
문자열은 작은 따옴표로 묶을 수 있어요.
문자열은 줄바꿈 문자를 이스케이프하여 여러 줄에 걸칠 수 있어요.
문자열은 새로운 문자 이스케이프를 포함할 수 있어요.
숫자는 16진수일 수 있어요.
숫자는 앞이나 뒤에 소수점을 가질 수 있어요.
숫자는 "Infinity", "-Infinity", "NaN"일 수 있어요.
숫자는 명시적인 더하기 부호로 시작할 수 있어요.
단일 (//...) 및 다중 (/.../) 주석이 허용돼요.
추가 공백 문자가 허용돼요.

문자열 X를 JSON5에서 정식 JSON으로 변환하려면 "json(X)"를 호출해요. "json()" 함수의 출력은 입력에 있는 JSON5 확장과 무관하게 정식 JSON이 돼요. 하위 호환성을 위해 "flags" 인자 없는 json_valid(X) 함수는 함수가 이해할 수 있는 JSON5라도 정식 JSON이 아닌 입력에 대해서는 계속 false를 보고해요. 입력 문자열이 유효한 JSON5인지 여부를 결정하려면 json_valid의 "flags" 인자에 0x02 비트를 포함하세요: "json_valid(X,2)".

이 루틴들은 모든 JSON5를 이해하며, 조금 더도 이해해요. SQLite는 JSON5 구문을 다음 두 가지 방식으로 확장해요:

엄격한 JSON5는 따옴표 없는 객체 키가 ECMAScript 5.1 IdentifierName이어야 한다고 요구해요. 하지만 키가 ECMAScript 5.1 IdentifierName인지 여부를 결정하려면 큰 유니코드 테이블과 많은 코드가 필요해요. 이런 이유로 SQLite는 객체 키가 공백 문자가 아닌 U+007f보다 큰 어떤 유니코드 문자도 포함할 수 있게 허용해요. 이 완화된 "식별자" 정의는 구현을 크게 단순화하고 JSON 파서가 더 작아지고 더 빨리 실행되게 해요.
JSON5는 부동 소수점 무한대를 정확히 그 경우 - 첫 "I"는 대문자이고 다른 모든 문자는 소문자인 - "Infinity", "-Infinity", "+Infinity"로 표현할 수 있게 해요. SQLite는 또한 "Infinity" 대신 약어 "Inf"를 사용할 수 있게 허용하고 두 키워드 모두 대문자와 소문자의 어떤 조합으로도 나타나도록 허용해요. 유사하게 JSON5는 not-a-number에 "NaN"을 허용해요. SQLite는 이것을 확장해 "QNaN"과 "SNaN"도 대소문자의 어떤 조합으로든 허용해요. SQLite가 NaN, QNaN, SNaN을 그냥 "null"의 대체 철자로 해석한다는 점에 유의하세요. 이 확장은 (우리가 들은 바로는) 무한대와 not-a-number에 대한 이런 비표준 표현을 포함하는 JSON이 실제로 많이 존재하기 때문에 추가되었어요.

3.7. 성능 고려 사항 (Performance Considerations)

대부분의 JSON 함수는 내부 처리를 JSONB로 수행해요. 그래서 입력이 텍스트이면 먼저 입력 텍스트를 JSONB로 변환해야 해요. 입력이 이미 JSONB 형식이면 변환이 필요 없고 그 단계를 건너뛸 수 있어 성능이 더 빨라요.

그래서 한 JSON 함수의 인자가 다른 JSON 함수에 의해 공급될 때, 인자로 사용되는 함수에는 보통 "jsonb_" 변형을 사용하는 것이 더 효율적이에요.

... json_insert(A,'$.b',json(C)) ...   ← 덜 효율적
... json_insert(A,'$.b',jsonb(C)) ...  ← 더 효율적

집계 JSON SQL 함수는 이 규칙의 예외예요. 그 함수들은 모두 JSONB 대신 텍스트를 사용해 처리를 수행해요. 그래서 집계 JSON SQL 함수의 경우 인자가 "json_" 함수로 공급되는 것이 "jsonb_" 함수보다 더 효율적이에요.

... json_group_array(json(A))) ...  ← 더 효율적
... json_group_array(jsonb(A))) ... ← 덜 효율적

3.8. JSON BLOB 입력 버그 (The JSON BLOB Input Bug)

JSON 입력이 JSONB가 아니고 텍스트로 변환했을 때 텍스트 JSON처럼 보이는 BLOB이면, 텍스트 JSON으로 받아들여져요. 이것은 사실 SQLite 개발자들이 몰랐던 원래 구현의 오래된 버그예요. 문서는 JSON 함수에 대한 BLOB 입력이 오류를 일으켜야 한다고 명시했어요. 하지만 실제 구현에서는 BLOB 내용이 데이터베이스의 텍스트 인코딩에서 유효한 JSON 문자열이라면 입력이 받아들여졌어요.

이 JSON BLOB 입력 버그는 JSON 루틴이 3.45.0 릴리스 (2024-01-15)에서 재구현될 때 우연히 고쳐졌어요. 이로 인해 옛 동작에 의존하게 된 애플리케이션에 문제가 발생했어요. (그 애플리케이션들의 변호를 하자면: 그들은 종종 CLI에서 사용 가능한 readfile() SQL 함수에 의해 BLOB을 JSON으로 사용하도록 유인되었어요. Readfile()은 디스크 파일에서 JSON을 읽는 데 사용되었지만, readfile()은 BLOB을 반환해요. 그리고 그것은 그들에게 작동했으니, 왜 그냥 하지 않겠어요?)

하위 호환 버그 호환성을 위해 (이전에는 올바르지 않았던) 다른 해석이 작동하지 않으면 BLOB을 텍스트 JSON으로 해석하는 레거시 동작이 여기에 문서화되었고 버전 3.45.1 (2024-01-30) 및 모든 후속 릴리스에서 공식적으로 지원돼요. 하지만 유효한 JSONB이면서 동시에 텍스트로 변환한 후 유효한 JSON인 BLOB도 존재한다는 점을 주의하세요. 예를 들어 BLOB x'33343536'은 유효한 JSONB(구체적으로 정수 456)이고, 텍스트로 변환하면 유효한 JSON 정수 리터럴 3456이에요. 따라서 JSON 텍스트를 BLOB으로 저장하는 레거시 데이터베이스가 있고 SQLite 3.45.0 이상에서 그 데이터베이스를 계속 사용하고 싶다면, 그 레거시 JSON 텍스트에 UPDATE를 실행해 실제로 TEXT로 저장되게 하는 것이 좋아요 (BLOB이 아니라).

3.9. 특이점 (Quirks)

SQLite는 U+0000을 문자열 종결자로 사용해요. 그 문자를 객체 라벨의 중간에 임베딩하면, PATH 비교는 U+0000 앞에 오는 접두사만 보게 돼요.

SQLite는 PATH에서 JSON 문자열 구문을 엄격히 강제하지 않아요. json_set() 또는 유사한 함수를 사용해 객체에 새 요소를 추가할 때 PATH 인자에 JSON에서 유효하지 않은 역슬래시 이스케이프가 포함되어 있으면, SQLite는 그렇게 하는 것을 막으려 하지 않으며 잘못된 JSON으로 끝날 수 있어요.

4. 함수 상세 (Function Details)

다음 섹션들은 다양한 JSON 함수와 연산자의 동작에 대한 추가 세부 사항을 제공해요.

4.1. json() 함수

json(X) 함수는 인자 X가 유효한 JSON 문자열 또는 JSONB blob인지 검증하고, 불필요한 모든 공백이 제거된 그 JSON 문자열의 minified 버전을 반환해요. X가 잘 구성된 JSON 문자열 또는 JSONB blob이 아니면 이 루틴은 오류를 던져요.

입력이 JSON5 텍스트이면 반환되기 전에 정식 RFC-8259 텍스트로 변환돼요.

json(X)에 대한 인자 X가 중복 라벨을 가진 JSON 객체를 포함하면, 중복이 보존되는지 여부는 정의되지 않아요. 현재 구현은 중복을 보존해요. 하지만 이 루틴의 향후 향상은 중복을 조용히 제거하도록 선택할 수도 있어요.

예시:

json(' { "this" : "is", "a": [ "test" ] } ') → '{"this":"is","a":["test"]}'

4.2. jsonb() 함수

jsonb(X) 함수는 인자 X로 제공된 JSON의 이진 JSONB 표현을 반환해요. X가 유효한 JSON 구문이 없는 TEXT이면 오류가 발생해요.

X가 BLOB이고 JSONB처럼 보이면 이 루틴은 단순히 X의 복사본을 반환해요. 하지만 JSONB 입력의 가장 바깥 요소만 조사돼요. JSONB의 깊은 구조는 검증되지 않아요.

4.3. json_array() 함수

json_array() SQL 함수는 0개 이상의 인자를 받아들이고 그 인자들로 구성된 잘 구성된 JSON 배열을 반환해요. json_array()에 대한 어떤 인자가 BLOB이면 오류가 던져져요.

SQL 타입이 TEXT인 인자는 보통 따옴표 붙은 JSON 문자열로 변환돼요. 하지만 인자가 다른 json1 함수의 출력이면 JSON으로 저장돼요. 이렇게 하면 json_array()와 json_object() 호출을 중첩할 수 있어요. json() 함수를 사용해 문자열이 JSON으로 인식되도록 강제할 수도 있어요.

예시:

json_array(1,2,'3',4) → '[1,2,"3",4]'
json_array('[1,2]') → '["[1,2]"]'
json_array(json_array(1,2)) → '[[1,2]]'
json_array(1,null,'3','[4,5]','{"six":7.7}') → '[1,null,"3","[4,5]","{\"six\":7.7}"]'
json_array(1,null,'3',json('[4,5]'),json('{"six":7.7}')) → '[1,null,"3",[4,5],{"six":7.7}]'

4.4. jsonb_array() 함수

jsonb_array() SQL 함수는 표준 RFC 8259 텍스트 형식 대신 SQLite의 개인 JSONB 형식으로 구성된 JSON 배열을 반환한다는 점을 제외하면 json_array() 함수와 똑같이 작동해요.

4.5. json_array_insert()와 jsonb_array_insert() 함수

json_array_insert()와 jsonb_array_insert() SQL 함수는 json_replace()와 jsonb_replace 함수와 비슷하게 작동하며, 단지 두 가지 차이점이 있어요:

PATH 인자는 배열의 요소를 참조해야 해요. 어떤 PATH 인자도 배열 요소 참조로 끝나지 않으면 (PATH가 "[…]"로 끝나지 않으면) 오류가 발생해요.
배열의 기존 요소를 덮어쓰는 대신 배열이 확대되고 PATH 인자가 지정한 위치에 새 요소가 삽입돼요.

json_replace()와 jsonb_replace()와 마찬가지로 PATH와 VALUE 인자는 여러 번 반복될 수 있어요. 수정된 JSON이 반환되는데, json_array_insert()의 경우 텍스트 형식으로, jsonb_array_insert()의 경우 이진 BLOB 형식으로 반환돼요.

예시:

json_array_insert('[1,2,3]','$[1]','new') → [1,"new",2,3]
json_array_insert('{"a":[1,2,3]}', '$.a[0]','new') → {"a":["new",1,2,3]}

4.6. json_array_length() 함수

json_array_length(X) 함수는 JSON 배열 X의 요소 수를 반환하고, X가 배열이 아닌 다른 종류의 JSON 값이면 0을 반환해요. json_array_length(X,P)는 X 안의 경로 P에 있는 배열을 찾아 그 배열의 길이를 반환하고, 경로 P가 X에서 배열이 아닌 요소를 찾으면 0을, 경로 P가 X의 어떤 요소도 찾지 못하면 NULL을 반환해요. X가 잘 구성된 JSON이 아니거나 P가 잘 구성된 경로가 아니면 오류가 던져져요.

예시:

json_array_length('[1,2,3,4]') → 4
json_array_length('[1,2,3,4]', '$') → 4
json_array_length('[1,2,3,4]', '$[2]') → 0
json_array_length('{"one":[1,2,3]}') → 0
json_array_length('{"one":[1,2,3]}', '$.one') → 3
json_array_length('{"one":[1,2,3]}', '$.two') → NULL

4.7. json_error_position() 함수

json_error_position(X) 함수는 입력 X가 잘 구성된 JSON 또는 JSON5 문자열이면 0을 반환해요. 입력 X에 하나 이상의 구문 오류가 있으면 이 함수는 첫 번째 구문 오류의 문자 위치를 반환해요. 가장 왼쪽 문자가 위치 1이에요.

입력 X가 BLOB이면 X가 잘 구성된 JSONB blob일 때 이 루틴은 0을 반환해요. 반환 값이 양수이면 첫 번째 감지된 오류의 BLOB에서의 대략적인 1 기반 위치를 나타내요.

json_error_position() 함수는 SQLite 버전 3.42.0 (2023-05-16)에서 추가되었어요.

4.8. json_extract() 함수

json_extract(X,P1,P2,...)는 X에서 잘 구성된 JSON에서 하나 이상의 값을 추출해 반환해요. 단일 경로 P1만 제공되면 결과의 SQL 데이터타입은 JSON null에 대해 NULL, JSON 숫자 값에 대해 INTEGER 또는 REAL, JSON false 값에 대해 INTEGER 0, JSON true 값에 대해 INTEGER 1, JSON 문자열 값에 대해 따옴표가 제거된 텍스트, JSON 객체와 배열 값에 대해 텍스트 표현이에요. 여러 경로 인자(P1, P2 등)가 있으면 이 루틴은 각 값을 담은 잘 구성된 JSON 배열인 SQLite 텍스트를 반환해요.

예시:

json_extract('{"a":2,"c":[4,5,{"f":7}]}', '$') → '{"a":2,"c":[4,5,{"f":7}]}'
json_extract('{"a":2,"c":[4,5,{"f":7}]}', '$.c') → '[4,5,{"f":7}]'
json_extract('{"a":2,"c":[4,5,{"f":7}]}', '$.c[2]') → '{"f":7}'
json_extract('{"a":2,"c":[4,5,{"f":7}]}', '$.c[2].f') → 7
json_extract('{"a":2,"c":[4,5],"f":7}','$.c','$.a') → '[[4,5],2]'
json_extract('{"a":2,"c":[4,5],"f":7}','$.c[#-1]') → 5
json_extract('{"a":2,"c":[4,5,{"f":7}]}', '$.x') → NULL
json_extract('{"a":2,"c":[4,5,{"f":7}]}', '$.x', '$.a') → '[null,2]'
json_extract('{"a":"xyz"}', '$.a') → 'xyz'
json_extract('{"a":null}', '$.a') → NULL

SQLite의 json_extract() 함수와 MySQL의 json_extract() 함수 사이에는 미묘한 비호환성이 있어요. MySQL 버전의 json_extract()는 항상 JSON을 반환해요. SQLite 버전의 json_extract()는 두 개 이상의 PATH 인자가 있거나(결과가 JSON 배열이 되므로) 단일 PATH 인자가 배열이나 객체를 참조할 때만 JSON을 반환해요. SQLite에서 json_extract()가 단일 PATH 인자만 있고 그 PATH가 JSON null, 문자열 또는 숫자 값을 참조하면, json_extract()는 해당하는 SQL NULL, TEXT, INTEGER 또는 REAL 값을 반환해요.

MySQL json_extract()와 SQLite json_extract()의 차이는 JSON 내에서 문자열 또는 NULL인 개별 값에 접근할 때만 실제로 두드러져요. 다음 표가 그 차이를 보여줘요:

Operation	SQLite Result	MySQL Result
`json_extract('{"a":null,"b":"xyz"}','$.a')`	NULL	`'null'`
`json_extract('{"a":null,"b":"xyz"}','$.b')`	`'xyz'`	`'"xyz"'`

4.9. jsonb_extract() 함수

jsonb_extract() 함수는 json_extract() 함수와 같게 작동하는데, json_extract()가 보통 텍스트 JSON 배열 객체를 반환하는 경우에 이 루틴은 배열이나 객체를 JSONB 형식으로 반환한다는 점만 달라요. 텍스트, 숫자, null 또는 부울 JSON 요소가 반환되는 일반적인 경우에는 이 루틴이 json_extract()와 정확히 같게 작동해요.

4.10. -> 및 ->> 연산자

SQLite 버전 3.38.0 (2022-02-22)부터 JSON의 하위 구성 요소를 추출하기 위해 -> 및 ->> 연산자를 사용할 수 있어요. SQLite의 -> 및 ->> 구현은 MySQL과 PostgreSQL 모두와 호환되도록 노력해요. -> 및 ->> 연산자는 왼쪽 피연산자로 JSON 문자열 또는 JSONB blob을, 오른쪽 피연산자로 PATH 표현식 또는 객체 필드 라벨 또는 배열 인덱스를 받아요. -> 연산자는 선택된 하위 구성 요소의 텍스트 JSON 표현을 반환하거나, 그 하위 구성 요소가 존재하지 않으면 NULL을 반환해요. ->> 연산자는 선택된 하위 구성 요소를 나타내는 SQL TEXT, INTEGER, REAL 또는 NULL 값을 반환하거나, 하위 구성 요소가 존재하지 않으면 NULL을 반환해요.

->와 ->> 연산자 모두 왼쪽 JSON의 같은 하위 구성 요소를 선택해요. 차이는 ->는 항상 그 하위 구성 요소의 JSON 표현을 반환하고 ->> 연산자는 항상 그 하위 구성 요소의 SQL 표현을 반환한다는 점이에요. 따라서 이 연산자들은 두 인자 json_extract() 함수 호출과 미묘하게 다르다는 뜻이에요. 두 인자로 json_extract()를 호출하면 하위 구성 요소가 JSON 배열이나 객체일 때에만 그 하위 구성 요소의 JSON 표현을 반환하고, 하위 구성 요소가 JSON null, 문자열 또는 숫자 값이면 하위 구성 요소의 SQL 표현을 반환해요.

-> 연산자가 JSON을 반환할 때 항상 그 JSON의 RFC 8565 텍스트 표현을 반환하며 JSONB는 아니에요. JSONB 형식의 하위 구성 요소가 필요하면 jsonb_extract() 함수를 사용하세요.

-> 및 ->> 연산자의 오른쪽 피연산자는 잘 구성된 JSON 경로 표현식일 수 있어요. 이것은 MySQL이 사용하는 형태예요. PostgreSQL과의 호환성을 위해 -> 및 ->> 연산자는 영숫자 텍스트 객체 라벨 또는 정수 배열 인덱스도 오른쪽 피연산자로 받아들여요. 오른쪽 피연산자가 영숫자 텍스트 라벨 X이면 JSON 경로 '$.X'로 해석돼요. 오른쪽 피연산자가 정수 값 N이면 음이 아닌 경우 JSON 경로 '$[N]'로 해석돼요. 또는 N이 값 -K인 음의 정수이면 JSON 경로 '$[#-K]'처럼 해석돼요. 다시 말해 인덱싱이 배열의 끝에서 시작해 앞쪽으로 이동해요. N의 음수 값은 SQLite 버전 3.47.0 (2024-10-21) 이상에서만 지원돼요.

예시:

'{"a":2,"c":[4,5,{"f":7}]}' -> '$' → '{"a":2,"c":[4,5,{"f":7}]}'
'{"a":2,"c":[4,5,{"f":7}]}' -> '$.c' → '[4,5,{"f":7}]'
'{"a":2,"c":[4,5,{"f":7}]}' -> 'c' → '[4,5,{"f":7}]'
'{"a":2,"c":[4,5,{"f":7}]}' -> '$.c[2]' → '{"f":7}'
'{"a":2,"c":[4,5,{"f":7}]}' -> '$.c[2].f' → '7'
'{"a":2,"c":[4,5,{"f":7}]}' ->> '$.c[2].f' → 7
'{"a":2,"c":[4,5,{"f":7}]}' -> 'c' -> 2 ->> 'f' → 7
'{"a":2,"c":[4,5],"f":7}' -> '$.c[#-1]' → '5'
'{"a":2,"c":[4,5,{"f":7}]}' -> '$.x' → NULL
'[11,22,33,44]' -> 3 → '44'
'[11,22,33,44]' ->> 3 → 44
'{"a":"xyz"}' -> '$.a' → '"xyz"'
'{"a":"xyz"}' ->> '$.a' → 'xyz'
'{"a":null}' -> '$.a' → 'null'
'{"a":null}' ->> '$.a' → NULL

4.11. json_insert(), json_replace, json_set() 함수

json_insert(), json_replace, json_set() 함수는 모두 첫 번째 인자로 단일 JSON 값을 받고 그 뒤에 0개 이상의 경로와 값 인자 쌍을 받아, 경로/값 쌍으로 입력 JSON을 업데이트해 형성된 새 JSON 문자열을 반환해요. 함수들은 새 값을 만들고 기존 값을 덮어쓰는 방식에서만 다르게 동작해요.

Function	Overwrite if already exists?	Create if does not exist?
`json_insert()`	No	Yes
`json_replace()`	Yes	No
`json_set()`	Yes	Yes

json_insert(), json_replace(), json_set() 함수는 항상 홀수 개의 인자를 받아요. 첫 번째 인자는 항상 편집할 원래 JSON이에요. 이후 인자는 쌍으로 나타나며 각 쌍의 첫 번째 요소는 경로이고 두 번째 요소는 그 경로에 삽입/교체/설정할 값이에요.

편집은 왼쪽에서 오른쪽으로 순차적으로 발생해요. 이전 편집으로 인한 변경은 이후 편집의 경로 검색에 영향을 줄 수 있어요.

경로/값 쌍의 값이 SQLite TEXT 값이면, 문자열이 유효한 JSON처럼 보여도 보통 따옴표 붙은 JSON 문자열로 삽입돼요. 하지만 값이 다른 json 함수(json(), json_array(), json_object() 등)의 결과이거나 -> 연산자의 결과이면 JSON으로 해석되어 모든 하위 구조를 유지한 채 JSON으로 삽입돼요. ->> 연산자의 결과인 값은 항상 TEXT로 해석되어 유효한 JSON처럼 보여도 JSON 문자열로 삽입돼요.

이 루틴들은 첫 번째 JSON 인자가 잘 구성되지 않았거나 어떤 PATH 인자도 잘 구성되지 않았거나 어떤 인자가 BLOB이면 오류를 던져요.

배열의 끝에 요소를 추가하려면 배열 인덱스 "#"로 json_insert()를 사용해요. 예시:

json_insert('[1,2,3,4]','$[#]',99) → '[1,2,3,4,99]'
json_insert('[1,[2,3],4]','$[1][#]',99) → '[1,[2,3,99],4]'

배열의 시작이나 중간에 요소를 삽입하려면 json_array_insert() 함수를 사용하세요.

다른 예시:

json_insert('{"a":2,"c":4}', '$.a', 99) → '{"a":2,"c":4}'
json_insert('{"a":2,"c":4}', '$.e', 99) → '{"a":2,"c":4,"e":99}'
json_replace('{"a":2,"c":4}', '$.a', 99) → '{"a":99,"c":4}'
json_replace('{"a":2,"c":4}', '$.e', 99) → '{"a":2,"c":4}'
json_set('{"a":2,"c":4}', '$.a', 99) → '{"a":99,"c":4}'
json_set('{"a":2,"c":4}', '$.e', 99) → '{"a":2,"c":4,"e":99}'
json_set('{"a":2,"c":4}', '$.c', '[97,96]') → '{"a":2,"c":"[97,96]"}'
json_set('{"a":2,"c":4}', '$.c', json('[97,96]')) → '{"a":2,"c":[97,96]}'
json_set('{"a":2,"c":4}', '$.c', json_array(97,96)) → '{"a":2,"c":[97,96]}'

4.12. jsonb_insert(), jsonb_replace, jsonb_set() 함수

jsonb_insert(), jsonb_replace(), jsonb_set() 함수는 각각 json_insert(), json_replace(), json_set()과 같게 작동하는데, "jsonb_" 버전이 결과를 이진 JSONB 형식으로 반환한다는 점만 달라요.

4.13. json_object() 함수

json_object() SQL 함수는 0개 이상의 인자 쌍을 받아들이고 그 인자들로 구성된 잘 구성된 JSON 객체를 반환해요. 각 쌍의 첫 번째 인자는 라벨이고 각 쌍의 두 번째 인자는 값이에요. json_object()에 대한 어떤 인자가 BLOB이면 오류가 던져져요.

json_object() 함수는 현재 불평 없이 중복 라벨을 허용하지만, 이것은 향후 향상에서 바뀔 수도 있어요.

SQL 타입이 TEXT인 인자는 입력 텍스트가 잘 구성된 JSON이라도 보통 따옴표 붙은 JSON 문자열로 변환돼요. 하지만 인자가 다른 JSON 함수나 -> 연산자(->> 연산자는 아님)의 직접 결과이면 JSON으로 취급되고 그 모든 JSON 타입 정보와 하위 구조가 보존돼요. 이렇게 하면 json_object()와 json_array() 호출을 중첩할 수 있어요. json() 함수를 사용해 문자열이 JSON으로 인식되도록 강제할 수도 있어요.

예시:

json_object('a',2,'c',4) → '{"a":2,"c":4}'
json_object('a',2,'c','{e:5}') → '{"a":2,"c":"{e:5}"}'
json_object('a',2,'c',json_object('e',5)) → '{"a":2,"c":{"e":5}}'

4.14. jsonb_object() 함수

jsonb_object() 함수는 생성된 객체가 이진 JSONB 형식으로 반환된다는 점을 제외하면 json_object() 함수와 똑같이 작동해요.

4.15. json_patch() 함수

json_patch(T,P) SQL 함수는 입력 T에 패치 P를 적용하기 위해 RFC-7396 MergePatch 알고리즘을 실행해요. 패치된 T의 복사본이 반환돼요.

MergePatch는 JSON 객체의 요소를 추가, 수정 또는 삭제할 수 있으므로, JSON 객체에 대해 json_patch() 루틴은 json_set()과 json_remove()의 일반화된 대체물이에요. 하지만 MergePatch는 JSON 배열 객체를 원자적으로 취급해요. MergePatch는 배열에 추가할 수 없고 배열의 개별 요소를 수정할 수도 없어요. 전체 배열을 단일 단위로 삽입, 교체 또는 삭제할 수만 있어요. 따라서 json_patch()는 배열, 특히 많은 하위 구조를 가진 배열을 포함하는 JSON을 다룰 때는 그렇게 유용하지 않아요.

예시:

json_patch('{"a":1,"b":2}','{"c":3,"d":4}') → '{"a":1,"b":2,"c":3,"d":4}'
json_patch('{"a":[1,2],"b":2}','{"a":9}') → '{"a":9,"b":2}'
json_patch('{"a":[1,2],"b":2}','{"a":null}') → '{"b":2}'
json_patch('{"a":1,"b":2}','{"a":9,"b":null,"c":8}') → '{"a":9,"c":8}'
json_patch('{"a":{"x":1,"y":2},"b":3}','{"a":{"y":9},"c":8}') → '{"a":{"x":1,"y":9},"b":3,"c":8}'

4.16. jsonb_patch() 함수

jsonb_patch() 함수는 패치된 JSON이 이진 JSONB 형식으로 반환된다는 점을 제외하면 json_patch() 함수와 똑같이 작동해요.

4.17. json_pretty() 함수

json_pretty() 함수는 JSON 결과를 사람이 읽기 더 쉽게 하기 위해 추가 공백을 추가한다는 점을 제외하면 json()처럼 작동해요. 첫 번째 인자는 pretty-print될 JSON 또는 JSONB예요. 선택적 두 번째 인자는 들여쓰기에 사용되는 텍스트 문자열이에요. 두 번째 인자가 생략되거나 NULL이면 들여쓰기는 수준당 네 칸이에요.

json_pretty() 함수는 SQLite 버전 3.46.0 (2024-05-23)에서 추가되었어요.

4.18. json_remove() 함수

json_remove(X,P,...) 함수는 첫 번째 인자로 단일 JSON 값을 받고 그 뒤에 0개 이상의 경로 인자를 받아요. json_remove(X,P,...) 함수는 경로 인자들이 식별한 모든 요소가 제거된 X 매개변수의 복사본을 반환해요. X에서 찾을 수 없는 요소를 선택하는 경로는 조용히 무시돼요.

제거는 왼쪽에서 오른쪽으로 순차적으로 발생해요. 이전 제거로 인한 변경은 이후 인자의 경로 검색에 영향을 줄 수 있어요.

json_remove(X) 함수가 경로 인자 없이 호출되면 입력 X를 과도한 공백을 제거하여 재형식화한 것을 반환해요.

json_remove() 함수는 첫 번째 인자가 잘 구성된 JSON이 아니거나 이후 어떤 인자도 잘 구성된 경로가 아니면 오류를 던져요.

예시:

json_remove('[0,1,2,3,4]','$[2]') → '[0,1,3,4]'
json_remove('[0,1,2,3,4]','$[2]','$[0]') → '[1,3,4]'
json_remove('[0,1,2,3,4]','$[0]','$[2]') → '[1,2,4]'
json_remove('[0,1,2,3,4]','$[#-1]','$[0]') → '[1,2,3]'
json_remove('{"x":25,"y":42}') → '{"x":25,"y":42}'
json_remove('{"x":25,"y":42}','$.z') → '{"x":25,"y":42}'
json_remove('{"x":25,"y":42}','$.y') → '{"x":25}'
json_remove('{"x":25,"y":42}','$') → NULL

4.19. jsonb_remove() 함수

jsonb_remove() 함수는 편집된 JSON 결과가 이진 JSONB 형식으로 반환된다는 점을 제외하면 json_remove() 함수와 똑같이 작동해요.

4.20. json_type() 함수

json_type(X) 함수는 X의 가장 바깥 요소의 "type"을 반환해요. json_type(X,P) 함수는 경로 P가 선택한 X의 요소의 "type"을 반환해요. json_type()이 반환하는 "type"은 'null', 'true', 'false', 'integer', 'real', 'text', 'array', 'object' 중 하나인 SQL 텍스트 값이에요. json_type(X,P)의 경로 P가 X에 존재하지 않는 요소를 선택하면 이 함수는 NULL을 반환해요.

json_type() 함수는 첫 번째 인자가 잘 구성된 JSON 또는 JSONB가 아니거나 두 번째 인자가 잘 구성된 JSON 경로가 아니면 오류를 던져요.

예시:

json_type('{"a":[2,3.5,true,false,null,"x"]}') → 'object'
json_type('{"a":[2,3.5,true,false,null,"x"]}','$') → 'object'
json_type('{"a":[2,3.5,true,false,null,"x"]}','$.a') → 'array'
json_type('{"a":[2,3.5,true,false,null,"x"]}','$.a[0]') → 'integer'
json_type('{"a":[2,3.5,true,false,null,"x"]}','$.a[1]') → 'real'
json_type('{"a":[2,3.5,true,false,null,"x"]}','$.a[2]') → 'true'
json_type('{"a":[2,3.5,true,false,null,"x"]}','$.a[3]') → 'false'
json_type('{"a":[2,3.5,true,false,null,"x"]}','$.a[4]') → 'null'
json_type('{"a":[2,3.5,true,false,null,"x"]}','$.a[5]') → 'text'
json_type('{"a":[2,3.5,true,false,null,"x"]}','$.a[6]') → NULL

4.21. json_valid() 함수

json_valid(X,Y) 함수는 인자 X가 잘 구성된 JSON이면 1을 반환하고, X가 잘 구성되지 않았으면 0을 반환해요. Y 매개변수는 "잘 구성됨"이 무엇을 의미하는지 정의하는 정수 비트마스크예요. Y의 다음 비트들이 현재 정의되어 있어요:

0x01 → 입력은 확장 없이 정식 RFC-8259 JSON을 엄격히 따르는 텍스트.
0x02 → 입력은 위에서 설명한 JSON5 확장을 가진 JSON인 텍스트.
0x04 → 입력은 표면적으로 JSONB처럼 보이는 BLOB.
0x08 → 입력은 내부 JSONB 형식을 엄격히 따르는 BLOB.

비트를 결합함으로써 다음의 유용한 Y 값들을 유도할 수 있어요:

1 → X는 RFC-8259 JSON 텍스트
2 → X는 JSON5 텍스트
4 → X는 아마도 JSONB
5 → X는 RFC-8259 JSON 텍스트 또는 JSONB
6 → X는 JSON5 텍스트 또는 JSONB ← 아마 이것이 여러분이 원하는 값일 거예요
8 → X는 엄격히 따르는 JSONB
9 → X는 RFC-8259 또는 엄격히 따르는 JSONB
10 → X는 JSON5 또는 엄격히 따르는 JSONB

Y 매개변수는 선택사항이에요. 생략되면 기본값 1로, 기본 동작은 입력 X가 확장 없이 엄격히 RFC-8259 JSON 텍스트를 따를 때만 true를 반환한다는 뜻이에요. 이로 인해 한 인자 버전의 json_valid()가 JSON5와 JSONB 지원이 추가되기 전의 SQLite 이전 버전과 호환되게 해요.

Y 매개변수의 0x04와 0x08 비트의 차이는, 0x04가 BLOB의 외부 래퍼만 조사해 표면적으로 JSONB처럼 보이는지 보는 것이라는 점이에요. 이것은 대부분의 목적에 충분하고 매우 빠르며, 0x08 비트는 BLOB의 모든 내부 세부 사항을 철저히 조사해요. 0x08 비트는 X 입력의 크기에 선형인 시간이 걸리며 훨씬 느려요. 대부분의 목적에는 0x04 비트가 권장돼요.

값이 다른 JSON 함수 중 하나에 그럴듯한 입력인지 그냥 알고 싶다면, Y 값 6이 아마 사용하고 싶은 값이에요.

최신 버전의 json_valid()에서 1보다 작거나 15보다 큰 Y 값은 오류를 발생시켜요. 하지만 향후 버전의 json_valid()는 이 범위 밖의 플래그 값을 받아들이도록 향상될 수 있으며, 우리가 아직 생각하지 못한 새로운 의미를 가질 수도 있어요.

json_valid()에 대한 X 또는 Y 입력 중 하나가 NULL이면 함수는 NULL을 반환해요.

예시:

json_valid('{"x":35}') → 1
json_valid('{x:35}') → 0
json_valid('{x:35}',6) → 1
json_valid('{"x":35') → 0
json_valid(NULL) → NULL

4.22. json_quote() 함수

json_quote(X) 함수는 SQL 값 X(숫자 또는 문자열)를 그에 해당하는 JSON 표현으로 변환해요. X가 다른 JSON 함수가 반환한 JSON 값이면 이 함수는 no-op이에요.

예시:

json_quote(3.14159) → '3.14159'
json_quote('verdant') → '"verdant"'
json_quote(NULL) → 'null'
json_quote('[1]') → '"[1]"'
json_quote(json('[1]')) → '[1]'
json_quote('[1,') → '"[1,"'

4.23. 배열 및 객체 집계 함수 (Array and object aggregate functions)

json_group_array(X) 함수는 집계의 모든 X 값으로 구성된 JSON 배열을 반환하는 집계 SQL 함수 (aggregate SQL function)예요. 유사하게 json_group_object(NAME,VALUE) 함수는 집계의 모든 NAME/VALUE 쌍으로 구성된 JSON 객체를 반환해요. "jsonb_" 변형들은 결과를 이진 JSONB 형식으로 반환한다는 점만 빼고 동일해요.

이 모든 함수들은 유효한 입력 행이 없어도 유효한 JSON 또는 JSONB 객체 또는 배열을 반환해요. 유효한 입력 행이 없으면 결과 객체 또는 배열은 비어 있어요.

json_group_object()와 jsonb_group_object() 함수는 NAME 인자가 NULL인 입력 행을 무시해요. json_group_array()와 jsonb_group_array() 함수는 NULL 입력을 포함해 모든 입력을 결과 배열에 포함해요.

4.24. JSON 파싱을 위한 테이블 값 함수: json_each(), jsonb_each(), json_tree(), jsonb_tree()

json_each(X), jsonb_each(X), json_tree(X), jsonb_tree(X) 테이블 값 함수 (table-valued functions)는 모두 첫 번째 인자로 제공된 JSON 값을 탐색하며 각 요소마다 한 행을 반환해요. json_each(X)와 jsonb_each(X) 함수는 최상위 배열 또는 객체의 바로 아래 자식들만 탐색하거나, 최상위 요소가 기본 값이면 최상위 요소 자체만 탐색해요. json_tree(X)와 jsonb_tree(X) 함수는 최상위 요소에서 시작해 JSON 하위 구조를 재귀적으로 탐색해요.

json_each(X,P), jsonb_each(X,P), json_tree(X,P), jsonb_tree(X,P) 함수는 경로 P가 식별한 요소를 최상위 요소로 취급한다는 점을 제외하면 한 인자 대응 함수들과 똑같이 작동해요.

jsonb_each()와 jsonb_tree() 변형들은 SQLite 버전 3.51.0 (2025-11-04)부터 사용 가능해요. 이 두 변형들은 "type" 컬럼이 'object' 또는 'array'일 때 "value" 컬럼이 텍스트 JSON 대신 JSONB를 반환한다는 점을 제외하면 "b"가 없는 대응 함수들과 같게 작동해요.

다음 표는 다양한 JSON 테이블 값 함수들 사이의 차이를 요약해요:

	json_each()	jsonb_each()	json_tree()	jsonb_tree()
입력 JSON의 하위 구조를 재귀적으로 탐색하거나 단일 계층만 파싱	single layer	single layer	recursive	recursive
'array'와 'object'에 대해 "value" 컬럼이 반환하는 것	text	JSONB	text	JSONB
지원 시작 SQLite 버전	3.9.0 (2015-10-14)	3.51.0 (2025-11-04)	3.9.0 (2015-10-14)	3.51.0 (2025-11-04)

모든 JSON 테이블 값 함수가 반환하는 테이블의 스키마는 다음과 같아요:

CREATE TABLE json_tree(
    key ANY,             -- key for current element relative to its parent
    value ANY,           -- value for the current element
    type TEXT,           -- 'object','array','string','integer', etc.
    atom ANY,            -- value for primitive types, null for array & object
    id INTEGER,          -- integer ID for this element
    parent INTEGER,      -- integer ID for the parent of this element
    fullkey TEXT,        -- full path describing the current element
    path TEXT,           -- path to the container of the current row
    json JSON HIDDEN,    -- 1st input parameter: the raw JSON
    root TEXT HIDDEN     -- 2nd input parameter: the PATH at which to start
);

"key" 컬럼은 JSON 배열의 요소에 대한 정수 배열 인덱스이고 JSON 객체의 요소에 대한 텍스트 라벨이에요. 다른 모든 경우에 key 컬럼은 NULL이에요.

"value" 컬럼은 "key" 또는 "fullkey"가 지정하는 JSON 요소에 대한 SQL 값이에요. 요소의 "type"이 'null', 'true', 'false', 'integer', 'real', 'text' 중 하나이면 그 요소의 해당 SQL 값이 "value"로 반환돼요. "type"이 'array' 또는 'object'일 때 "value" 컬럼은 json_each()와 json_tree()에서는 배열이나 객체의 텍스트 JSON을 반환하고, jsonb_each() 또는 jsonb_tree()의 경우에는 배열이나 객체의 JSONB를 반환해요. "value" 컬럼의 형식이 json_each()와 jsonb_each() 사이, 그리고 json_tree()와 jsonb_tree() 사이의 유일한 동작 차이예요.

"type" 컬럼은 현재 JSON 요소의 타입에 따라 ('null', 'true', 'false', 'integer', 'real', 'text', 'array', 'object')에서 가져온 SQL 텍스트 값이에요.

"atom" 컬럼은 기본 요소 - JSON 배열과 객체 이외의 요소 - 에 해당하는 SQL 값이에요. "atom" 컬럼은 JSON 배열 또는 객체에 대해 NULL이에요.

"id" 컬럼은 완전한 JSON 문자열 내에서 특정 JSON 요소를 식별하는 정수예요. "id" 정수는 내부 관리 번호이며, 그 계산은 향후 릴리스에서 바뀔 수 있어요. 유일한 보장은 "id" 컬럼이 모든 행에 대해 다르다는 것이에요.

"parent" 컬럼은 json_each()에 대해 항상 NULL이에요. json_tree()의 경우 "parent" 컬럼은 현재 요소의 부모에 대한 "id" 정수이거나, 최상위 JSON 요소 또는 두 번째 인자의 루트 경로가 식별한 요소에 대해 NULL이에요.

"fullkey" 컬럼은 원래 JSON 문자열 내에서 현재 행 요소를 고유하게 식별하는 텍스트 경로예요. "root" 인자로 대체 시작점이 제공되어도 실제 최상위 요소에 대한 완전한 키가 반환돼요.

"path" 컬럼은 현재 행을 담고 있는 배열 또는 객체 컨테이너에 대한 경로이거나, 반복이 기본 타입에서 시작해 단일 출력 행만 제공하는 경우 현재 행에 대한 경로예요.

"json"과 "root"라는 두 개의 숨겨진 컬럼은 가상 테이블에 대한 입력 전용이에요. 조인 제약, 테이블 값 함수 (table-valued function) 구문이 자동으로 구성하는 암시적 조인 제약을 포함해, 조인 제약에 사용하세요. "json" 또는 "root" 컬럼의 값을 반환하려고 하면 신뢰할 수 있는 답을 얻지 못할 수도 있어요.

4.24.1. json_each()와 json_tree() 사용 예시

테이블 "CREATE TABLE user(name,phone)"이 user.phone 필드에 0개 이상의 전화번호를 JSON 배열 객체로 저장한다고 가정해 봅시다. 704 지역 번호를 가진 전화번호가 있는 모든 사용자를 찾으려면:

SELECT DISTINCT user.name
  FROM user, json_each(user.phone)
 WHERE json_each.value LIKE '704-%';

이제 user.phone 필드가 사용자가 단일 전화번호만 있으면 일반 텍스트를, 여러 전화번호가 있으면 JSON 배열을 포함한다고 가정해 봅시다. "704 지역 번호에 전화번호가 있는 사용자는 누구인가?"라는 같은 질문을 합니다. 하지만 이제 json_each() 함수가 첫 번째 인자로 잘 구성된 JSON을 요구하므로 json_each() 함수는 두 개 이상의 전화번호가 있는 사용자에 대해서만 호출될 수 있어요:

SELECT name FROM user WHERE phone LIKE '704-%'
UNION
SELECT user.name
  FROM user, json_each(user.phone)
 WHERE json_valid(user.phone)
   AND json_each.value LIKE '704-%';

"CREATE TABLE big(json JSON)"이 있는 다른 데이터베이스를 고려해 봅시다. 데이터의 완전한 줄별 분해를 보려면:

SELECT big.rowid, fullkey, value
  FROM big, json_tree(big.json)
 WHERE json_tree.type NOT IN ('object','array');

앞에서 WHERE 절의 "type NOT IN ('object','array')" 항은 컨테이너를 억제하고 잎 요소만 통과시켜요. 같은 효과를 이렇게 얻을 수도 있어요:

SELECT big.rowid, fullkey, atom
  FROM big, json_tree(big.json)
 WHERE atom IS NOT NULL;

BIG 테이블의 각 항목이 고유 식별자인 '$.id' 필드와 깊게 중첩된 객체일 수 있는 '$.partlist' 필드를 가진 JSON 객체라고 가정해 봅시다. '$.partlist' 어디에든 uuid '6fa5181e-5721-11e5-a04e-57f3d7b32808'에 대한 참조를 하나 이상 포함하는 모든 항목의 id를 찾고 싶어요.

SELECT DISTINCT json_extract(big.json,'$.id')
  FROM big, json_tree(big.json, '$.partlist')
 WHERE json_tree.key='uuid'
   AND json_tree.value='6fa5181e-5721-11e5-a04e-57f3d7b32808';