Oracle 12cR2をJSON形式で操作

2017.11.02

こんにちは。YDCの やす です。

今回はOracle 12cR2についての記事です。前回はSQL Server 2016 の 新機能 「JSONサポート」について紹介しましたが、
Oracleだとどうなの??ということでOracle 12cR2の新機能「SQL/JSONファンクション」を色々と試してみました!
「JSONサポート」とはまた違う魅力があり、楽しく検証できたので紹介させて頂きます。


環境
OS Oracle Database
Windows Server 2012 Standard 12.2.0.1 EE

テーブルの準備

今回の検証に使うテーブルとサンプルデータの事前準備をします。
比較しやすいように「JSONサポート」の紹介と同じテーブルデータを使用しています。

SQL> create table animal (
2 no number(2),
3 name varchar2(30),
4 cry varchar2(10))
5 ;

表が作成されました。

SQL> insert into animal values(1,'dog','wan');

1行が作成されました。

SQL> insert into animal values(1,'cat','nya!');

1行が作成されました。

SQL> commit;

コミットが完了しました。

SQL> set line 400
SQL> select * from animal;

NO NAME CRY
---------- ---------------------------------------------------- -------------------
1 dog wan
1 cat nya!

SQL>

「SQL/JSONファンクション」機能を活用

1.JSON形式で問い合わせ結果を取得(JSON_OBJECT関数)

上記のテーブルの問い合わせ結果をJSON形式で取得してみましょう。
取得するにはキーバリューペアをJSON_OBJECT関数の引数として指定します。

SQL> select json_object('no' value no, 'name' value name, 'cry' value cry) from animal;

JSON_OBJECT('NO'VALUENO,'NAME'VALUENAME,'CRY'VALUECRY)
-------------------------------------------------------------------------------------------
{"no":1,"name":"dog","cry":"wan"} ★JSON形式で取得
{"no":1,"name":"cat","cry":"nya!"} ★JSON形式で取得

SQL>

テーブルの行ごとにJSON形式のデータとして取得することができました。問い合わせ結果はJSON形式に変換され、
データの値はJSONオブジェクトのvalue、カラム名は指定したkeyとして生成されます。SQL Server 2016 の新機能
「JSONサポート」では1行のJSON配列として結果が取得されるので比較した時に問い合わせ結果が大きく異なります。

「JSONサポート」のように1行のJSON配列として問い合わせ結果を取得することはできないのか...。
そんなことはありません。後述しますが他の関数を組み合わせることで実現可能です。
JSON_OBJECT関数以外にも色々な関数があるので先に紹介させて頂きます。

2.キーバリューペアを集約して結果を取得(JSON_OBJECTAGG関数)

指定したキーバリューペアを集約して、一つのJSONオブジェクトを作成するにはJSON_OBJECTAGG関数を使用します。

SQL> select json_objectagg(name value cry) from animal;

JSON_OBJECTAGG(NAMEVALUECRY)
-------------------------------------------------------------------
{"dog":"wan","cat":"nya!"} ★JSON形式で集約して取得

SQL>

キーバリューペアを集約して取得することができました。

3.JSON配列として結果を取得(JSON_ARRAY関数)

先ほど紹介した例はJSON形式の要素としてレコードを取得する例でしたが
JSON_ARRAY関数を使用することでJSON配列として取得することが可能です。
まずは簡単な例として引数の値を配列として作成してみます。

SQL> select json_array('1','2','3','4','5') from dual;

JSON_ARRAY('1','2','3','4','5')
------------------------------------------------------------
["1","2","3","4","5"] ★配列として作成

SQL>

作成することができました。JSON_ARRAY関数とJSON_OBJECT関数を組み合わせることで
JSON配列を作成することができます。animalテーブルをJSON配列で取得してみましょう。

SQL> select json_array(json_object('NO' value no, 'NAME' value name, 'CRY' value cry)) from animal;

JSON_ARRAY(JSON_OBJECT('NO'VALUENO,'NAME'VALUENAME,'CRY'VALUECRY))
--------------------------------------------------------------------------------
[{"NO":1,"NAME":"dog","CRY":"wan"}] ★JSON配列で取得
[{"NO":1,"NAME":"cat","CRY":"nya!"}] ★JSON配列で取得

JSON配列で取得できました。

4.配列として集約して結果を取得(JSON_ARRAYAGG関数)

JSON_ARRAYAGG関数を使用することで指定した列を一つの配列として集約することができます。

SQL> select json_arrayagg(name) from animal;

