[오라클 레퍼런스 함수] JSON_QUERY - JSON 문서에서 일부 내용 추출

★

 

 

 

JSON_QUERY

 

구문

JSON_QUERY
  ( expr [ FORMAT JSON ], JSON_basic_path_expression
    [ JSON_query_returning_clause ] [ JSON_query_wrapper_clause ]
    [ JSON_query_on_error_clause ] [ JSON_query_on_empty_clause ]
  )

 

JSON_query_returning_clause::=

[ RETURNING JSON_query_return_type ] [ PRETTY ] [ ASCII ]

 

JSON_query_return_type::=

VARCHAR2 [ ( size [BYTE | CHAR] [ TRUNCATE ]) ]
| CLOB
| BLOB

 

JSON_query_wrapper_clause::=

WITHOUT [ ARRAY ] WRAPPER
| WITH [ UNCONDITIONAL | CONDITIONAL ] [ ARRAY ] WRAPPER

 

JSON_query_on_error_clause::=

{ ERROR
| NULL
| EMPTY
| EMPTY ARRAY
| EMPTY OBJECT
} ON ERROR

 

JSON_query_on_empty_clause::=

{ ERROR
| NULL
| EMPTY
| EMPTY ARRAY
| EMPTY OBJECT
} ON EMPTY

 

목적

JSON_QUERY는 JSON 데이터에서 하나 이상의 값을 선택하여 반환합니다. 이 함수를 사용하여 JSON 문서의 일부를 검색할 수 있습니다.

 

참고:

• Query JSON Data
• Oracle Database Globalization Support Guide의 부록 C는 JSON_QUERY가 반환하는 문자 값에 할당된 정렬(collation) 결정 규칙에 대한 정보를 제공합니다.

 

expr

expr을 사용하여 조회하려는 JSON 데이터를 지정합니다.

expr은 SQL 데이터 유형 중 하나인 JSON, VARCHAR2, CLOB 또는 BLOB의 인스턴스를 반환하는 SQL 표현식입니다. 테이블이나 뷰 열 값, PL/SQL 변수 또는 적절한 캐스팅을 사용한 바인드 변수가 될 수 있습니다.

expr이 null이면 함수는 null을 반환합니다.

expr이 strict 또는 lax 구문을 사용하여 올바르게 형식화된 JSON 데이터의 텍스트 리터럴이 아닌 경우, 기본적으로 함수는 null을 반환합니다. JSON_query_on_error_clause를 사용하여이 기본 동작을 무시할 수 있습니다. JSON_query_on_error_clause을 참조하십시오.

 

FORMAT JSON

expr이 BLOB 데이터 유형의 열인 경우에는 FORMAT JSON을 지정해야 합니다.

 

JSON_basic_path_expression

이 절을 사용하여 SQL/JSON 경로 식을 지정합니다. 함수는 경로 식을 사용하여 expr을 평가하고 경로 식과 일치하거나 만족하는 하나 이상의 JSON 값이 있는지 찾습니다. 경로 식은 텍스트 리터럴이어야 합니다. JSON_basic_path_expression의 전체 의미에 대해서는 Oracle Database JSON Developer's Guide를 참조하십시오.

 

JSON_query_returning_clause

이 절을 사용하여 함수가 반환하는 문자열의 데이터 유형과 형식을 지정합니다.

 

RETURNING

RETURNING 절을 사용하여 반환되는 인스턴스의 데이터 유형을 지정할 수 있습니다. VARCHAR2, CLOB 또는 BLOB 중 하나의 데이터 유형을 사용할 수 있습니다.

모든 입력 데이터 유형에 대해 기본 반환 유형은 VARCHAR2(4000)입니다.

SQL의 다른 위치에서 VARCHAR2 데이터 유형을 지정할 때 크기를 지정해야 합니다. 그러나 이 절에서는 크기를 생략할 수 있습니다. 이 경우 JSON_QUERY는 VARCHAR2(4000) 유형의 문자열을 반환합니다.

 

