SQLをつかってJSONデータ生成（Oracle12c R2 新機能 SQL/JSON関数）

# RDBMS に格納されているデータをJSONフォーマットで利用したいニーズにこたえます。

## Oracle Database 12c R2 から追加されたSQL/JSON関数
- JSON_OBJECT
- JSON_OBJECTAGG
- JSON_ARRAY
- JSON_ARRAYAGG

#### JSON_OBJECT
キーバリューペアを指定し、JSONオブジェクトを作成します。
` json_object('id' value 1, 'name' value 'myobject') `

``` sql
SQL> select json_object('id' value 1, 'name' value 'myobject') from dual;

JSON_OBJECT('ID'VALUE1,'NAME'VALUE'MYOBJECT')
--------------------------------------------------------------------------------
{"id":1,"name":"myobject"}
```

``` sql
SELECT JSON_OBJECT (
         KEY 'dept-number' VALUE d.deptno,
         KEY 'dept-name' VALUE d.dname,
         KEY 'location' VALUE d.loc
       ) AS dept
FROM   dept d
ORDER BY d.deptno;

DEPT
--------------------------------------------------------------------------------
{"dept-number":10,"dept-name":"ACCOUNTING","location":"NEW YORK"}
{"dept-number":20,"dept-name":"RESEARCH","location":"DALLAS"}
{"dept-number":30,"dept-name":"SALES","location":"CHICAGO"}
{"dept-number":40,"dept-name":"OPERATIONS","location":"BOSTON"}
```

#### JSON_OBJECTAGG
指定したキーバリューペアを集約して、ひとつのJSONオブジェクトを作成

``` sql
 SELECT JSON_OBJECTAGG (
         KEY d.dname VALUE d.deptno
       ) AS dept
FROM   dept d
ORDER BY d.deptno;

DEPT
--------------------------------------------------------------------------------
{"ACCOUNTING":10,"RESEARCH":20,"SALES":30,"OPERATIONS":40}
```

#### json_array
引数を配列の要素として、JSON配列を作成

` select json_array(1,2,3,4,5,6,7,8) from dual;
[1,2,3,4,5,6,7,8] `

``` sql
SELECT JSON_ARRAY(
         ROWNUM,
         JSON_OBJECT(KEY 'dept_no' VALUE d.deptno),
         JSON_OBJECT(KEY 'dept_name' VALUE d.dname)
       ) AS dept_json_array
FROM   dept d;

DEPT_JSON_ARRAY
--------------------------------------------------------------------------------
[1,{"dept_no":10},{"dept_name":"ACCOUNTING"}]
[2,{"dept_no":20},{"dept_name":"RESEARCH"}]
[3,{"dept_no":30},{"dept_name":"SALES"}]
[4,{"dept_no":40},{"dept_name":"OPERATIONS"}]
```

#### json_arrayagg
指定した値をひとつの配列として集約

``` sql
SELECT JSON_ARRAYAGG(d.dname) dname_array FROM dept d;


DNAME_ARRAY
--------------------------------------------------------------------------------
["ACCOUNTING","RESEARCH","SALES","OPERATIONS"]
```

#### NULL値の取り扱い
すべての SQL/JSON関数はNULL値の取り扱いを制御できます。
デフォルトの「NULL ON NULL」は NULLを表示し
「ABSENT ON NULL」を指定すると KEY と VALUE の組み合わせが省略されます。

``` sql
「NULL on NULL」の指定（デフォルト）
SELECT JSON_OBJECT(
         KEY 'emp_name' VALUE e.ename,
         KEY 'commission' VALUE e.comm
         NULL ON NULL
       ) AS employees
FROM   emp e
WHERE  e.deptno = 10;

EMPLOYEES
----------------------------------------------------------------------
{"emp_name":"KING","commission":null}
{"emp_name":"CLARK","commission":null}
{"emp_name":"MILLER","commission":null}
```

``` sql
「ABSENT ON NULL」の指定
SELECT JSON_OBJECT(
         KEY 'emp_name' VALUE e.ename,
         KEY 'commission' VALUE e.comm
         ABSENT ON NULL
       ) AS employees
FROM   emp e
WHERE  e.deptno = 10;

EMPLOYEES
----------------------------------------------------------------------
{"emp_name":"KING"}
{"emp_name":"CLARK"}
{"emp_name":"MILLER"}
```



## SQLファンクションとして提供されるメリット
- 入れ子でファンクションを呼び出すことにより、複雑なJSONドキュメントの生成が可能
- PL/SQL組込みファンクションとして利用が可能
- 出力がJSONドキュメント形式であるためJSON書式の間違いを排除
- パフォーマンスの向上

#### 複雑なJSONドキュメントの生成

