[오라클 레퍼런스 함수] JSON_VALUE - JSON 속성 값 추출

★

 

JSON_VALUE

 

구문

JSON_VALUE
  ( expr [ FORMAT JSON ] , [ JSON_basic_path_expression ]
    [ JSON_value_returning_clause ] [ JSON_value_on_error_clause ]
    [ JSON_value_on_empty_clause ] [ JSON_value_on_mismatch_clause ]
  )

 

JSON_basic_path_expression::=

(JSON_basic_path_expression: SQL/JSON Path Expressions 참조)

 

JSON_value_returning_clause::=

[ RETURNING JSON_value_return_type ] [ ASCII ]

 

JSON_value_return_type::=

{ VARCHAR2 [ ( size [BYTE | CHAR] [ TRUNCATE ] ) ]
| NUMBER [ ( precision [, scale] ) ]
| DATE
| TIMESTAMP [ WITH TIME ZONE ]
| CLOB
| SDO_GEOMETRY
| JSON_value_return_object_instance
}

 

JSON_value_return_object_instance ::=

object_type_name [ JSON_value_mapper_clause ]

 

JSON_value_mapper_clause ::=

USING CASE-SENSITIVE MAPPING

 

JSON_value_on_error_clause::=

{ ERROR | NULL | DEFAULT literal } ON ERROR

 

JSON_value_on_empty_clause::=

{ ERROR | NULL | DEFAULT literal } ON EMPTY

 

JSON_value_on_mismatch_clause::=

JSON_value_on_mismatch ( 
   ( IGNORE | ERROR | NULL ) 
    ON MISMATCH 
   [  ( (MISSING DATA) | (EXTRA DATA) | (TYPE ERROR) )  ]
  ) ...

 

목적

SQL/JSON 함수인 JSON_VALUE는 JSON 데이터에서 지정된 스칼라 JSON 값을 찾아서 SQL 값으로 반환합니다.

 

참고:

Oracle Database Globalization Support Guide의 부록 C는 이 함수가 문자 값일 때 반환되는 값에 할당된 정렬(collation) 결정 규칙에 대한 정보를 제공합니다.

 

expr

이 절을 사용하여 평가할 JSON 데이터를 지정합니다. expr에는 텍스트 리터럴로 평가되는 표현식을 지정합니다. 만약 expr이 컬럼인 경우, 해당 컬럼은 VARCHAR2, CLOB 또는 BLOB 데이터 유형이어야 합니다. 만약 expr이 null이라면, 함수는 null을 반환합니다.

만약 expr이 엄격한 또는 느슨한 구문을 사용하여 잘 형식화된 JSON 데이터의 텍스트 리터럴이 아니라면, 함수는 기본적으로 null을 반환합니다. 이 기본 동작을 무시하려면 JSON_value_on_error_clause를 사용할 수 있습니다. JSON_value_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_value_returning_clause

이 절을 사용하여 이 함수에 의해 반환되는 값의 데이터 유형과 형식을 지정합니다.

 

RETURNING

RETURNING 절을 사용하여 반환 값의 데이터 유형을 지정합니다. 이 절을 생략하면 JSON_VALUE는 VARCHAR2(4000) 유형의 값을 반환합니다.

 

JSON_value_return_type ::=

JSON_value_return_type을 사용하여 다음과 같은 데이터 유형을 지정할 수 있습니다:

• VARCHAR2[(size [BYTE,CHAR])]
  이 데이터 유형을 지정하면 이 함수에 의해 반환된 스칼라 값은 문자 또는 숫자 값일 수 있습니다. 숫자 값은 암시적으로 VARCHAR2로 변환됩니다. SQL의 다른 위치에서 VARCHAR2 데이터 유형을 지정할 때는 크기를 지정해야 합니다. 그러나 이 절에서는 크기를 생략할 수 있습니다. 이 경우 JSON_VALUE는 VARCHAR2(4000) 유형의 값을 반환합니다.
  
  VARCHAR2(N) 다음에 옵션으로 TRUNCATE 절을 지정하여 반환 값을 N 글자로 자를 수 있습니다. 반환 값이 N 글자보다 큰 경우에만 적용됩니다.
  
  TRUNCATE 절에 관한 참고 사항:
  º 문자열 값이 너무 길 경우 ORA-40478이 발생합니다.
  º TRUNCATE가 존재하고 반환 값이 문자 유형이 아닌 경우 컴파일 시간 오류가 발생합니다.
  º TRUNCATE가 FORMAT JSON과 함께 사용되면 반환 값에 문법적으로 올바르지 않은 JSON 데이터가 포함될 수 있습니다.
  º TRUNCATE는 EXISTS와 함께 작동하지 않습니다.
