[오라클 레퍼런스 함수] JSON_OBJECT - JSON 객체의 키-값 쌍 반환

★

 

 

 

JSON_OBJECT

 

구문

JSON_OBJECT ( JSON_OBJECT_content ) JSON | { JSON_OBJECT_content }

 

json_object_content::=

JSON_OBJECT
    ( * | entry [ , entry ] ... )
    [ JSON_on_null_clause ] [ JSON_returning_clause ]
    [ STRICT ]
    [ WITH UNIQUE KEYS ]  

 

entry::=

( regular_entry [ format_clause ] ) | wildcard

 

regular_entry::=

[ KEY ] expr VALUE expr 
    | expr [ ":" expr ]

 

format_clause::=

FORMAT JSON

 

wildcard::=

[ id "." ] id "." "*"

 

JSON_on_null_clause::=

{ NULL | ABSENT } ON NULL

 

JSON_returning_clause::=

RETURNING 
 {  VARCHAR2 [( size [BYTE | CHAR] )] 
   | CLOB 
   | BLOB 
 } 

 

목적

SQL/JSON 함수인 JSON_OBJECT는 입력으로 키-값 쌍(key-value pairs)의 시퀀스 또는 하나의 객체 유형 인스턴스를 받습니다. 컬렉션 유형은 JSON_OBJECT에 전달할 수 없습니다.

이 함수는 각 키-값 쌍(key-value pairs)을 가진 JSON 객체를 반환합니다.

 

entry

regular_entry 절은 속성 키-값 쌍을 지정하는 데 사용됩니다.

 

regular_entry

• KEY는 선택적이며 의미상 명확성을 위해 제공됩니다.
• 선택적인 expr을 사용하여 속성 키 이름을 대소문자를 구분하여 지정할 수 있습니다.
• expr을 사용하여 속성 값(value)을 지정합니다. expr에는 SQL 숫자 리터럴, 텍스트 리터럴, 날짜 또는 타임스탬프로 평가되는 표현식을 지정할 수 있습니다. 날짜와 타임스탬프 데이터 유형은 ISO 날짜 형식을 따르는 JSON 문자열로 생성된 JSON 객체나 배열에 인쇄됩니다. expr이 숫자 리터럴로 평가되면 결과 속성 값은 JSON 숫자 값이 되며, 그렇지 않으면 결과 속성 값은 더블 쿼테이션 마크로 묶인 대소문자 구분하는 JSON 문자열 값이 됩니다.
  
  JSON_OBJECT 항목을 구분하기 위해 콜론(:)을 사용할 수 있습니다.

 

예시

SELECT JSON_OBJECT(
'name' : first_name || ' ' || last_name,
'email' : email,
'phone' : phone_number,
'hire_date' : hire_date
)
FROM employees
WHERE employee_id = 140;

 

format_clause

입력 표현식 뒤에 FORMAT JSON을 지정하여 해당 값이 JSON 데이터를 나타낸다고 선언하고, 따라서 출력에서 인용부호로 둘러싸이지 않을 것임을 나타냅니다.

 

wildcard

와일드카드 항목은 여러 열을 선택하며, * 형태로 표시할 수 있습니다. 는 table., view.*, t_alias.*와 같이 사용할 수 있습니다. 와일드카드 항목을 사용하면 쿼리에서 모든 열을 명시적으로 지정하지 않고도 테이블, 서브쿼리 또는 뷰에서 모든 열을 JSON 객체에 매핑할 수 있습니다. 이 경우 와일드카드 항목은 select_list에서 직접 사용하는 방식과 동일하게 사용됩니다.

 

예시1

결과적으로 생성된 JSON 객체에서 키 이름은 해당하는 열의 이름과 동일합니다.

SELECT JSON_OBJECT(*)
FROM employees
WHERE employee_id = 140;

 

출력1