"VARCHAR2 Data Type"을 참조하여 자세한 정보를 확인하십시오.

반환된 문자열을 보유하기에 충분한 크기가 아닌 경우 이 함수는 기본적으로 null을 반환합니다. 이 기본 동작을 무시하려면 JSON_query_on_error_clause를 사용할 수 있습니다. JSON_query_on_error_clause를 참조하십시오.

 

PRETTY

PRETTY를 지정하면 반환되는 문자열을 읽기 쉽게 출력하기 위해 줄 바꿈 문자와 들여쓰기를 삽입합니다.

 

ASCII

ASCII를 지정하면 반환되는 문자열에서 비-ASCII 유니코드 문자를 자동으로 표준 ASCII 유니코드 이스케이프 시퀀스를 사용하여 이스케이프 처리합니다.

 

JSON_query_wrapper_clause

이 절을 사용하여 이 함수가 경로 식에 의해 일치하는 값을 배열 래퍼로 래핑하는지 여부를 제어할 수 있습니다. 즉, 값을 대괄호([])로 묶어서 나열합니다.

 

• WITHOUT WRAPPER 지정하면 배열 래퍼를 제외합니다. 이 절을 사용할 수 있는 경우는 경로 식이 단일 JSON 객체 또는 JSON 배열과 일치하는 경우에만 해당합니다. 이것이 기본값입니다.
• WITH WRAPPER를 지정하면 배열 래퍼를 포함시킵니다. 이 절을 사용할 경우 경로 식이 단일 스칼라 값(JSON 객체 또는 JSON 배열이 아닌 값) 또는 여러 값에 일치하는 경우에 지정해야 합니다.
• WITH UNCONDITIONAL WRAPPER 절을 지정하는 것은 WITH WRAPPER 절을 지정하는 것과 동일합니다. UNCONDITIONAL 키워드는 의미상의 명확성을 위해 제공됩니다.
• WITH CONDITIONAL WRAPPER를 지정하면 경로 식이 단일 스칼라 값 또는 여러 값에 일치하는 경우에만 배열 래퍼를 포함합니다. 경로 식이 단일 JSON 객체나 JSON 배열과 일치하는 경우 배열 래퍼가 제외됩니다.

ARRAY 키워드는 선택 사항이며 의미상의 명확성을 위해 제공됩니다.

함수가 단일 스칼라 값 또는 여러 값에 대한 결과를 반환하며 WITH [UNCONDITIONAL | CONDITIONAL] WRAPPER를 지정하지 않은 경우 기본적으로 함수는 null을 반환합니다. 이 기본 동작을 무시하려면 JSON_query_on_error_clause를 사용할 수 있습니다. JSON_query_on_error_clause를 참조하세요.

 

JSON_query_on_error_clause

이 절을 사용하여 다음과 같은 오류가 발생할 때 이 함수가 반환하는 값을 지정할 수 있습니다:

• expr이 엄격한 또는 유연한 JSON 구문을 사용하여 잘 형식화되지 않은 JSON 데이터인 경우
• SQL/JSON 경로 식을 사용하여 JSON 데이터를 평가할 때 일치하는 항목을 찾을 수 없는 경우. 이 유형의 오류에 대한 동작은 JSON_query_on_empty_clause를 지정하여 무시할 수 있습니다.
• 반환 값 데이터 유형이 반환 문자열을 저장하기에 충분히 크지 않은 경우
• 함수가 단일 스칼라 값 또는 여러 값에 일치하고 WITH [UNCONDITIONAL | CONDITIONAL] WRAPPER 절이 지정되지 않은 경우

다음 절을 지정할 수 있습니다:

• NULL ON ERROR - 오류가 발생한 경우 null을 반환합니다. 이것이 기본값입니다.
• ERROR ON ERROR - 오류가 발생한 경우 적절한 Oracle 오류를 반환합니다.
• EMPTY ON ERROR - 이 절을 지정하는 것은 EMPTY ARRAY ON ERROR를 지정하는 것과 동일합니다.
• EMPTY ARRAY ON ERROR - 오류가 발생한 경우 빈 JSON 배열 ([])을 반환합니다.
• EMPTY OBJECT ON ERROR - 오류가 발생한 경우 빈 JSON 객체 ({})을 반환합니다.

 