JSON_ARRAYAGG(NAME)
-----------------------------------------------
["dog","cat"] ★一つの配列として集約

SQL>

集約することができました。さて後述すると記載しましたが「JSONサポート」のように1行のJSON配列として取得することが
JSON_ARRAYAGG関数とJSON_OBJECT関数を組み合わせることで実現することができます。

SQL> select json_arrayagg(json_object('no' value no, 'name' value name, 'cry' value cry)) from animal;

JSON_ARRAYAGG(JSON_OBJECT('NO'VALUENO,'NAME'VALUENAME,'CRY'VALUECRY))
--------------------------------------------------------------------------------
[{"no":1,"name":"dog","cry":"wan"},{"no":1,"name":"cat","cry":"nya!"}] ★1行のJSON形式のデータで取得

SQL>

1行のJSON形式のデータを取得することができました。しかし問い合わせ結果にルート要素がついていません。
JSON_OBJECT関数をさらにもうひとつ組み合わすことでルート要素を付与することができます。

SQL> select json_object('animal' value json_arrayagg
2 (json_object
3 ('no' value no, 'name' value name, 'cry' value cry)
4 )
5 ) from animal;

JSON_OBJECT('ANIMAL'VALUEJSON_ARRAYAGG(JSON_OBJECT('NO'VALUENO,'NAME'VALUENAME,'CRY'VALUECRY)))
------------------------------------------------------------------------------------------------------
{"animal":[{"no":1,"name":"dog","cry":"wan"},{"no":1,"name":"cat","cry":"nya!"}]} ★root要素を付与

ルート要素を付与することができました。このように「SQL/JSONファンクション」を組み合わせることで
自由なJSON形式でデータを取得することができます。

JSON形式のデータを格納

新機能ではないですが、実はOracle Database 12.1.0.2からJSON形式のデータをそのままテーブルに
格納することができます。
使用条件はデータ型がCLOB、BLOB、VARCHAR2でありJSON形式のチェック制約を付与されていることです。
それではJSON形式のデータ格納用のテーブルを作って、さっき取得したJSON形式のデータを格納してみましょう。

SQL> create table animal_json(
2 doc clob constraint valid_json check(doc is json))
3 ;

表が作成されました。

SQL>
SQL> insert into animal_json values(
2 '{"animal":[{"no":1,"name":"dog","cry":"wan"},{"no":1,"name":"cat","cry":"nya!"}]}'
3 );

1行が作成されました。

SQL>

格納することができました。続いてJSON形式で格納されたデータの検索の方法です。
下記の文法で検索することが可能です。エイリアスを使用しないとエラーになる点は注意してください。

SELECT <エイリアス>.<要素>.<要素>... FROM <テーブル名> <エイリアス>;

それでは色々検索を試してみます。

SQL> select doc from animal_json;

DOC
--------------------------------------------------------------------------------
{"animal":[{"no":1,"name":"dog","cry":"wan"},{"no":1,"name":"cat","cry":"nya!"}]
★普通にSELECTすると文字列として表示


SQL> select doc.animal from animal_json;
select doc.animal from animal_json
*
行1でエラーが発生しました。:
ORA-00904: "DOC"."ANIMAL": 無効な識別子です。
★エイリアスなしでSELECTするとエラー



SQL> select json.doc.animal from animal_json json;

ANIMAL
--------------------------------------------------------------------------------
[{"no":1,"name":"dog","cry":"wan"},{"no":1,"name":"cat","cry":"nya!"}]
★animalから取得


SQL> select json.doc.animal.name from animal_json json;

ANIMAL
--------------------------------------------------------------------------------
["dog","cat"]
★animal.nameから全て取得


SQL> select json.doc.animal[0].name from animal_json json;

ANIMAL
--------------------------------------------------------------------------------
dog
★animal.nameの一つ目の値を取得


SQL> select json.doc.animal[1].name from animal_json json;

ANIMAL
--------------------------------------------------------------------------------
cat
★animal.nameの一つ目の値を取得

SQL>

今回はOracle 12cR2の新機能「SQL/JSONファンクション」を色々と試してみました。
個人的には関数を組み合わせて、自由に形式を整えることができる点に魅力を感じます。
Web APIで送受信する形式として、Webブラウザからも扱いやすいJSONが増えています。
テーブルデータを簡単にJSON形式に変換できるのでぜひ試してみてください!

Standby Expressに関するお問合わせ

  • TEL 042-333-6217
  • FAX 042-352-6101
  • LINE
  • Mail