{"EMPLOYEE_ID":140,"FIRST_NAME":"Joshua","LAST_NAME":"Patel","EMAIL":"JPAT
EL","PHONE_NUMBER":"650.121.1834","HIRE_DATE":"2006-04-
06T00:00:00","JOB_ID":"ST_CLERK","SALARY":2500,"COMMISSION_PCT":null,"MAN
AGER_ID":123,"DEPARTMENT_ID":50}

 

예시2

이 쿼리는 조인 쿼리에서 특정 테이블의 열을 선택합니다.

SELECT JSON_OBJECT('NAME' VALUE first_name, d.*)
FROM employees e, departments d
WHERE e.department_id = d.department_id
AND e.employee_id =140

 

예시3

이 쿼리는 departments 테이블을 하나의 JSON 배열 값으로 변환합니다.

SELECT JSON_ARRAYAGG(JSON_OBJECT(*))
FROM departments

 

JSON_on_null_clause

이 절은 expr이 null로 평가될 때 이 함수의 동작을 지정하는 데 사용됩니다.

• NULL ON NULL - NULL ON NULL이 지정되면 JSON NULL 값을 해당 키의 값으로 사용합니다.

SELECT JSON_OBJECT('key1' VALUE NULL)  evaluates to  {"key1" :  null}

 

• ABSENT ON NULL - 이 절을 지정하면 함수가 JSON 객체에서 해당 속성 키-값 쌍을 생략합니다.

 

JSON_returning_clause

이 절은 반환 값의 유형을 지정하는 데 사용됩니다. 다음 중 하나를 선택할 수 있습니다:

 

• VARCHAR2: 크기를 바이트나 문자의 개수로 지정합니다. 기본값은 바이트입니다. 이 절을 생략하거나 size 값을 지정하지 않으면 JSON_ARRAY는 VARCHAR2(4000) 유형의 문자열을 반환합니다. 자세한 내용은 VARCHAR2 Data Type을 참조하세요. SQL의 다른 위치에서 VARCHAR2 데이터 형식을 지정할 때는 크기를 지정해야 합니다. 그러나 JSON_returning_clause에서는 크기를 생략할 수 있습니다.
• CLOB: 단일 바이트 또는 멀티바이트 문자를 포함하는 문자형 큰 객체를 반환합니다.
• BLOB: AL32UTF8 문자 집합의 이진 형태 큰 객체를 반환합니다.
• WITH TYPENAME

 

STRICT

STRICT 절을 사용하여 JSON 생성 함수의 출력이 올바른 JSON인지 확인합니다. 확인에 실패하면 구문 오류가 발생합니다.

예제 1: FORMAT JSON을 사용하지 않았기 때문에 출력 문자열은 따옴표 내에 표시됩니다.

SELECT JSON_OBJECT ('name' value 'Foo') FROM DUAL
Output:
JSON_OBJECT('NAME'VALUE'FOO'FORMATJSON)
-------------------------------------------------
{"name":"Foo"}

 

예제 2: FORMAT JSON을 사용한 경우 출력 문자열에 따옴표가 없습니다.

SELECT JSON_OBJECT ('name' value 'Foo' FORMAT JSON ) FROM DUAL
Output:
JSON_OBJECT('NAME'VALUE'FOO'FORMATJSON)
-------------------------------------------------
{"name":Foo}

 

예제 3: FORMAT JSON STRICT를 사용한 경우 JSON 구문 오류가 발생합니다.

SELECT JSON_OBJECT ('name' value 'Foo' FORMAT JSON STRICT ) FROM DUAL
Output:
ORA-40441: JSON syntax error

 

WITH UNIQUE KEYS

WITH UNIQUE KEYS를 지정하여 생성된 JSON 객체가 고유한 키를 가지도록 보장합니다.

 

예시

다음 예제는 각각 두 개의 속성 키-값 쌍을 포함하는 JSON 객체를 반환합니다.

SELECT JSON_OBJECT (
    KEY 'deptno' VALUE d.department_id,
    KEY 'deptname' VALUE d.department_name 
    ) "Department Objects"
  FROM departments d
  ORDER BY d.department_id;

Department Objects
----------------------------------------
{"deptno":10,"deptname":"Administration"}
{"deptno":20,"deptname":"Marketing"}
{"deptno":30,"deptname":"Purchasing"}
{"deptno":40,"deptname":"Human Resources"}
{"deptno":50,"deptname":"Shipping"}
. . .

 

JSON_OBJECT Column Entries

일부 경우에는 키 이름이 테이블 열의 이름과 일치하도록 하여 키 값 표현식에서 열 이름을 반복하지 않도록 할 수 있습니다. 예를 들어:

SELECT JSON_OBJECT(
'first_name' VALUE first_name,
'last_name' VALUE last_name,
'email' VALUE email,
'hire_date' VALUE hire_date
)
FROM employees
WHERE employee_id = 140;

{"first_name":"Joshua","last_name":"Patel","email":"JPATEL","hire_date":"2006-04-
06T00:00:00"}

 

이러한 경우에는 하나의 열 값만 지정하면 해당하는 객체 항목 키가 열의 이름에서 유추될 수 있는 단축키를 사용할 수 있습니다. 예를 들어:

SELECT JSON_OBJECT(first_name, last_name, email, hire_date)
FROM employees
WHERE employee_id = 140; 

{"first_name":"Joshua","last_name":"Patel","email":"JPATEL","hire_date":"2006-04-
06T00:00:00"}

 

열 이름에는 따옴표를 사용하거나 사용하지 않을 수 있습니다. 따옴표를 사용하지 않는 경우 쿼리에 기록된 대소문자를 그대로 사용하여 해당하는 객체 키 값을 생성합니다. 그러나 열 값을 참조하는 용도로는 여전히 대소문자를 구분하지 않습니다. 예를 들어:

SELECT JSON_OBJECT(eMail)
FROM employees
WHERE employee_id = 140

{"eMail":"JPATEL"}

 

대문자 'M'이 열 이름으로 입력한 대로 보존되는 것을 주목하세요.

 

참고:

Generation of JSON Data Using SQL

 

---

출처: 오라클 레퍼런스

원문 링크: Oracle JSON_OBJECT 함수 문서