JSON_query_on_empty_clause

이 절을 사용하여 SQL/JSON 경로 식을 사용하여 JSON 데이터를 평가할 때 일치하는 내용이 없는 경우에이 함수가 반환하는 값을 지정할 수 있습니다. 이 절을 사용하여 JSON_query_on_error_clause로 지정된 결과와 다른 결과를 지정할 수 있습니다.

다음 절을 지정할 수 있습니다.

• NULL ON EMPTY - 일치하는 내용이 없을 때 null을 반환합니다.
• ERROR ON EMPTY - 일치하는 내용이 없을 때 적절한 Oracle 오류를 반환합니다.
• EMPTY ON EMPTY - 이 절을 지정하는 것은 EMPTY ARRAY ON EMPTY를 지정하는 것과 동일합니다.
• EMPTY ARRAY ON EMPTY - 일치하는 내용이 없을 때 빈 JSON 배열([])을 반환합니다.
• EMPTY OBJECT ON EMPTY - 일치하는 내용이 없을 때 빈 JSON 객체({})를 반환합니다.

이 절을 생략하면 JSON_query_on_error_clause가 일치하는 내용이 없을 때 반환되는 값을 결정합니다.

 

예시

다음 쿼리는 컨텍스트 항목 또는 지정된 JSON 데이터 문자열을 반환합니다. 경로 식은 단일 JSON 객체와 일치하며, 이는 배열 래퍼가 필요하지 않습니다. 반환된 값에서 JSON 데이터는 엄격한 JSON 구문으로 변환됩니다. 즉, 객체 속성 이름은 이중 따옴표로 묶입니다.

SELECT JSON_QUERY('{a:100, b:200, c:300}', '$') AS value
  FROM DUAL;

VALUE
--------------------------------------------------------------------------------
{"a":100,"b":200,"c":300}

 

다음 쿼리는 속성 이름이 a인 멤버의 값을 반환합니다. 경로 식은 스칼라 값을 일치시키며, 이 값은 배열 래퍼로 묶여야 합니다. 따라서 WITH WRAPPER 절이 지정되었습니다.

SELECT JSON_QUERY('{a:100, b:200, c:300}', '$.a' WITH WRAPPER) AS value
  FROM DUAL;

VALUE
--------------------------------------------------------------------------------
[100]

 

다음 쿼리는 모든 객체 멤버의 값을 반환합니다. 경로 식은 여러 값을 일치시키며, 이 값들은 배열 래퍼로 묶여야 합니다. 따라서 WITH WRAPPER 절이 지정되었습니다.

SELECT JSON_QUERY('{a:100, b:200, c:300}', '$.*' WITH WRAPPER) AS value
  FROM DUAL;

VALUE
--------------------------------------------------------------------------------
[100,200,300]

 

다음 쿼리는 컨텍스트 항목 또는 지정된 JSON 데이터 문자열을 반환합니다. 경로 식은 단일 JSON 배열과 일치하며, 배열 래퍼가 필요하지 않습니다.

SELECT JSON_QUERY('[0,1,2,3,4]', '$') AS value
  FROM DUAL;

VALUE
--------------------------------------------------------------------------------
[0,1,2,3,4]

 

다음 쿼리는 이전 쿼리와 유사하지만 WITH WRAPPER 절이 지정되었습니다. 따라서 JSON 배열은 배열 래퍼로 둘러싸여 반환됩니다.

SELECT JSON_QUERY('[0,1,2,3,4]', '$' WITH WRAPPER) AS value
  FROM DUAL;

VALUE
--------------------------------------------------------------------------------
[[0,1,2,3,4]]

 

