LoginSignup
10
8

More than 5 years have passed since last update.

@kenwatanin日本オラクル株式会社

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

Posted at

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> select json_object('id' value 1, 'name' value 'myobject') from dual;

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

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オブジェクトを作成

 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]

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

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

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 の組み合わせが省略されます。

「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}

「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ドキュメントの生成

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ドキュメント

{
   "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なしで利用可能

以下のような記載を

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;
/

以下に変更可能

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 を指定することが 可能です。

SELECT JSON_OBJECTAGG (
         KEY d.dname VALUE d.deptno
         RETURNING CLOB
       ) AS departments
FROM   dept d
ORDER BY d.deptno;
KEY に指定できるデータ型

SQL/JSON関数では KEY に 数値型は 指定できません。

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関数を使って文字型に変更します

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"}
10
8
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
10
8