• CLOB
  이 데이터 유형을 지정하여 단일 바이트 또는 멀티 바이트 문자가 포함된 문자 큰 객체를 반환합니다.
• NUMBER[(precision [, scale])]
  이 데이터 유형을 지정하면 이 함수에 의해 반환된 스칼라 값은 반드시 숫자 값이어야 합니다. 스칼라 값은 또한 JSON 부울 값일 수 있습니다. 그러나 JSON 부울 값을 반환하는 NUMBER는 더 이상 권장되지 않습니다.
• DATE
  이 데이터 유형을 지정하면 이 함수에 의해 반환된 스칼라 값은 DATE 데이터 유형으로 암시적으로 변환될 수 있는 문자 값이어야 합니다.
• TIMESTAMP
  이 데이터 유형을 지정하면 이 함수에 의해 반환된 스칼라 값은 TIMESTAMP 데이터 유형으로 암시적으로 변환될 수 있는 문자 값이어야 합니다.
• TIMESTAMP WITH TIME ZONE
  이 데이터 유형을 지정하면 이 함수에 의해 반환된 스칼라 값은 TIMESTAMP WITH TIME ZONE 데이터 유형으로 암시적으로 변환될 수 있는 문자 값이어야 합니다.
• SDO_GEOMETRY
  이 데이터 유형은 Oracle Spatial and Graph 데이터에 사용됩니다. 이 데이터 유형을 지정하면 expr은 GeoJSON 데이터를 포함하는 텍스트 리터럴로 평가되어야 합니다. GeoJSON은 JSON 형식으로 지리 데이터를 인코딩하는 형식입니다. 이 데이터 유형을 지정하면 이 함수에 의해 반환된 스칼라 값은 SDO_GEOMETRY 유형의 객체여야 합니다.
• JSON_value_return_object_instance
  JSON_VALUE가 JSON 객체를 대상으로하고 사용자 정의 SQL 객체 유형을 반환 유형으로 지정하는 경우, JSON_VALUE는 object_type_name에서 해당 객체 유형의 인스턴스를 반환합니다.
  예제는 Using JSON_VALUE To Instantiate a User-Defined Object Type Instance을 참조하십시오.

참고:

• 개념적 이해를 위한 SQL/JSON Function JSON_VALUE.
• 앞서 언급한 데이터 유형에 대한 자세한 정보는 "Data Types"을 참조하십시오.
• 반환 값을 보유하기에 충분히 크지 않은 경우, 이 함수는 기본적으로 null을 반환합니다. 이 기본 동작을 무시하려면 JSON_value_on_error_clause를 사용할 수 있습니다. JSON_value_on_error_clause를 참조하십시오.

 

ASCII

ASCII를 지정하면 반환 값에 포함된 non-ASCII 유니코드 문자를 자동으로 표준 ASCII 유니코드 이스케이프 시퀀스(ASCII Unicode escape sequences)를 사용하여 이스케이프합니다.

 

JSON_value_on_error_clause

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

• expr이 엄격한 또는 느슨한 JSON 구문을 사용하여 잘 형식화되지 않은 JSON 데이터인 경우
• SQL/JSON 경로 표현식을 사용하여 JSON 데이터를 평가할 때 비 스칼라(nonscalar) 값이 발견되는 경우
• SQL/JSON 경로 표현식을 사용하여 JSON 데이터를 평가할 때 일치하는 항목이 없는 경우. 이 유형의 오류에 대한 동작을 JSON_value_on_empty_clause를 지정하여 재정의할 수 있습니다.
• 반환 값 데이터 유형이 반환 값 저장에 충분히 크지 않은 경우

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

• NULL ON ERROR - 오류가 발생할 때 null을 반환합니다. 이것이 기본값입니다.
• ERROR ON ERROR - 오류가 발생할 때 적절한 Oracle 오류를 반환합니다.
• DEFAULT literal ON ERROR - 오류가 발생할 때 literal을 반환합니다. literal의 데이터 유형은 이 함수가 반환하는 값의 데이터 유형과 일치해야 합니다.

 

JSON_value_on_empty_clause

이 절을 사용하여 SQL/JSON 경로 표현식을 사용하여 JSON 데이터를 평가할 때 일치하는 항목이 없는 경우 이 함수가 반환하는 값을 지정합니다. 이 절을 사용하여 JSON_value_on_error_clause에서 지정한 결과와는 다른 결과를 지정할 수 있습니다.

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