다음 쿼리는 JSON 배열의 모든 요소를 반환합니다. 경로 식은 여러 값과 일치하며, 이 값들은 배열 래퍼로 둘러싸여야 합니다. 따라서 WITH WRAPPER 절이 지정되었습니다.

SELECT JSON_QUERY('[0,1,2,3,4]', '$[*]' WITH WRAPPER) AS value
  FROM DUAL;

VALUE
--------------------------------------------------------------------------------
[0,1,2,3,4]

 

다음 쿼리는 JSON 배열의 인덱스 0, 3에서 5까지의 요소 및 7의 요소를 반환합니다. 경로 식은 여러 값과 일치하며, 이 값들은 배열 래퍼로 둘러싸여야 합니다. 따라서 WITH WRAPPER 절이 지정되었습니다.

SELECT JSON_QUERY('[0,1,2,3,4,5,6,7,8]', '$[0, 3 to 5, 7]' WITH WRAPPER) AS value
  FROM DUAL;

VALUE
--------------------------------------------------------------------------------
[0,3,4,5,7]

 

다음 쿼리는 JSON 배열의 네 번째 요소를 반환합니다. 경로 식은 스칼라 값과 일치하며, 이 값은 배열 래퍼로 둘러싸여야 합니다. 따라서 WITH WRAPPER 절이 지정되었습니다.

SELECT JSON_QUERY('[0,1,2,3,4]', '$[3]' WITH WRAPPER) AS value
  FROM DUAL;

VALUE
--------------------------------------------------------------------------------
[3]

 

다음 쿼리는 JSON 배열의 첫 번째 요소를 반환합니다. WITH CONDITIONAL WRAPPER 절이 지정되었으며, 경로 식은 단일 JSON 객체와 일치합니다. 따라서 반환된 값은 배열로 래핑되지 않습니다. 주의해야 할 점은 반환된 값에서 JSON 데이터가 엄격한 JSON 구문으로 변환된다는 것입니다. 즉, 객체 속성 이름이 이중 따옴표로 둘러싸인 것입니다.

SELECT JSON_QUERY('[{a:100},{b:200},{c:300}]', '$[0]'
       WITH CONDITIONAL WRAPPER) AS value
  FROM DUAL;

VALUE
--------------------------------------------------------------------------------
{"a":100}

 

다음 쿼리는 JSON 배열의 모든 요소를 반환합니다. WITH CONDITIONAL WRAPPER 절이 지정되었으며, 경로 식은 여러 개의 JSON 객체와 일치합니다. 따라서 반환된 값은 배열로 래핑됩니다.

SELECT JSON_QUERY('[{"a":100},{"b":200},{"c":300}]', '$[*]'
       WITH CONDITIONAL WRAPPER) AS value
  FROM DUAL;

VALUE
--------------------------------------------------------------------------------
[{"a":100},{"b":200},{"c":300}]

 

다음 쿼리는 이전 쿼리와 유사하지만, 반환된 값의 데이터 유형은 VARCHAR2(100)입니다.

SELECT JSON_QUERY('[{"a":100},{"b":200},{"c":300}]', '$[*]'
       RETURNING VARCHAR2(100) WITH CONDITIONAL WRAPPER) AS value
  FROM DUAL;

VALUE
--------------------------------------------------------------------------------
[{"a":100},{"b":200},{"c":300}]

 

다음 쿼리는 JSON 배열에서 네 번째 요소를 반환합니다. 그러나 제공된 JSON 배열에는 네 번째 요소가 포함되어 있지 않아 오류가 발생합니다. EMPTY ON ERROR 절이 지정되었습니다. 따라서 쿼리는 빈 JSON 배열을 반환합니다.

SELECT JSON_QUERY('[{"a":100},{"b":200},{"c":300}]', '$[3]'
       EMPTY ON ERROR) AS value
  FROM DUAL;

VALUE
--------------------------------------------------------------------------------
[]

 

---

출처: 오라클 레퍼런스

원문 링크: Oracle JSON_QUERY 함수 문서