JSON 함수와 연산자
JSON 함수와 연산자 (JSON functions and operators)
이 문서는 Trino의 JSON 함수와 연산자를 설명합니다. SQL 표준 기반의 JSON 데이터 조회(json_exists, json_query, json_value), 생성(json_array, json_object), 그리고 JSON 경로 언어와 캐스팅까지 배울 수 있어요.
출처: 문서
본문
SQL 표준은 JSON 데이터를 처리하는 함수와 연산자를 설명합니다. 이들을 사용하면 구조에 따라 JSON 데이터에 접근하고, JSON 데이터를 생성하고, SQL 테이블에 영구적으로 저장할 수 있습니다.
중요한 점은 SQL 표준이 SQL에서 JSON 데이터를 표현하는 전용 데이터 유형을 부과하지 않는다는 것입니다. 대신 JSON 데이터는 문자 또는 바이너리 문자열로 표현됩니다. Trino가 JSON 유형을 지원하지만, 다음 함수들은 그것을 사용하거나 생성하지 않습니다.
Trino는 JSON 데이터를 조회하는 세 가지 함수를 지원합니다: json_exists, json_query, json_value. 각 함수는 JSON 경로를 사용해 JSON 입력을 탐색하고 처리하는 같은 메커니즘에 기반합니다.
JSON 유형 컬럼의 편리한 탐색을 위해 Trino는 점/첨자 표기법을 사용해 JSON 값에 접근할 수 있는 JSON 단순 접근자 문법도 지원합니다.
Trino는 JSON 데이터를 생성하는 두 가지 함수, json_array와 json_object도 지원합니다.
JSON 경로 언어 (JSON path language)
JSON 경로 언어는 특정 SQL 연산자가 JSON 입력에 대해 수행할 쿼리를 지정하기 위해 독점적으로 사용하는 특별한 언어입니다. JSON 경로 표현식은 SQL 쿼리에 포함되지만 문법은 SQL과 크게 다릅니다. JSON 경로 표현식의 조건, 연산자 등의 의미는 일반적으로 SQL의 의미를 따릅니다. JSON 경로 언어는 키워드와 식별자에 대해 대소문자를 구분합니다.
JSON 경로 문법과 의미 (JSON path syntax and semantics)
JSON 경로 표현식은 재귀 구조입니다. "path"라는 이름이 JSON 구조 안으로 단계별로 들어가는 선형 순서를 암시하지만, JSON 경로 표현식은 사실 트리입니다. 입력 JSON 항목에 여러 번, 여러 방식으로 접근하고 결과를 결합할 수 있습니다. 더욱이 JSON 경로 표현식의 결과는 단일 항목이 아니라 항목의 정렬된 시퀀스입니다. 각 하위 표현식은 하나 이상의 입력 시퀀스를 받아 시퀀스를 결과로 반환합니다.
완화(lax) 모드에서 대부분의 경로 연산은 먼저 입력 시퀀스의 모든 JSON 배열을 언네스트(unnest)합니다. 이 규칙의 어떤 발산도 아래 목록에서 언급됩니다. 경로 모드는 "JSON 경로 모드" 섹션에서 설명합니다.
JSON 경로 언어의 기능은 리터럴, 변수, 산술 이항 표현식, 산술 단항 표현식, 그리고 접근자(accessors)로 총칭되는 연산자 그룹으로 나뉩니다.
리터럴 (literals)
- 숫자 리터럴: 정확한 수와 근사 수를 포함하며 SQL 값처럼 해석됩니다. 예:
-1, 1.2e3, NaN - 문자열 리터럴: 큰따옴표로 묶습니다. 예:
"Some text" - 부울 리터럴:
true, false - null 리터럴: SQL null이 아닌 JSON null의 의미를 가집니다. "비교 규칙" 섹션을 참고하세요. 예:
null
변수 (variables)
- 컨텍스트 변수: 현재 처리 중인 JSON 함수의 입력을 나타냅니다. 예:
$ - 명명된 변수: 이름으로 명명된 파라미터를 나타냅니다. 예:
$param - 현재 항목 변수: 필터 표현식 안에서 입력 시퀀스의 현재 처리 중인 항목을 참조하는 데 사용됩니다. 예:
@ - 마지막 첨자 변수: 가장 안쪽의 둘러싸는 배열의 마지막 인덱스를 나타냅니다. JSON 경로 표현식의 배열 인덱스는 0부터 시작합니다. 예:
last
산술 이항 표현식 (arithmetic binary expressions)
JSON 경로 언어는 다섯 개의 산술 이항 연산자를 지원합니다:
<path1> + <path2>
<path1> - <path2>
<path1> * <path2>
<path1> / <path2>
<path1> % <path2>
두 피연산자는 항목 시퀀스로 평가됩니다. 산술 이항 연산자의 경우 각 입력 시퀀스는 단일 숫자 항목을 포함해야 합니다. 산술 연산은 SQL 의미에 따라 수행되며 결과를 담은 단일 요소 시퀀스를 반환합니다. 연산자는 SQL 산술 연산과 같은 우선순위 규칙을 따르며, 괄호를 사용해 그룹화할 수 있습니다.
산술 단항 표현식 (arithmetic unary expressions)
+ <path>
- <path>
피연산자는 항목 시퀀스로 평가됩니다. 모든 항목은 숫자 값이어야 합니다. 단항 플러스 또는 마이너스가 SQL 의미에 따라 시퀀스의 모든 항목에 적용되고 결과가 반환 시퀀스를 형성합니다.
멤버 접근자 (member accessor)
멤버 접근자는 입력 시퀀스의 각 JSON 객체에 대해 지정된 키를 가진 멤버의 값을 반환합니다.
<path>.key
<path>."key"
JSON 객체에 그러한 멤버가 없을 때의 조건을 구조적 오류(structural error)라고 합니다. 완화 모드에서는 억제되고 오류가 있는 객체는 결과에서 제외됩니다.
<path>가 세 개의 JSON 객체 시퀀스를 반환한다고 합시다:
{"customer" : 100, "region" : "AFRICA"},
{"region" : "ASIA"},
{"customer" : 300, "region" : "AFRICA", "comment" : null}
표현식 .customer는 첫 번째와 세 번째 객체에서 성공하지만 두 번째 객체는 필요한 멤버가 없습니다. 엄격(strict) 모드에서는 경로 평가가 실패합니다. 완화 모드에서는 두 번째 객체가 조용히 건너뛰어지고 결과 시퀀스는 100, 300입니다.
입력 시퀀스의 모든 항목은 JSON 객체여야 합니다.
Trino는 중복 키가 있는 JSON 객체를 지원하지 않습니다.
와일드카드 멤버 접근자 (wildcard member accessor)
입력 시퀀스의 각 JSON 객체의 모든 키-값 쌍에서 값을 반환합니다. 모든 부분 결과는 반환 시퀀스로 연결됩니다.
<path>.*
<path>가 세 개의 JSON 객체 시퀀스를 반환한다면 결과는:
100, "AFRICA", "ASIA", 300, "AFRICA", null
입력 시퀀스의 모든 항목은 JSON 객체여야 합니다. 단일 JSON 객체에서 반환되는 값의 순서는 임의입니다. 모든 JSON 객체의 하위 시퀀스는 JSON 객체가 입력 시퀀스에 나타난 순서대로 연결됩니다.
하위 멤버 접근자 (descendant member accessor)
입력 시퀀스의 모든 중첩 수준의 모든 JSON 객체에서 지정된 키와 연관된 값을 반환합니다.
<path>..key
<path>.."key"
반환 값의 순서는 전위 깊이 우선 검색(선주문)입니다. 먼저 둘러싸는 객체를 방문하고 그 다음 모든 자식 노드를 방문합니다. 이 메서드는 완화 모드에서 배열 언래핑을 수행하지 않습니다. 결과는 완화와 엄격 모드에서 같습니다. 이 메서드는 JSON 배열과 JSON 객체를 탐색합니다. 비구조적 JSON 항목은 건너뜁니다.
<path>가 JSON 객체를 포함하는 시퀀스라면:
{
"id" : 1,
"notes" : [{"type" : 1, "comment" : "foo"}, {"type" : 2, "comment" : null}],
"comment" : ["bar", "baz"]
}
<path>..comment --> ["bar", "baz"], "foo", null
배열 접근자 (array accessor)
입력 시퀀스의 각 JSON 배열에 대해 지정된 인덱스의 요소를 반환합니다. 인덱스는 0부터 시작합니다.
<path>[ <subscripts> ]
<subscripts> 목록은 하나 이상의 첨자를 포함합니다. 각 첨자는 단일 인덱스 또는 범위(끝 포함)를 지정합니다:
<path>[<path1>, <path2> to <path3>, <path4>,...]
완화 모드에서 입력 시퀀스 평가로 나온 모든 비배열 항목은 단일 요소 배열로 감싸집니다. 이는 자동 배열 래핑 규칙에 대한 예외라는 점에 유의하세요.
입력 시퀀스의 각 배열은 다음 방식으로 처리됩니다:
- 변수
last가 배열의 마지막 인덱스로 설정됩니다. - 모든 첨자 인덱스가 선언 순서대로 계산됩니다. 단일 첨자
<path>의 결과는 단일 숫자 항목이어야 합니다. 범위 첨자<path1> to <path2>의 경우 두 개의 숫자 항목이 예상됩니다. - 지정된 배열 요소가 순서대로 출력 시퀀스에 추가됩니다.
<path>가 세 개의 JSON 배열 시퀀스를 반환한다고 합시다:
[0, 1, 2], ["a", "b", "c", "d"], [null, null]
다음 표현식은 매 배열의 마지막 요소를 포함하는 시퀀스를 반환합니다:
<path>[last] --> 2, "d", null
다음 표현식은 매 배열의 세 번째와 네 번째 요소를 반환합니다:
<path>[2 to 3] --> 2, "c", "d"
첫 번째 배열에는 네 번째 요소가 없고 마지막 배열에는 세 번째/네 번째 요소가 없습니다. 존재하지 않는 요소에 접근하는 것은 구조적 오류입니다. 엄격 모드에서는 경로 표현식이 실패합니다. 완화 모드에서는 그러한 오류가 억제되고 존재하는 요소만 반환됩니다. 5 to 3 같은 부적절한 범위 지정도 구조적 오류의 예입니다.
첨자는 겹칠 수 있고 요소 순서를 따를 필요가 없습니다. 반환 시퀀스의 순서는 첨자를 따릅니다:
<path>[1, 0, 0] --> 1, 0, 0, "b", "a", "a", null, null, null
와일드카드 배열 접근자 (wildcard array accessor)
입력 시퀀스의 각 JSON 배열의 모든 요소를 반환합니다.
<path>[*]
완화 모드에서 입력 시퀀스 평가로 나온 모든 비배열 항목은 단일 요소 배열로 감싸집니다. 출력 순서는 원본 JSON 배열의 순서를 따르며 배열 내 요소 순서도 보존됩니다.
<path>[*] --> 0, 1, 2, "a", "b", "c", "d", null, null
필터 (filter)
조건을 만족하는 입력 시퀀스의 항목을 검색합니다.
<path>?( <predicate> )
JSON 경로 조건은 SQL의 부울 표현식과 구문적으로 유사합니다. 그러나 의미는 여러 측면에서 다릅니다:
- 항목 시퀀스에 대해 동작합니다.
- 자체 오류 처리가 있습니다 (절대 실패하지 않습니다).
- 완화 또는 엄격 모드에 따라 다르게 동작합니다.
조건은 true, false, 또는 unknown으로 평가됩니다. 일부 조건 표현식은 중첩 JSON 경로 표현식을 포함합니다. 중첩 경로를 평가할 때 변수 @는 입력 시퀀스의 현재 검사 중인 항목을 나타냅니다.
다음 조건 표현식이 지원됩니다:
- 결합 (Conjunction):
<predicate1> && <predicate2> - 분리 (Disjunction):
<predicate1> || <predicate2> - 부정 (Negation):
! <predicate> exists조건:exists( <path> )— 중첩 경로가 비어 있지 않은 시퀀스로 평가되면true, 빈 시퀀스면false를 반환합니다. 경로 평가가 오류를 던지면unknown을 반환합니다.starts with조건:<path> starts with "Some text"또는<path> starts with $variable— 중첩<path>는 텍스트 항목 시퀀스로, 다른 피연산자는 단일 텍스트 항목으로 평가되어야 합니다. 두 피연산자의 평가 중 오류가 발생하면 결과는unknown입니다. 시퀀스의 모든 항목이 오른쪽 피연산자로 시작하는지 검사합니다. 일치하는 것이 발견되면true, 아니면false입니다. 다만 어떤 비교가 오류를 던지면 엄격 모드 결과는unknown입니다. 완화 모드 결과는 일치 또는 오류 중 어떤 것이 먼저 발견되었는지에 따라 달라집니다.is unknown조건:( <predicate> ) is unknown— 중첩 조건이unknown으로 평가되면true, 아니면false를 반환합니다.- 비교 (Comparisons):
<path1> == <path2>,<>,!=,<,>,<=,>=— 비교의 두 피연산자는 항목 시퀀스로 평가됩니다. 평가 중 오류가 발생하면 결과는unknown입니다. 좌우 시퀀스의 항목을 쌍으로 비교합니다.starts with조건과 유사하게 비교 중 어느 하나라도true를 반환하면 결과는true, 아니면false입니다. 다만 어떤 비교가 오류를 던지면(예: 비교되는 유형이 호환되지 않음) 엄격 모드 결과는unknown입니다. 완화 모드 결과는true비교 또는 오류 중 어떤 것이 먼저 발견되었는지에 따라 달라집니다.
비교 규칙: 비교 맥락의 null 값은 SQL null과 다르게 동작합니다.
null == null→truenull != null,null < 1,null > 1등 →false- null을 스칼라 값과 비교 →
false - null을 JSON 배열 또는 JSON 객체와 비교 →
false
두 스칼라 값을 비교할 때 비교가 성공적으로 수행되면 true 또는 false가 반환됩니다. 비교의 의미는 SQL과 같습니다. 오류(예: 텍스트와 숫자 비교)의 경우 unknown이 반환됩니다. 스칼라 값을 JSON 배열/JSON 객체와 비교하고, JSON 배열/객체를 비교하는 것은 오류이므로 unknown이 반환됩니다.
필터 예제:
<path>?(@.region != "ASIA") --> {"customer" : 100, "region" : "AFRICA"},
{"customer" : 300, "region" : "AFRICA", "comment" : null}
<path>?(!exists(@.customer)) --> {"region" : "ASIA"}
다음 접근자들을 총칭하여 **항목 메서드(item methods)**라고 합니다.
double()
숫자 또는 텍스트 값을 double 값으로 변환합니다. <path>.double()
<path>가 시퀀스 -1, 23e4, "5.6"을 반환한다면: <path>.double() --> -1e0, 23e4, 5.6e0
ceiling(), floor(), abs()
시퀀스의 모든 숫자 항목에 대해 ceiling, floor, 절댓값을 얻습니다. 연산의 의미는 SQL과 같습니다.
<path>.ceiling() --> -1.0, -1, 2.0
<path>.floor() --> -2.0, -1, 1.0
<path>.abs() --> 1.5, 1, 1.3
keyvalue()
시퀀스의 각 JSON 객체에 대한 원본 객체의 매 멤버당 하나의 객체를 포함하는 JSON 객체 컬렉션을 반환합니다. <path>.keyvalue()
반환된 객체는 세 멤버를 가집니다: "name"(원본 키), "value"(원본 바인딩 값), "id"(입력 객체에 특정한 고유 번호).
입력 시퀀스의 모든 항목은 JSON 객체여야 합니다. 반환 값의 순서는 원본 JSON 객체의 순서를 따릅니다. 다만 객체 내에서 반환 항목의 순서는 임의입니다.
type()
시퀀스의 모든 항목에 대한 유형 이름을 포함하는 텍스트 값을 반환합니다. <path>.type()
이 메서드는 완화 모드에서 배열 언래핑을 수행하지 않습니다. 반환 값은 JSON null이면 "null", 숫자 항목이면 "number", 텍스트 항목이면 "string", 부울 항목이면 "boolean", date 유형 항목이면 "date", time 유형 항목이면 "time without time zone", time with time zone 유형이면 "time with time zone", timestamp 유형이면 "timestamp without time zone", timestamp with time zone 유형이면 "timestamp with time zone", JSON 배열이면 "array", JSON 객체면 "object"입니다.
size()
시퀀스의 각 JSON 배열에 대한 크기를 포함하는 숫자 값을 반환합니다. <path>.size()
이 메서드는 완화 모드에서 배열 언래핑을 수행하지 않습니다. 대신 모든 비배열 항목이 단일 JSON 배열로 감싸지므로 크기는 1입니다. 입력 시퀀스의 모든 항목은 JSON 배열이어야 합니다.
Trino 특정 동작 (Trino-specific behavior)
형식 템플릿 없이 datetime()은 값을 값의 형태에 기반해 DATE / TIME(p) / TIME(p) WITH TIME ZONE / TIMESTAMP(p) / TIMESTAMP(p) WITH TIME ZONE 중 가장 구체적인 것으로 파싱합니다.
datetime() 형식 템플릿은 필드와 리터럴 텍스트의 문자열입니다. 필드는 대소문자를 구분하지 않고 값에서 숫자를 소비합니다. 리터럴 텍스트는 값과 원문 그대로 일치해야 합니다.
지원되는 필드:
| 필드 | 폭 | 설명 |
|---|---|---|
YYYY YYY YY Y |
4 / 3 / 2 / 1 | 연도. 4 미만의 폭은 참조 연도 1970의 앞자리로 접두어 처리됩니다 (예: YY=24 → 1924). |
RRRR RR |
4 / 2 | 반올림된 연도. 폭 2에서 값 0–49는 현재 세기, 50–99는 이전 세기로 매핑. |
MM |
2 | 월, 1–12. |
DD |
2 | 월 중 일자, 1–31. |
DDD |
3 | 연중 일, 1–366. MM/DD와 상호 배타적. |
HH24 |
2 | 하루 중 시간, 0–23. HH/HH12/A.M./P.M.와 상호 배타적. |
HH HH12 |
2 | 반일 중 시간, 1–12. A.M. 또는 P.M. 필요. |
A.M. P.M. |
4 | 반일 표시. 대소문자 구분 없음(a.m.과 A.M. 모두 허용). HH 또는 HH12 필요. |
MI |
2 | 분, 0–59. |
SS |
2 | 초, 0–59. |
SSSSS |
5 | 하루 중 초, 0–86399. HH/HH12/HH24/MI/SS/A.M./P.M.와 상호 배타적. |
FF1–FF9 |
1–9 | 소수 초, 주어진 숫자 폭. |
TZH |
3 | 부호가 있는 시간대 시간 오프셋, 예: +05 또는 -08. |
TZM |
2 | 시간대 분 오프셋, 0–59. TZH 필요. |
리터럴 텍스트는 단일 문자 구분자 -, ., /, ,, ', ;, :, 공백, 또는 큰따옴표 문자열(내장 "는 ""로 이스케이프) 중 하나입니다. 인접한 두 구분자는 거부됩니다. 따옴표 처리된 리터럴 옆의 구분자는 괜찮습니다.
예제:
YYYY-MM-DD -> 2024-01-02
YYYY-MM-DD"T"HH24:MI:SS.FF3 -> 2024-01-02T12:34:56.789
HH12:MI:SS A.M. -> 09:30:00 P.M.
YYYY-MM-DD HH24:MI:SS.FF3 TZH:TZM -> 2024-01-02 12:34:56.789 +05:30
Trino는 Trino의 최대 TIME(p)/TIMESTAMP(p) 정밀도인 12와 일치하도록 FF10, FF11, FF12를 추가로 허용합니다. 이 폭은 Trino 확장이며 다른 SQL/JSON 구현에서 이식되지 않을 수 있습니다.
like_regex()는 표준 SQL/XQuery 플래그(i, m, s, x)를 허용합니다.
제한 사항 (Limitations)
\s,\d,\w는 XQuery가 정의한 전체 유니코드 문자 클래스가 아니라 ASCII 문자만 일치합니다.- XML 이름 클래스 이스케이프
\i,\I,\c,\C는 지원되지 않습니다. x확장 모드 플래그는 모든 구성에서 지원되지 않을 수 있습니다.
JSON 경로 모드 (JSON path modes)
JSON 경로 표현식은 엄격(strict)과 완화(lax) 두 모드로 평가될 수 있습니다. 엄격 모드에서는 입력 JSON 데이터가 경로 표현식이 요구하는 스키마에 정확히 맞아야 합니다. 완화 모드에서는 입력 JSON 데이터가 예상 스키마에서 벗어날 수 있습니다.
다음 표는 두 모드의 차이를 보여줍니다.
| 조건 | 엄격 모드 | 완화 모드 |
|---|---|---|
배열에 비배열이 필요한 연산 수행(예: $.key는 JSON 객체 필요, $.floor()는 숫자 값 필요) |
ERROR | 배열이 자동으로 언네스트되고 각 배열 요소에 연산이 수행됨 |
비배열에 배열이 필요한 연산 수행(예: $[0], $[*], $.size()) |
ERROR | 비배열 항목이 자동으로 단일 배열로 감싸지고 배열에 연산이 수행됨 |
구조적 오류: 배열의 존재하지 않는 요소 또는 JSON 객체의 존재하지 않는 멤버 접근(예: $[-1] 배열 인덱스 범위 초과, $.key — 입력 JSON 객체에 멤버 key가 없음) |
ERROR | 오류가 억제되고 연산은 빈 시퀀스를 결과로 함 |
완화 모드 동작 예제:
<path>가 JSON 배열, JSON 객체, 스칼라 숫자 값 세 항목의 시퀀스를 반환한다고 합시다:
[1, "a", null], {"key1" : 1.0, "key2" : true}, -2e3
완화 모드의 와일드카드 배열 접근자. JSON 배열은 모든 요소를 반환하고, JSON 객체와 숫자는 단일 배열로 감싼 뒤 언네스트되므로 사실상 출력 시퀀스에서 그대로 나타납니다:
<path>[*] --> 1, "a", null, {"key1" : 1.0, "key2" : true}, -2e3
size() 메서드를 호출하면 JSON 객체와 숫자도 단일 배열로 감싸집니다:
<path>.size() --> 3, 1, 1
어떤 경우에는 완화 모드도 실패를 막을 수 없습니다. 다음 예제에서 floor() 메서드를 호출하기 전에 JSON 배열이 언래핑되더라도 항목 "a"가 유형 불일치를 일으킵니다.
<path>.floor() --> ERROR
JSON 단순 접근자 (JSON simplified accessor)
JSON 유형 값의 편리한 탐색을 위해 Trino는 행이나 배열을 탐색하는 것처럼 읽히는 점/첨자 접근자 체인을 작성할 수 있게 해줍니다. 체인의 수신자는 선언 유형이 JSON인 값이어야 합니다. 각 단계는 그 값에 적용된 JSON 경로를 확장합니다.
| 문법 | 동등한 것 |
|---|---|
j.name |
JSON_QUERY(j, 'lax $.name' WITH CONDITIONAL ARRAY WRAPPER NULL ON EMPTY NULL ON ERROR) |
j."FooBar" |
JSON_QUERY(j, 'lax $."FooBar"' …) — 구분 식별자, 대소문자 구분 |
j.'foo bar' |
구분 형식과 동일 |
j[3] |
JSON_QUERY(j, 'lax $[3]' …) — 정수 첨자 |
j[*] |
JSON_QUERY(j, 'lax $[*]' …) — 배열 와일드카드 |
j.* (SELECT에서) |
JSON_QUERY(j, 'lax $.*' …) — 멤버 와일드카드, VARCHAR 컬럼 하나 생성 |
j.name.bigint() |
JSON_VALUE(j, 'lax $.name' RETURNING BIGINT …) — 항목 메서드 |
j.payload.amount.decimal(18,2) |
JSON_VALUE(j, 'lax $.payload.amount' RETURNING DECIMAL(18,2) …) |
멤버, 인덱스, 와일드카드, 항목 메서드 단계는 자유롭게 결합됩니다: j.rows[1].cells[*], j.items[0].label, j.payload.* 등.
대소문자 구분 (Case sensitivity)
멤버 이름 식별자는 참조하는 JSON 키에 대해 대소문자를 구분해 일치합니다:
j.Foo는 문자 그대로Foo라는 이름의 멤버와 일치합니다.j."Foo"와j.'Foo'는 같은 경로입니다. 둘 다 멤버 이름을 인용합니다.
이는 j.foo와 j.FOO가 같은 컬럼을 참조하는 일반 SQL 식별자 처리와 다릅니다. JSON 경로 언어는 대소문자를 구분하며, 단순 접근자는 원본 대소문자를 보존해 JSON 키가 정확히 작성된 대로 일치하게 합니다.
항목 메서드 이름(bigint, time, decimal, …)은 SQL 식별자이며 대소문자를 구분하지 않습니다. j.x.BIGINT(), j.x.Bigint(), j.x.bigint() 모두 같은 항목 메서드로 해석됩니다.
항목 메서드 (Item methods)
항목 메서드는 json_value의 RETURNING 절이 하는 것과 같은 캐스트 집합을 다룹니다.
| 메서드 | 반환 |
|---|---|
bigint() |
BIGINT |
boolean() |
BOOLEAN |
date() |
DATE |
decimal() |
DECIMAL (파라미터 없음) |
decimal(p) |
DECIMAL(p) |
decimal(p, s) |
DECIMAL(p, s) |
integer() |
INTEGER |
number() |
DOUBLE |
string() |
VARCHAR |
time() |
TIME(3) |
time(p) |
TIME(p) |
time_tz() |
TIME(3) WITH TIME ZONE |
time_tz(p) |
TIME(p) WITH TIME ZONE |
timestamp() |
TIMESTAMP(3) |
timestamp(p) |
TIMESTAMP(p) |
timestamp_tz() |
TIMESTAMP(3) WITH TIME ZONE |
timestamp_tz(p) |
TIMESTAMP(p) WITH TIME ZONE |
ON EMPTY와 ON ERROR 절은 기본적으로 NULL입니다.
SELECT의 멤버 와일드카드 (Member wildcard in SELECT)
SELECT j.*는 SELECT JSON_QUERY(j, 'lax $.*' …)의 약식입니다. j의 최상위 멤버의 JSON 배열인 값을 가진 VARCHAR 출력 컬럼 하나를 생성합니다. 선택적 AS (column_alias)가 출력 컬럼 이름을 제공할 수 있습니다:
SELECT j.* AS (payload) FROM (VALUES (CAST('{"a":1,"b":2}' AS JSON))) AS t(j);
payload
-----------
[1,2]
(1 row)
같은 와일드카드는 값 표현식 위치의 첨자 체인 안에서도 인식되어 그 접두어 아래 경로를 확장합니다: j.payload.*, j.items[*], j.items[*].label 등.
외부 스코프 (Outer scope)
체인의 수신자는 상관 서브쿼리를 위해 외부 스코프의 컬럼을 참조할 수 있습니다:
SELECT (SELECT o.j.foo)
FROM (VALUES (CAST('{"foo":1}' AS JSON))) AS o(j);
외부 스코프 수신자는 멤버 접근(o.j.foo), 첨자(o.j.items[0]), 첨자 체인의 항목 메서드(o.j.items[0].bigint()), 멤버 와일드카드(o.j.*)에 대해 지원됩니다. 사이에 첨자 없이 점 멤버 체인에 직접 적용된 항목 메서드(예: o.j.foo.bigint())는 외부 스코프 컬럼에서 지원되지 않습니다. 그러한 경우 컬럼을 로컬로 조회하거나 명시적 JSON_VALUE 호출을 삽입하세요.
json_exists
json_exists 함수는 JSON 값이 JSON 경로 사양을 만족하는지 결정합니다.
JSON_EXISTS(
json_input [ FORMAT JSON [ ENCODING { UTF8 | UTF16 | UTF32 } ] ],
json_path
[ PASSING json_argument [, ...] ]
[ { TRUE | FALSE | UNKNOWN | ERROR } ON ERROR ]
)
json_path는 json_input을 컨텍스트 변수($)로, 전달된 인수를 명명된 변수($variable_name)로 사용해 평가됩니다. 경로가 비어 있지 않은 시퀀스를 반환하면 반환 값은 true, 빈 시퀀스를 반환하면 false입니다. 오류가 발생하면 반환 값은 ON ERROR 절에 따라 달라집니다. ON ERROR의 기본 반환 값은 FALSE입니다. ON ERROR 절은 다음 오류 종류에 적용됩니다:
- 잘못된 JSON 같은 입력 변환 오류
- 0으로 나누기 같은 JSON 경로 평가 오류
json_input은 문자 문자열 또는 바이너리 문자열입니다. 단일 JSON 항목을 포함해야 합니다. 바이너리 문자열의 경우 인코딩을 지정할 수 있습니다.
json_path는 JSON 경로 문법 및 의미 섹션에 설명된 문법 규칙을 따르는 경로 모드 사양과 경로 표현식을 포함하는 문자열 리터럴입니다.
'strict ($.price + $.tax)?(@ > 99.9)'
'lax $[0 to 1].floor()?(@ > 10)'
PASSING 절에서 경로 표현식이 사용할 임의의 표현식을 전달할 수 있습니다.
PASSING orders.totalprice AS O_PRICE,
orders.tax % 10 AS O_TAX
전달된 파라미터는 $ 접두어가 있는 명명된 변수로 경로 표현식에서 참조할 수 있습니다.
'lax $?(@.price > $O_PRICE || @.tax > $O_TAX)'
SQL 값에 더해 형식과 선택적 인코딩을 지정해 JSON 값을 전달할 수도 있습니다:
PASSING orders.json_desc FORMAT JSON AS o_desc,
orders.binary_record FORMAT JSON ENCODING UTF16 AS o_rec
JSON 경로 언어는 대소문자를 구분하는 반면, 따옴표 없는 SQL 식별자는 대문자화됩니다. 따라서 PASSING 절에서 따옴표 처리된 식별자를 사용하는 것이 좋습니다:
'lax $.keyvalue()?(@.name == $KeyName).value' PASSING nation.name AS KeyName --> ERROR; 전달된 값 없음
'lax $.keyvalue()?(@.name == $KeyName).value' PASSING nation.name AS "KeyName" --> correct
예제 (Examples)
customers가 id:bigint, description:varchar 두 컬럼을 가진 테이블이라고 합시다. 다음 쿼리는 10세 이상의 자녀가 있는 고객을 확인합니다:
SELECT
id,
json_exists(
description,
'lax $.children[*]?(@ > 10)'
) AS children_above_ten
FROM customers
다음 쿼리에서 경로 모드는 엄격입니다. 각 고객의 세 번째 자녀를 확인합니다. 세 명 이상의 자녀가 없는 고객에게는 구조적 오류가 발생해야 합니다. 이 오류는 ON ERROR 절에 따라 처리됩니다.
SELECT
id,
json_exists(
description,
'strict $.children[2]?(@ > 10)'
UNKNOWN ON ERROR
) AS child_3_above_ten
FROM customers
json_query
json_query 함수는 JSON 값에서 JSON 값을 추출합니다.
JSON_QUERY(
json_input [ FORMAT JSON [ ENCODING { UTF8 | UTF16 | UTF32 } ] ],
json_path
[ PASSING json_argument [, ...] ]
[ RETURNING type [ FORMAT JSON [ ENCODING { UTF8 | UTF16 | UTF32 } ] ] ]
[ WITHOUT [ ARRAY ] WRAPPER |
WITH [ { CONDITIONAL | UNCONDITIONAL } ] [ ARRAY ] WRAPPER ]
[ { KEEP | OMIT } QUOTES [ ON SCALAR STRING ] ]
[ { ERROR | NULL | EMPTY ARRAY | EMPTY OBJECT } ON EMPTY ]
[ { ERROR | NULL | EMPTY ARRAY | EMPTY OBJECT } ON ERROR ]
)
상수 문자열 json_path는 json_input을 컨텍스트 변수($)로, 전달된 인수를 명명된 변수($variable_name)로 사용해 평가됩니다.
반환 값은 경로가 반환한 JSON 항목입니다. 기본적으로 문자 문자열(varchar)로 표현됩니다. RETURNING 절에서 다른 문자 문자열 유형 또는 varbinary를 지정할 수 있습니다. varbinary에서는 원하는 인코딩도 지정할 수 있습니다.
json_input은 문자 문자열 또는 바이너리 문자열입니다. 단일 JSON 항목을 포함해야 합니다. 바이너리 문자열의 경우 인코딩을 지정할 수 있습니다.
json_path는 경로 문법을 따르는 문자열 리터럴입니다.
'strict $.keyvalue()?(@.name == $cust_id)'
'lax $[5 to last]'
PASSING 절에서 임의의 표현식을 전달할 수 있습니다. PASSING orders.custkey AS CUST_ID 등. 전달된 파라미터는 $ 접두어가 있는 명명된 변수로 경로 표현식에서 참조할 수 있습니다.
ARRAY WRAPPER 절은 결과를 JSON 배열로 감싸 출력을 수정하게 합니다. WITHOUT ARRAY WRAPPER가 기본 옵션입니다. WITH CONDITIONAL ARRAY WRAPPER는 단일 JSON 배열 또는 JSON 객체가 아닌 모든 결과를 감쌉니다. WITH UNCONDITIONAL ARRAY WRAPPER는 모든 결과를 감쌉니다.
QUOTES 절은 스칼라 문자열의 결과를 수정해 JSON 문자열 표현의 일부인 큰따옴표를 제거합니다.
예제 (Examples)
customers 테이블에서 다음 쿼리는 각 고객의 children 배열을 얻습니다:
SELECT
id,
json_query(
description,
'lax $.children'
) AS children
FROM customers
다음 쿼리는 각 고객의 자녀 컬렉션을 얻습니다. json_query는 단일 JSON 항목만 출력할 수 있다는 점에 유의하세요. 배열 래퍼를 사용하지 않으면 자녀가 여러 명인 고객마다 오류가 발생합니다. 이 오류는 ON ERROR 절에 따라 처리됩니다.
SELECT
id,
json_query(
description,
'lax $.children[*]'
WITHOUT ARRAY WRAPPER
NULL ON ERROR
) AS children
FROM customers
다음 쿼리는 각 고객의 마지막 자녀를 JSON 배열로 감싼 값을 얻습니다:
SELECT
id,
json_query(
description,
'lax $.children[last]'
WITH ARRAY WRAPPER
) AS last_child
FROM customers
다음 쿼리는 각 고객의 12세 이상 자녀를 JSON 배열로 감싼 값을 얻습니다. 두 번째와 세 번째 고객은 이 나이의 자녀가 없습니다. 그러한 경우는 ON EMPTY 절에 따라 처리됩니다. ON EMPTY의 기본 반환 값은 NULL입니다. 다음 예제에서는 EMPTY ARRAY ON EMPTY가 지정됩니다.
SELECT
id,
json_query(
description,
'strict $.children[*]?(@ > 12)'
WITH ARRAY WRAPPER
EMPTY ARRAY ON EMPTY
) AS children
FROM customers
다음 쿼리는 QUOTES 절의 결과를 보여줍니다. KEEP QUOTES가 기본입니다.
SELECT
id,
json_query(description, 'strict $.comment' KEEP QUOTES) AS quoted_comment,
json_query(description, 'strict $.comment' OMIT QUOTES) AS unquoted_comment
FROM customers
오류가 발생하면 반환 값은 ON ERROR 절에 따라 달라집니다. ON ERROR의 기본 반환 값은 NULL입니다. 오류 예시는 경로가 여러 항목을 반환하는 것입니다. ON ERROR 절이 포착해 처리하는 다른 오류는 다음과 같습니다:
- 잘못된 JSON 같은 입력 변환 오류
- 0으로 나누기 같은 JSON 경로 평가 오류
- 출력 변환 오류
json_value
json_value 함수는 JSON 값에서 스칼라 SQL 값을 추출합니다.
JSON_VALUE(
json_input [ FORMAT JSON [ ENCODING { UTF8 | UTF16 | UTF32 } ] ],
json_path
[ PASSING json_argument [, ...] ]
[ RETURNING type ]
[ { ERROR | NULL | DEFAULT expression } ON EMPTY ]
[ { ERROR | NULL | DEFAULT expression } ON ERROR ]
)
json_path는 json_input을 컨텍스트 변수($)로, 전달된 인수를 명명된 변수($variable_name)로 사용해 평가됩니다.
반환 값은 경로가 반환한 SQL 스칼라입니다. 기본적으로 문자열(varchar)로 변환됩니다. RETURNING 절에서 문자 문자열, 숫자, 부울 또는 datetime 유형 같은 원하는 다른 유형을 지정할 수 있습니다.
json_path는 경로 문법을 따르는 문자열 리터럴입니다. PASSING 절에서 임의의 표현식을 전달할 수 있고 $ 접두어가 있는 명명된 변수로 참조합니다.
경로가 빈 시퀀스를 반환하면 ON EMPTY 절이 적용됩니다. ON EMPTY의 기본 반환 값은 NULL입니다. 기본 값을 지정할 수도 있습니다:
DEFAULT -1 ON EMPTY
오류가 발생하면 반환 값은 ON ERROR 절에 따라 달라집니다. ON ERROR의 기본 반환 값은 NULL입니다. 오류 예시는 경로가 여러 항목을 반환하는 것입니다. ON ERROR 절이 처리하는 다른 오류는 다음과 같습니다:
- 잘못된 JSON 같은 입력 변환 오류
- 0으로 나누기 같은 JSON 경로 평가 오류
- 반환된 스칼라를 원하는 유형으로 변환할 수 없음
ON EMPTY와 ON ERROR의 DEFAULT 표현식은 해당 분기가 선택될 때만 평가됩니다. ON EMPTY 기본값 자체의 평가가 데이터 예외를 발생시키면 실패가 ON ERROR 절로 연쇄됩니다. DEFAULT 표현식에서는 서브쿼리가 지원되지 않습니다.
예제 (Examples)
다음 쿼리는 각 고객의 comment를 char(12)로 얻습니다:
SELECT id, json_value(
description,
'lax $.comment'
RETURNING char(12)
) AS comment
FROM customers
다음 쿼리는 각 고객의 첫 번째 자녀의 나이를 tinyint로 얻습니다:
SELECT id, json_value(
description,
'lax $.children[0]'
RETURNING tinyint
) AS child
FROM customers
다음 쿼리는 각 고객의 세 번째 자녀의 나이를 얻습니다. 엄격 모드에서 세 번째 자녀가 없는 고객에게는 구조적 오류가 발생해야 합니다. 이 오류는 ON ERROR 절에 따라 처리됩니다.
SELECT id, json_value(
description,
'strict $.children[2]'
DEFAULT 'err' ON ERROR
) AS child
FROM customers
모드를 완화로 바꾸면 구조적 오류가 억제되고 세 번째 자녀가 없는 고객은 빈 시퀀스를 생성합니다. 이 경우는 ON EMPTY 절에 따라 처리됩니다.
SELECT id, json_value(
description,
'lax $.children[2]'
DEFAULT 'missing' ON EMPTY
) AS child
FROM customers
json_table
json_table 절은 JSON 값에서 테이블을 추출합니다. 이 절을 사용해 JSON 데이터를 관계형 형식으로 변환해 조회와 분석을 더 쉽게 할 수 있습니다. SELECT 문의 FROM 절에서 json_table을 사용해 JSON 데이터에서 테이블을 만듭니다.
JSON_TABLE(
json_input,
json_path [ AS path_name ]
[ PASSING value AS parameter_name [, ...] ]
COLUMNS (
column_definition [, ...] )
[ PLAN ( json_table_specific_plan )
| PLAN DEFAULT ( json_table_default_plan ) ]
[ { ERROR | EMPTY } ON ERROR ]
)
COLUMNS 절은 다음 column_definition 인수를 지원합니다:
column_name FOR ORDINALITY
| column_name type
[ FORMAT JSON [ ENCODING { UTF8 | UTF16 | UTF32 } ] ]
[ PATH json_path ]
[ { WITHOUT | WITH { CONDITIONAL | UNCONDITIONAL } } [ ARRAY ] WRAPPER ]
[ { KEEP | OMIT } QUOTES [ ON SCALAR STRING ] ]
[ { ERROR | NULL | EMPTY { [ARRAY] | OBJECT } | DEFAULT expression } ON EMPTY ]
[ { ERROR | NULL | DEFAULT expression } ON ERROR ]
| NESTED [ PATH ] json_path [ AS path_name ] COLUMNS ( column_definition [, ...] )
json_input은 문자 문자열 또는 바이너리 문자열입니다. 단일 JSON 항목을 포함해야 합니다.
json_path는 경로 모드 사양과 경로 표현식을 포함하는 문자열 리터럴입니다.
'strict ($.price + $.tax)?(@ > 99.9)'
'lax $[0 to 1].floor()?(@ > 10)'
PASSING 절에서 json_path 표현식이 참조할 수 있는 값을 명명된 파라미터로 전달합니다. PASSING orders.totalprice AS o_price, orders.tax % 10 AS o_tax 등. 명명된 파라미터는 $ 접두어로 경로 표현식에서 참조합니다.
PASSING 절에서 JSON 값도 전달할 수 있습니다. 형식을 지정하려면 FORMAT JSON, 인코딩을 지정하려면 ENCODING을 사용하세요.
json_path 값은 대소문자를 구분합니다. SQL 식별자는 대문자입니다. PASSING 절에서 따옴표 처리된 식별자를 사용하세요.
PLAN 절은 서로 다른 경로의 컬럼을 조인하는 방법을 지정합니다. OUTER 또는 INNER를 사용해 부모 경로를 자식 경로와 조인하는 방법을 정의하고, CROSS 또는 UNION을 사용해 형제를 조인합니다.
COLUMNS는 테이블의 스키마를 정의합니다. 각 column_definition은 json_input 값을 관계형 컬럼으로 추출하고 형식화하는 방법을 지정합니다.
PLAN은 중첩 JSON 데이터를 처리하고 조인하는 방법을 제어하는 선택적 절입니다.
ON ERROR는 처리 오류를 처리하는 방법을 지정합니다. ERROR ON ERROR는 오류를 던지고, EMPTY ON ERROR는 빈 결과 집합을 반환합니다.
ON EMPTY 또는 ON ERROR 절에 DEFAULT가 있는 각 값 컬럼에 대해 기본 표현식은 해당 분기가 선택될 때만 평가되고, ON EMPTY 기본값 자체가 데이터 예외를 발생시키면 실패가 ON ERROR 절로 연쇄됩니다. json_table DEFAULT 표현식에서는 서브쿼리가 지원되지 않습니다.
column_name은 컬럼 이름을 지정합니다.
FOR ORDINALITY는 1부터 시작하는 행 번호 컬럼을 출력 테이블에 추가합니다. 열 정의에 컬럼 이름을 지정하세요: row_num FOR ORDINALITY.
NESTED PATH는 json_input 값의 중첩 수준에서 데이터를 추출합니다. 각 NESTED PATH 절은 column_definition 값을 포함할 수 있습니다.
json_table 함수는 쿼리에서 다른 테이블처럼 사용할 수 있는 결과 집합을 반환합니다. 결과 집합을 다른 테이블과 조인하거나 JSON 데이터의 여러 배열을 결합할 수 있습니다. 데이터를 여러 번 파싱하지 않고도 중첩 JSON 객체를 처리할 수 있습니다. 다른 테이블의 JSON 데이터를 처리하려면 json_table을 lateral join으로 사용하세요.
예제 (Examples)
다음 쿼리는 JSON 배열에서 값을 추출해 세 컬럼이 있는 테이블의 행으로 반환합니다:
SELECT
*
FROM
json_table(
'[
{"id":1,"name":"Africa","wikiDataId":"Q15"},
{"id":2,"name":"Americas","wikiDataId":"Q828"},
{"id":3,"name":"Asia","wikiDataId":"Q48"},
{"id":4,"name":"Europe","wikiDataId":"Q51"}
]',
'strict $' COLUMNS (
NESTED PATH 'strict $[*]' COLUMNS (
id integer PATH 'strict $.id',
name varchar PATH 'strict $.name',
wiki_data_id varchar PATH 'strict $."wikiDataId"'
)
)
);
다음 쿼리는 중첩 JSON 객체 배열에서 값을 추출해 중첩 JSON 데이터를 단일 테이블로 평탄화합니다. 각 대륙이 국가와 인구 배열을 포함하는 대륙 이름 배열을 처리합니다.
NESTED PATH 'lax $[*]' 절이 대륙 객체를 반복하고, NESTED PATH 'lax $.countries[*]'가 각 대륙 안의 각 국가를 반복합니다. 이로써 각 대륙을 그 국가들과 결합한 네 행의 평평한 테이블 구조가 만들어집니다. 대륙 값은 각 국가마다 반복됩니다.
SELECT
*
FROM
json_table(
'[
{"continent": "Asia", "countries": [
{"name": "Japan", "population": 125.7},
{"name": "Thailand", "population": 71.6}
]},
{"continent": "Europe", "countries": [
{"name": "France", "population": 67.4},
{"name": "Germany", "population": 83.2}
]}
]',
'lax $' COLUMNS (
NESTED PATH 'lax $[*]' COLUMNS (
continent varchar PATH 'lax $.continent',
NESTED PATH 'lax $.countries[*]' COLUMNS (
country varchar PATH 'lax $.name',
population double PATH 'lax $.population'
)
)
));
다음 쿼리는 PLAN을 사용해 부모 경로와 자식 경로 사이의 OUTER 조인을 지정합니다:
SELECT
*
FROM
JSON_TABLE(
'[]',
'lax $' AS "root_path"
COLUMNS(
a varchar(1) PATH 'lax "A"',
NESTED PATH 'lax $[*]' AS "nested_path"
COLUMNS (b varchar(1) PATH 'lax "B"'))
PLAN ("root_path" OUTER "nested_path")
);
다음 쿼리는 PLAN을 사용해 부모 경로와 자식 경로 사이의 INNER 조인을 지정합니다:
SELECT
*
FROM
JSON_TABLE(
'[]',
'lax $' AS "root_path"
COLUMNS(
a varchar(1) PATH 'lax "A"',
NESTED PATH 'lax $[*]' AS "nested_path"
COLUMNS (b varchar(1) PATH 'lax "B"'))
PLAN ("root_path" INNER "nested_path")
);
json_array
json_array 함수는 주어진 요소를 포함하는 JSON 배열을 만듭니다.
JSON_ARRAY(
[ array_element [, ...]
[ { NULL ON NULL | ABSENT ON NULL } ] ],
[ RETURNING type [ FORMAT JSON [ ENCODING { UTF8 | UTF16 | UTF32 } ] ] ]
)
인수 유형 (Argument types)
배열 요소는 임의의 표현식일 수 있습니다. 전달되는 각 값은 유형과 선택적 FORMAT/ENCODING 사양에 따라 JSON 항목으로 변환됩니다.
부울, 숫자, 문자 문자열 유형의 SQL 값을 전달할 수 있습니다. 이들은 대응하는 JSON 리터럴로 변환됩니다:
SELECT json_array(true, 12e-1, 'text')
--> '[true,1.2,"text"]'
SQL 값에 더해 JSON 값을 전달할 수 있습니다. 이들은 지정된 형식과 선택적 인코딩을 가진 문자 또는 바이너리 문자열입니다:
SELECT json_array(
'[ "text" ] ' FORMAT JSON,
X'5B0035005D00' FORMAT JSON ENCODING UTF16
)
--> '[["text"],[5]]'
다른 JSON 반환 함수를 중첩할 수도 있습니다. 이 경우 FORMAT 옵션이 암시적입니다:
SELECT json_array(
json_query('{"key" : [ "value" ]}', 'lax $.key')
)
--> '[["value"]]'
다른 전달 값은 varchar로 캐스팅되어 JSON 텍스트 리터럴이 됩니다:
SELECT json_array(
DATE '2001-01-31',
UUID '12151fd2-7586-11e9-8f9e-2a86e4085a59'
)
--> '["2001-01-31","12151fd2-7586-11e9-8f9e-2a86e4085a59"]'
인수를 생략해 빈 배열을 얻을 수도 있습니다: SELECT json_array() --> '[]'.
null 처리 (Null handling)
배열 요소로 전달된 값이 null이면 지정된 null 처리 옵션에 따라 처리됩니다. ABSENT ON NULL을 지정하면 null 요소가 결과에서 생략됩니다. NULL ON NULL을 지정하면 JSON null이 결과에 추가됩니다. ABSENT ON NULL이 기본 설정입니다:
SELECT json_array(true, null, 1)
--> '[true,1]'
SELECT json_array(true, null, 1 ABSENT ON NULL)
--> '[true,1]'
SELECT json_array(true, null, 1 NULL ON NULL)
--> '[true,null,1]'
반환 유형 (Returned type)
SQL 표준은 SQL에서 JSON 데이터를 표현하는 전용 데이터 유형을 부과하지 않습니다. 대신 JSON 데이터는 문자 또는 바이너리 문자열로 표현됩니다. 기본적으로 json_array 함수는 JSON 배열의 텍스트 표현을 담은 varchar를 반환합니다. RETURNING 절로 다른 문자 문자열 유형을 지정할 수 있습니다:
SELECT json_array(true, 1 RETURNING VARCHAR(100))
--> '[true,1]'
반환 유형으로 varbinary와 필요한 인코딩을 지정할 수도 있습니다. 기본 인코딩은 UTF8입니다:
SELECT json_array(true, 1 RETURNING VARBINARY)
--> X'5b 74 72 75 65 2c 31 5d'
SELECT json_array(true, 1 RETURNING VARBINARY FORMAT JSON ENCODING UTF8)
--> X'5b 74 72 75 65 2c 31 5d'
SELECT json_array(true, 1 RETURNING VARBINARY FORMAT JSON ENCODING UTF16)
--> X'5b 00 74 00 72 00 75 00 65 00 2c 00 31 00 5d 00'
SELECT json_array(true, 1 RETURNING VARBINARY FORMAT JSON ENCODING UTF32)
--> X'5b 00 00 00 74 00 00 00 72 00 00 00 75 00 00 00 65 00 00 00 2c 00 00 00 31 00 00 00 5d 00 00 00'
json_object
json_object 함수는 주어진 키-값 쌍을 포함하는 JSON 객체를 만듭니다.
JSON_OBJECT(
[ key_value [, ...]
[ { NULL ON NULL | ABSENT ON NULL } ] ],
[ { WITH UNIQUE [ KEYS ] | WITHOUT UNIQUE [ KEYS ] } ]
[ RETURNING type [ FORMAT JSON [ ENCODING { UTF8 | UTF16 | UTF32 } ] ] ]
)
인수 전달 규칙 (Argument passing conventions)
키와 값을 전달하는 두 가지 규칙이 있습니다:
SELECT json_object('key1' : 1, 'key2' : true)
--> '{"key1":1,"key2":true}'
SELECT json_object(KEY 'key1' VALUE 1, KEY 'key2' VALUE true)
--> '{"key1":1,"key2":true}'
두 번째 규칙에서는 KEY 키워드를 생략할 수 있습니다:
SELECT json_object('key1' VALUE 1, 'key2' VALUE true)
--> '{"key1":1,"key2":true}'
인수 유형 (Argument types)
키는 임의의 표현식일 수 있습니다. 문자 문자열 유형이어야 합니다. 각 키는 JSON 텍스트 항목으로 변환되어 생성되는 JSON 객체의 키가 됩니다. 키는 null이어서는 안 됩니다.
값은 임의의 표현식일 수 있습니다. 각 전달 값은 유형과 선택적 FORMAT/ENCODING 사양에 따라 JSON 항목으로 변환됩니다.
SELECT json_object('x' : true, 'y' : 12e-1, 'z' : 'text')
--> '{"x":true,"y":1.2,"z":"text"}'
JSON 값을 전달할 수도 있습니다 (형식/인코딩 지정 사용). 다른 JSON 반환 함수를 중첩할 수도 있습니다. 다른 전달 값은 varchar로 캐스팅되어 JSON 텍스트 리터럴이 됩니다.
인수를 생략해 빈 객체를 얻을 수도 있습니다: SELECT json_object() --> '{}'.
null 처리 (Null handling)
JSON 객체 키로 전달되는 값은 null이어서는 안 됩니다. JSON 객체 값으로 null을 전달하는 것은 허용됩니다. null 값은 지정된 null 처리 옵션에 따라 처리됩니다. NULL ON NULL을 지정하면 null 값을 가진 JSON 객체 항목이 결과에 추가됩니다. ABSENT ON NULL을 지정하면 항목이 결과에서 생략됩니다. NULL ON NULL이 기본 설정입니다:
SELECT json_object('x' : null, 'y' : 1)
--> '{"x":null,"y":1}'
SELECT json_object('x' : null, 'y' : 1 NULL ON NULL)
--> '{"x":null,"y":1}'
SELECT json_object('x' : null, 'y' : 1 ABSENT ON NULL)
--> '{"y":1}'
키 고유성 (Key uniqueness)
중복 키가 발견되면 지정된 키 고유성 제약에 따라 처리됩니다.
WITH UNIQUE KEYS가 지정되면 중복 키는 쿼리 실패를 초래합니다:
SELECT json_object('x' : null, 'x' : 1 WITH UNIQUE KEYS)
--> failure: "duplicate key passed to JSON_OBJECT function"
이 옵션은 어떤 인수에 FORMAT 사양이 있으면 지원되지 않습니다.
WITHOUT UNIQUE KEYS가 지정되면 구현 제한으로 중복 키가 지원되지 않습니다. WITHOUT UNIQUE KEYS가 기본 설정입니다.
반환 유형 (Returned type)
기본적으로 json_object 함수는 JSON 객체의 텍스트 표현을 담은 varchar를 반환합니다. RETURNING 절로 다른 문자 문자열 유형을 지정할 수 있고, varbinary와 필요한 인코딩을 지정할 수도 있습니다. 기본 인코딩은 UTF8입니다.
JSON으로 캐스트 (Cast to JSON)
다음 유형을 JSON으로 캐스팅할 수 있습니다: BOOLEAN, TINYINT, SMALLINT, INTEGER, BIGINT, REAL, DOUBLE, VARCHAR. 또한 다음 요구 사항이 충족되면 ARRAY, MAP, ROW 유형도 JSON으로 캐스팅할 수 있습니다:
ARRAY유형은 배열의 요소 유형이 지원되는 유형 중 하나일 때 캐스팅할 수 있습니다.MAP유형은 맵의 키 유형이VARCHAR이고 값 유형이 지원되는 유형일 때 캐스팅할 수 있습니다.ROW유형은 행의 모든 필드 유형이 지원되는 유형일 때 캐스팅할 수 있습니다.
지원되는 문자 문자열 유형을 사용한 캐스트 연산은 입력을 JSON으로 검증하지 않고 문자열로 취급합니다. 즉 문자열 유형 입력이 잘못된 JSON이면 캐스트가 잘못된 JSON으로 성공합니다. 대신 json_parse() 함수를 사용해 문자열에서 검증된 JSON을 만드는 것을 고려하세요.
캐스팅 예제:
SELECT CAST(NULL AS JSON); -- NULL
SELECT CAST(1 AS JSON); -- JSON '1'
SELECT CAST(9223372036854775807 AS JSON); -- JSON '9223372036854775807'
SELECT CAST('abc' AS JSON); -- JSON '"abc"'
SELECT CAST(true AS JSON); -- JSON 'true'
SELECT CAST(1.234 AS JSON); -- JSON '1.234'
SELECT CAST(ARRAY[1, 23, 456] AS JSON); -- JSON '[1,23,456]'
SELECT CAST(ARRAY[1, NULL, 456] AS JSON); -- JSON '[1,null,456]'
SELECT CAST(ARRAY[ARRAY[1, 23], ARRAY[456]] AS JSON); -- JSON '[[1,23],[456]]'
SELECT CAST(MAP(ARRAY['k1', 'k2', 'k3'], ARRAY[1, 23, 456]) AS JSON);
-- JSON '{"k1":1,"k2":23,"k3":456}'
SELECT CAST(CAST(ROW(123, 'abc', true) AS ROW(v1 BIGINT, v2 VARCHAR, v3 BOOLEAN)) AS JSON);
-- JSON '{"v1":123,"v2":"abc","v3":true}'
NULL에서 JSON으로 캐스팅하는 것은 간단하지 않습니다. 독립된 NULL에서 캐스팅하면 JSON 'null' 대신 SQL NULL이 생성됩니다. 다만 NULL을 포함하는 배열이나 맵에서 캐스팅하면 생성되는 JSON에 null이 포함됩니다.
JSON에서 캐스트 (Cast from JSON)
BOOLEAN, TINYINT, SMALLINT, INTEGER, BIGINT, REAL, DOUBLE, VARCHAR로의 캐스팅이 지원됩니다. 배열의 요소 유형이 지원되는 유형 중 하나일 때 ARRAY로, 맵의 키 유형이 VARCHAR이고 값 유형이 지원되는 유형 중 하나일 때 MAP으로 캐스팅할 수 있습니다.
SELECT CAST(JSON 'null' AS VARCHAR); -- NULL
SELECT CAST(JSON '1' AS INTEGER); -- 1
SELECT CAST(JSON '9223372036854775807' AS BIGINT); -- 9223372036854775807
SELECT CAST(JSON '"abc"' AS VARCHAR); -- abc
SELECT CAST(JSON 'true' AS BOOLEAN); -- true
SELECT CAST(JSON '1.234' AS DOUBLE); -- 1.234
SELECT CAST(JSON '[1,23,456]' AS ARRAY(INTEGER)); -- [1, 23, 456]
SELECT CAST(JSON '[1,null,456]' AS ARRAY(INTEGER)); -- [1, NULL, 456]
SELECT CAST(JSON '[[1,23],[456]]' AS ARRAY(ARRAY(INTEGER))); -- [[1, 23], [456]]
SELECT CAST(JSON '{"k1":1,"k2":23,"k3":456}' AS MAP(VARCHAR, INTEGER));
-- {k1=1, k2=23, k3=456}
SELECT CAST(JSON '{"v1":123,"v2":"abc","v3":true}' AS ROW(v1 BIGINT, v2 VARCHAR, v3 BOOLEAN));
-- {v1=123, v2=abc, v3=true}
SELECT CAST(JSON '[123,"abc",true]' AS ROW(v1 BIGINT, v2 VARCHAR, v3 BOOLEAN));
-- {v1=123, v2=abc, v3=true}
JSON 배열은 혼합 요소 유형을 가질 수 있고 JSON 맵은 혼합 값 유형을 가질 수 있습니다. 이것은 일부 경우 SQL 배열과 맵으로 캐스팅하는 것을 불가능하게 만듭니다. 이를 해결하기 위해 Trino는 배열과 맵의 부분 캐스팅을 지원합니다:
SELECT CAST(JSON '[[1, 23], 456]' AS ARRAY(JSON));
-- [JSON '[1,23]', JSON '456']
SELECT CAST(JSON '{"k1": [1, 23], "k2": 456}' AS MAP(VARCHAR, JSON));
-- {k1 = JSON '[1,23]', k2 = JSON '456'}
SELECT CAST(JSON '[null]' AS ARRAY(JSON));
-- [JSON 'null']
JSON에서 ROW로 캐스팅할 때는 JSON 배열과 JSON 객체 모두 지원됩니다.
기타 JSON 함수 (Other JSON functions)
앞선 섹션에서 자세히 설명한 함수에 더해 다음 함수를 사용할 수 있습니다:
is_json_scalar(json) → boolean
json이 스칼라(즉 JSON 숫자, JSON 문자열, true, false, null)인지 결정합니다:
SELECT is_json_scalar('1'); -- true
SELECT is_json_scalar('[1, 2, 3]'); -- false
json_array_contains(json, value) → boolean
value가 json(JSON 배열을 담은 문자열)에 존재하는지 결정합니다:
SELECT json_array_contains('[1, 2, 3]', 2); -- true
json_array_get(json_array, index) → json
이 함수의 의미는 손상되어 있습니다. 추출된 요소가 문자열이면 제대로 인용되지 않은 유효하지 않은 JSON 값으로 변환됩니다(값이 따옴표로 둘러싸이지 않고 내부 따옴표도 이스케이프되지 않음). 이 함수 사용을 권장하지 않습니다. 기존 사용에 영향을 주지 않고 고칠 수 없으며 향후 릴리스에서 제거될 수 있습니다. 대신 JSONPath 배열 인덱스 문법을 사용하는 json_query(예: json_query(json_array, 'lax $[0]'))를 사용하세요.
json_array에서 지정된 인덱스의 요소를 반환합니다. 인덱스는 0부터 시작합니다:
SELECT json_array_get('["a", [3, 9], "c"]', 0); -- JSON 'a' (invalid JSON)
SELECT json_array_get('["a", [3, 9], "c"]', 1); -- JSON '[3,9]'
이 함수는 배열 끝에서 셈하는 음수 인덱스도 지원합니다:
SELECT json_array_get('["c", [3, 9], "a"]', -1); -- JSON 'a' (invalid JSON)
SELECT json_array_get('["c", [3, 9], "a"]', -2); -- JSON '[3,9]'
지정된 인덱스의 요소가 존재하지 않으면 null을 반환합니다:
SELECT json_array_get('[]', 0); -- NULL
SELECT json_array_get('["a", "b", "c"]', 10); -- NULL
SELECT json_array_get('["c", "b", "a"]', -10); -- NULL
json_array_length(json) → bigint
json(JSON 배열을 담은 문자열)의 배열 길이를 반환합니다:
SELECT json_array_length('[1, 2, 3]'); -- 3
json_extract(json, json_path) → json
JSONPath 유사 표현식 json_path를 json(JSON을 담은 문자열)에 대해 평가하고 결과를 JSON 문자열로 반환합니다:
SELECT json_extract(json, '$.store.book');
SELECT json_extract(json, '$.store[book]');
SELECT json_extract(json, '$.store["book name"]');
json_query 함수가 JSON 데이터를 파싱/추출하는 더 강력하고 기능이 풍부한 대안을 제공합니다.
json_extract_scalar(json, json_path) → varchar
json_extract()처럼 동작하지만 결과 값을 JSON으로 인코딩하는 대신 문자열로 반환합니다. json_path가 참조하는 값은 스칼라(부울, 숫자 또는 문자열)여야 합니다.
SELECT json_extract_scalar('[1, 2, 3]', '$[2]');
SELECT json_extract_scalar(json, '$.store.book[0].author');
json_format(json) → varchar
입력 JSON 값에서 직렬화된 JSON 텍스트를 반환합니다. 이것은 json_parse()의 역함수입니다:
SELECT json_format(JSON '[1, 2, 3]'); -- '[1,2,3]'
SELECT json_format(JSON '"a"'); -- '"a"'
json_format()과 CAST(json AS VARCHAR)은 완전히 다른 의미를 가집니다. json_format()은 입력 JSON 값을 RFC 7159를 따르는 JSON 텍스트로 직렬화합니다. JSON 값은 JSON 객체, JSON 배열, JSON 문자열, JSON 숫자, true, false, null일 수 있습니다.
CAST(json AS VARCHAR)는 JSON 값을 대응하는 SQL VARCHAR 값으로 캐스팅합니다. JSON 문자열, JSON 숫자, true, false, null의 경우 캐스트 동작은 대응하는 SQL 유형과 같습니다. JSON 객체와 JSON 배열은 VARCHAR로 캐스팅할 수 없습니다:
SELECT CAST(JSON '{"a": 1, "b": 2}' AS VARCHAR); -- ERROR!
SELECT CAST(JSON '[1, 2, 3]' AS VARCHAR); -- ERROR!
SELECT CAST(JSON '"abc"' AS VARCHAR); -- 'abc' (the double quote is gone)
SELECT CAST(JSON '42' AS VARCHAR); -- '42'
SELECT CAST(JSON 'true' AS VARCHAR); -- 'true'
SELECT CAST(JSON 'null' AS VARCHAR); -- NULL
json_parse(string) → json
입력 JSON 텍스트에서 역직렬화된 JSON 값을 반환합니다. 이것은 json_format()의 역함수입니다:
SELECT json_parse('[1, 2, 3]'); -- JSON '[1,2,3]'
SELECT json_parse('"abc"'); -- JSON '"abc"'
json_parse()과 CAST(string AS JSON)은 완전히 다른 의미를 가집니다. json_parse()은 RFC 7159를 따르는 JSON 텍스트를 기대하고 JSON 텍스트에서 역직렬화된 JSON 값을 반환합니다.
SELECT json_parse('not_json'); -- ERROR!
SELECT json_parse('["a": 1, "b": 2]'); -- JSON '["a": 1, "b": 2]'
SELECT json_parse('[1, 2, 3]'); -- JSON '[1,2,3]'
SELECT json_parse('"abc"'); -- JSON '"abc"'
SELECT json_parse('42'); -- JSON '42'
SELECT json_parse('true'); -- JSON 'true'
SELECT json_parse('null'); -- JSON 'null'
CAST(string AS JSON)은 어떤 VARCHAR 값도 입력으로 받아 그 값을 입력 문자열로 설정한 JSON 문자열을 반환합니다.
json_size(json, json_path) → bigint
json_extract()처럼 동작하지만 값의 크기를 반환합니다. 객체나 배열의 경우 크기는 멤버 수이고 스칼라 값의 크기는 0입니다.
SELECT json_size('{"x": {"a": 1, "b": 2}}', '$.x'); -- 2
SELECT json_size('{"x": [1, 2, 3]}', '$.x'); -- 3
SELECT json_size('{"x": {"a": 1, "b": 2}}', '$.x.a'); -- 0
더 알아보기 (Learn more)
JSON 데이터를 다룰 때 문자열 함수와 변환 함수도 함께 살펴보세요. JSON 경로 언어는 SQL과 문법이 다르므로, json_exists/json_query/json_value의 사용 예시를 꼭 참고해보세요.