``` sql
SELECT JSON_OBJECT (
         KEY 'departments' VALUE
           JSON_ARRAY(
             (SELECT JSON_OBJECTAGG (
                       KEY 'department' VALUE
                         JSON_OBJECT(
                           KEY 'department_name' VALUE d.dname,
                           KEY 'department_no' VALUE d.deptno,
                           KEY 'employees' VALUE
                               (SELECT JSON_ARRAYAGG(
                                         JSON_OBJECT(
                                           KEY 'employee_number' VALUE e.empno,
                                           KEY 'employee_name' VALUE e.ename
                                         )
                                       )
                                FROM   emp e
                                WHERE  e.deptno = d.deptno
                               )
                         )
                   )
              FROM   dept d
             )
          )
      ) EMP_DEPT_JSON
FROM  dual;

EMP_DEPT_JSON
------------------------------------------------------------------------------------------------------------------------
{"departments":[{"department":{"department_name":"ACCOUNTING","department_no":10,"employees":[{"employee_number":7839,"e
mployee_name":"KING"},{"employee_number":7782,"employee_name":"CLARK"},{"employee_number":7934,"employee_name":"MILLER"}
]},"department":{"department_name":"RESEARCH","department_no":20,"employees":[{"employee_number":7566,"employee_name":"J
ONES"},{"employee_number":7788,"employee_name":"SCOTT"},{"employee_number":7902,"employee_name":"FORD"},{"employee_numbe
r":7369,"employee_name":"SMITH"},{"employee_number":7876,"employee_name":"ADAMS"}]},"department":{"department_name":"SAL
ES","department_no":30,"employees":[{"employee_number":7698,"employee_name":"BLAKE"},{"employee_number":7499,"employee_n
ame":"ALLEN"},{"employee_number":7521,"employee_name":"WARD"},{"employee_number":7654,"employee_name":"MARTIN"},{"employ
ee_number":7844,"employee_name":"TURNER"},{"employee_number":7900,"employee_name":"JAMES"}]},"department":{"department_n
ame":"OPERATIONS","department_no":40,"employees":null}}]}
```

見やすく整形すると以下のJSONドキュメント

``` JSON
{
   "departments":[
      {
         "department":{
            "department_name":"ACCOUNTING",
            "department_no":10,
            "employees":[
               {
                  "employee_number":7782,
                  "employee_name":"CLARK"
               },
               {
                  "employee_number":7839,
                  "employee_name":"KING"
               },
               {
                  "employee_number":7934,
                  "employee_name":"MILLER"
               }
            ]
         },
         "department":{
            "department_name":"RESEARCH",
            "department_no":20,
            "employees":[
               {
                  "employee_number":7369,
                  "employee_name":"SMITH"
               },
               {
                  "employee_number":7566,
                  "employee_name":"JONES"
               },
               {
                  "employee_number":7788,
                  "employee_name":"SCOTT"
               },
               {
                  "employee_number":7876,
                  "employee_name":"ADAMS"
               },
               {
                  "employee_number":7902,
                  "employee_name":"FORD"
               }
            ]
         },
         "department":{
            "department_name":"SALES",
            "department_no":30,
            "employees":[
               {
                  "employee_number":7499,
                  "employee_name":"ALLEN"
               },
               {
                  "employee_number":7521,
                  "employee_name":"WARD"
               },
               {
                  "employee_number":7654,
                  "employee_name":"MARTIN"
               },
               {
                  "employee_number":7698,
                  "employee_name":"BLAKE"
               },
               {
                  "employee_number":7844,
                  "employee_name":"TURNER"
               },
               {
                  "employee_number":7900,
                  "employee_name":"JAMES"
               }
            ]
         },
         "department":{
            "department_name":"OPERATIONS",
            "department_no":40,
            "employees":null
         }
      }
   ]
}
```

#### PL/SQL組込みファンクションとして利用
PL/SQL 内での代入にSELECTなしで利用可能

以下のような記載を

``` sql
declare
js varchar2(4000);
begin
   select json_query('{"doc":["a","b","c"]}','$.doc[0]' with wrapper) into js from dual;
   dbms_output.put_line(js);
end;
/
```

以下に変更可能

``` sql
declare 
js varchar2(4000);
begin
   js := json_query('{"doc":["a","b","c"]}','$.doc[0]' with wrapper);
   dbms_output.put_line(js);
end;
/
```

### 補足
###### 出力 JSON オブジェクトのデータ型
デフォルトは VARCHAR2 型です。 オプションで RETURNING句を追加するとVARCHAR2 ( BYTE / CHAR) や CLOB を指定することが 可能です。

``` sql
SELECT JSON_OBJECTAGG (
         KEY d.dname VALUE d.deptno
         RETURNING CLOB
       ) AS departments
FROM   dept d
ORDER BY d.deptno;
```

###### KEY に指定できるデータ型
SQL/JSON関数では KEY に 数値型は 指定できません。

``` sql
SELECT JSON_OBJECTAGG (
         KEY d.deptno VALUE d.dname
       ) AS dept
FROM   dept d;
         KEY d.deptno VALUE d.dname
             *
ERROR at line 2:
ORA-00932: inconsistent datatypes: expected CHAR got NUMBER
```

TO_CHAR関数を使って文字型に変更します

``` sql
SELECT JSON_OBJECTAGG (
         KEY TO_CHAR(d.deptno) VALUE d.dname
       ) AS dept
FROM   dept d;

DEPT
----------------------------------------------------------------------
{"10":"ACCOUNTING","20":"RESEARCH","30":"SALES","40":"OPERATIONS"}
```