• NULL ON EMPTY - 일치하는 항목을 찾지 못한 경우 null을 반환합니다.
• ERROR ON EMPTY - 일치하는 항목을 찾지 못한 경우 적절한 Oracle 오류를 반환합니다.
• DEFAULT literal ON EMPTY - 일치하는 항목을 찾지 못한 경우 literal을 반환합니다. literal의 데이터 유형은 이 함수가 반환하는 값의 데이터 유형과 일치해야 합니다.

이 절을 생략하면 일치하는 항목을 찾지 못한 경우 JSON_value_on_error_clause에서 지정한 값을 반환합니다.

 

JSON_value_on_mismatch_clause

JSON_value_on_mismatch_clause를 두 가지 방법으로 사용할 수 있습니다: 일반적으로 또는 각각의 경우에 따라.

일반적으로 사용하여 추가 데이터, 누락된 데이터 및 유형 오류와 같은 모든 오류 경우에 적용할 수 있습니다.

각각의 경우에 다른 ON MISMATCH 절을 지정하여 각 경우에 따라 사용할 수도 있습니다. 예를 들어:

IGNORE ON MISMATCH (EXTRA DATA)

 

ERROR ON MISMATCH ( MISSING DATA, TYPE ERROR)

 

예제

다음 쿼리는 속성 이름이 a인 멤버의 값을 반환합니다. RETURNING 절이 지정되지 않았으므로, 해당 값은 VARCHAR2(4000) 데이터 유형으로 반환됩니다:

SELECT JSON_VALUE('{a:100}', '$.a') AS value
  FROM DUAL;

VALUE
-----
100

 

다음 쿼리는 속성 이름이 a인 멤버의 값을 반환합니다. RETURNING NUMBER 절이 지정되었으므로, 해당 값은 NUMBER 데이터 유형으로 반환됩니다:

SELECT JSON_VALUE('{a:100}', '$.a' RETURNING NUMBER) AS value
  FROM DUAL;

     VALUE
----------
       100

 

다음 쿼리는 속성 이름이 a인 멤버의 값 내에 있는 속성 이름이 b인 멤버의 값을 반환합니다:

SELECT JSON_VALUE('{a:{b:100}}', '$.a.b') AS value
  FROM DUAL;

VALUE
-----
100

 

다음 쿼리는 어떤 객체 내에 있는 속성 이름이 d인 멤버의 값을 반환합니다:

SELECT JSON_VALUE('{a:{b:100}, c:{d:200}, e:{f:300}}', '$.*.d') AS value
  FROM DUAL;

VALUE
-----
200

 

다음 쿼리는 배열의 첫 번째 요소의 값을 반환합니다:

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

VALUE
-----
0

 

다음 쿼리는 속성 이름이 a인 멤버의 값인 배열에서 세 번째 요소의 값을 반환합니다:

SELECT JSON_VALUE('{a:[5, 10, 15, 20]}', '$.a[2]') AS value
  FROM DUAL;

VALUE
-----
15

 

다음 쿼리는 배열 내의 두 번째 객체에서 속성 이름이 a인 멤버의 값을 반환합니다:

SELECT JSON_VALUE('[{a:100}, {a:200}, {a:300}]', '$[1].a') AS value
  FROM DUAL;

VALUE
-----
200

 

다음 쿼리는 배열 내의 모든 객체에서 속성 이름이 c인 멤버의 값을 반환합니다:

SELECT JSON_VALUE('[{a:100}, {b:200}, {c:300}]', '$[*].c') AS value
  FROM DUAL;

VALUE
-----
300

 

다음 쿼리는 property 이름이 lastname인 멤버의 값을 반환하려고 시도합니다. 그러나 지정된 JSON 데이터에는 해당하는 멤버가 없으므로 일치하는 항목이 없습니다. ON ERROR 절이 지정되지 않았으므로 문장은 기본값인 NULL ON ERROR를 사용하고 null을 반환합니다.

SELECT JSON_VALUE('{firstname:"John"}', '$.lastname') AS "Last Name"
  FROM DUAL;

Last Name
---------

 

다음 쿼리는 지정된 JSON에 존재하지 않는 property 이름이 lastname인 멤버의 값을 반환하려고 시도하면 오류가 발생합니다. ON ERROR 절이 지정되었기 때문에 이 문장은 지정된 텍스트 리터럴을 반환합니다.

SELECT JSON_VALUE('{firstname:"John"}', '$.lastname'
                  DEFAULT 'No last name found' ON ERROR) AS "Last Name"
  FROM DUAL;

Last Name
---------
No last name found

 

---

출처: 오라클 레퍼런스

원문 링크: Oracle JSON_VALUE 함수 문서