oracle 12c json

1. json 생성 (기존 테이블로 부터)

1) json_object

2) json_objectagg

3) json_array

4) json_arrayagg

select json_object (

           'department' value d.department_name,

           'employees' value json_arrayagg (

              json_object (

                'name' value first_name || ',' || last_name, 

                'job' value job_title )))

from (select *  from hr.departments fetch first 10 rows only) d, hr.employees e, hr.jobs j

where d.department_id = e.department_id 

and e.job_id = j.job_id 

group by d.department_name;

2. json 저장 ( varchar2, clob, blob )

-- clob으로 생성

create table json_doc_t1 (

     id     raw(16) not null,

     data  clob,

     constraint json_doc_t1_pk primary key (id),

     constraint json_doc_t1_chk  check  (data is json)

);

-- varchar2(4000)으로 생성

create table json_doc_t2 (

id    raw(16) not null,

data varchar2(4000),

     constraint json_doc_t2_pk  primary key(id),

     constraint json_doc_t2_chk  check (data is json)

);

insert into json_doc_t1 (id, data) 

values (sys_guid(),    '{  "firstname" : "t1", "lastname" : "test",  "address" : {  "streect" : "t1", "city" : "seoul", "country: "korea" }  }' );

insert into json_doc_t1 (id, data) 

values (sys_guid(),    '{  "firstname" : "t2", "lastname" : "test",  "address" : {  "streect" : "t2", "city" : "seoul", "country: "korea" }  }' );

insert into json_doc_t1 (id, data) 

values (sys_guid(),    '{  "firstname" : "t3", "lastname" : "test",  "address" : {  "streect" : "t3", "city" : "",  }  }' );

1) dot으로 접근  (옵티마이저가 json_value, json_query등을 사용하여 변환하여 수행)

select a.data.firstname, a.data.lastname, a.data.address

from json_doc_t1 a;

SELECT value FROM v$diag_info WHERE  name = 'Default Trace File';

ALTER SESSION SET EVENTS '10053 trace name context forever';

select a.data.firstname, a.data.lastname, a.data.address

from json_doc_t1 a;

ALTER SESSION SET EVENTS '10053 trace name context off';

2) json_value로 접근  (scala 형태만 return, 배열등은 null로 리턴)

select json_value(data, '$.firstname') as firstname,

        json_value(data, '$.lastname') as lastname,

        json_value(data, '$.address') as address  -- null로 리턴, error on error 설정시 에러

from json_doc_t1 a

where json_value(data, '$firsname') is not null;

* JSON 데이터중 null 체크 (json_exists 사용)

select json_value(data, '$.firstname') as firstname,

        json_value(data, '$.lastname') as lastname,

        json_value(data, '$.address') as address  -- null로 리턴, error on error 설정시 에러

from json_doc_t1 a

where json_value(data, '$.address.city') is null and json_exists(data.address, '$.city'  false on error);

* JSON 필트 없는항목 체크 (json_exists 사용) 

select json_value(data, '$.firstname') as firstname,

        json_value(data, '$.lastname') as lastname,

        json_value(data, '$.address') as address  -- null로 리턴, error on error 설정시 에러

from json_doc_t1 a

where not json_exists(data.address, '$.country'  false on error);

3) json_query로 접근 (json_value 반대,  한개 이상의 값을 포함한 json 반환)

select json_query(data, '$.firstname') as firstname,    -- null로 리턴

        json_query(data, '$.address'  returning varchar2(500) with wrapper ) as address  -- json string으로 리턴

from json_doc_t1;

4) json_table로 접근 (dot 또는 json_ 함수로 여러번 써야 할때)

select t.firstname,  t.lastname,

from json_doc_t1,

       json_table(data, '$'  columns( firstname varchar2(50)  path '$.firstname',

                                              lastname, varchar2(50)  path '$.lastname' )) t;

-- view 로 생성

create or replace view json_view as 

select t.firstname,  t.lastname,

from json_doc_t1,

       json_table(data, '$'  columns( firstname varchar2(50)  path '$.firstname',

                                              lastname, varchar2(50)  path '$.lastname' )) t;

3. json index

   --function-based index 생성

   create index  json_doc_idx1  on json_doc_t1 (json_value(data, '$.address.street'  returning varchar2 error on error);

     

   --복합 인덱스 생성

   create index json_doc_idx2 on json_doc_t1 (json_value(data, '$.firstname' returning varchar2(50)), 

                                                           json_value(data, '$.lastname' returning varchar2(50)) );