JSONデータの操作
リレーショナル構造の一部としてのJSONデータの使用、より具体的には(リレーショナル)表でのJSON列の使用について説明します。
Oracle Databaseは、トランザクション、索引付け、宣言的問合せおよびビューを含むリレーショナル・データベースの機能を使用してJSONをネイティブにサポートしています。リレーショナル・データとは異なり、JSONデータは、スキーマがなくても、データベースへの格納、索引付けおよび問合せが可能です。脚注1
Oracleでは、データベース内に格納されたJSONデータへのアクセス用に、Simple Oracle Document Access (SODA) APIファミリも提供しています。SODAは、リレーショナル・データベース機能やSQLおよびPL/SQLなどの言語に関する知識を使用しないスキーマレス・アプリケーション開発向けに設計されています。これにより、ドキュメントがデータベースにどのように格納されているかを理解していなくても、ドキュメントのコレクションを作成してOracle Databaseに格納し、それらを取得したり、問い合せることができます。
JSONデータは、アプリケーション層とデータベースの間で情報を交換するために広く使用されています。Oracle REST Data Services (ORDS)は、データベースへのRESTコールを行うための最も便利なツールです。例7-15に、この概念を示します。
JSONの操作は、JavaScriptのコア機能の1つです。受信JSONドキュメントは、JSON.parse()を使用して解析する必要はなく、すぐに使用できます。マイクロサービス・アーキテクチャでは、データベース内のJavaScriptによって提供される拡張オプションが非常に有益です。
関連項目:
- SODAおよびデータベース内のJavaScriptの詳細は、「MLE JavaScriptコードでのSODAコレクションの操作」を参照してください
- Oracle DatabaseでのJSONの使用の詳細は、『Oracle Database JSON開発者ガイド』を参照してください
例7-15 データベース表へのJSONデータの挿入
この例は、REST APIがORDSで公開されており、ユーザーがJSONデータをデータベースにPOSTできることを前提としています。これにより、管理者は、departments表にさらに部門をアップロードできます。JSONデータを受け取ると、MLEモジュールはJSON_TABLE()を使用して、JSONデータ構造をリレーショナル・モデルに変換します。
CREATE TABLE departments(
department_id NUMBER NOT NULL PRIMARY KEY,
department_name VARCHAR2(50) NOT NULL,
manager_id NUMBER,
location_id NUMBER
);
CREATE OR REPLACE FUNCTION REST_API_DEMO(
"depts" JSON
) RETURN BOOLEAN
AS MLE LANGUAGE JAVASCRIPT
{{
/**
*insert a number of department records, provided as JSON,
*into the departments table
*@params {object} depts - an array of departments
*/
if(depts.constructor !== Array){
throw new Error('must provide an array of departments to this function');
}
//convert JSON input to relational data and insert into a table
const result = session.execute(`
INSERT INTO departments(
department_id,
department_name,
manager_id,
location_id
)
SELECT
jt.*
FROM json_table(:depts, '$[*]' columns
department_id path '$.department_id',
department_name path '$.department_name',
manager_id path '$.manager_id',
location_id path '$.location_id'
) jt`,
{
depts:{
val: depts,
type: oracledb.DB_TYPE_JSON
}
}
);
if(result.rowsAffected !== depts.length){
return false;
} else {
return true;
}
}};
/次の匿名PL/SQLブロックを使用してRESTコールをシミュレートすると、追加の部門を表に挿入できます:
DECLARE
l_success boolean := false;
l_depts JSON;
BEGIN
l_depts := JSON('[
{
"department_id": 1010,
"department_name": "New Department 1010",
"manager_id": 200,
"location_id": 1700
},
{
"department_id": 1020,
"department_name": "New Department 1020",
"manager_id": 201,
"location_id": 1800
},
{
"department_id": 1030,
"department_name": "New Department 1030",
"manager_id": 114,
"location_id": 1700
},
{
"department_id": 1040,
"department_name": "New Department 1040",
"manager_id": 203,
"location_id": 2400
}]'
);
l_success := REST_API_DEMO(l_depts);
IF NOT l_success THEN
RAISE_APPLICATION_ERROR(
-20001,
'an unexpected error occurred ' || sqlerrm
);
END IF;
END;
/次の問合せで示されているように、データが正常に挿入されました:
SELECT *
FROM departments
WHERE department_id > 1000;結果:
DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID LOCATION_ID
------------- ------------------------------ ---------- -----------
1010 New Department 1010 200 1700
1020 New Department 1020 201 1800
1030 New Department 1030 114 1700
1040 New Department 1040 203 2400
例7-16 JavaScriptを使用したJSONデータの操作
JSON_TABLEやJSON_TRANSFORMなどのSQLファンクションを使用するのではなく、JavaScriptでもJSONデータ操作を実行できます。
この例は、『Oracle Database JSON開発者ガイド』で定義されているJ_PURCHASEORDER表に基づいています。この表には、複数の顧客の購買オーダーを含むJSONドキュメントが格納されます。各購買オーダーは、1つ以上の明細品目で構成されます。
次のファンクションaddFreeItem()を使用すると、しきい値を超える商品を注文する顧客に無料アイテムを追加できます。
CREATE OR REPLACE MLE MODULE purchase_order_mod
LANGUAGE JAVASCRIPT AS
/**
*a simple function accepting a purchase order and checking whether
*its value is high enough to merit the addition of a free item
*
*@param {object} po the purchase order to be checked
*@param {object} freeItem which free item to add to the order free of charge
*@param {number} threshold the minimum order value before a free item can be added
*@param {boolean} itemAdded a flag indicating whether the free item was successfully added
*@returns {object} the potentially updated purchaseOrder
*@throws exception in case
* -any of the mandatory parameters is null
* -in the absence of line items
* -if the free item has already been added to the order
*/
export function addFreeItem(po, freeItem, threshold, itemAdded){
//ensure values for parameters have been provided
if(po == null || freeItem == null || threshold == null){
throw new Error(`mandatory parameter either not provided or null`);
}
//make sure there are line items provided by the purchase order
if(po.LineItems === undefined) {
throw new Error(
`PO number ${po.PONumber} does not contain any line items`
);
}
//bail out if the free item has already been added to the purchase order
if(po.LineItems.find(({Part}) => Part.Description === freeItem.Part.Description)){
throw new Error(`${freeItem.Part.Description} has already been added to order ${po.PONumber}`);
}
//In, Out, and InOut Parameters are implemented in JavaScript using
//special interfaces
itemAdded.value = false;
//get the total order value
const poValue = po.LineItems
.map(x => x.Part.UnitPrice * c.Quantity)
.reduce(
(accumulator, currentValue) => accumulator + currentValue, 0
);
//add a free item to the purchase order if its value exceeds
//the threshold
if(poValue > threshold){
//update the ItemNumber
freeItem.ItemNumber = (po.LineItems.length + 1)
po.LineItems.push(freeItem);
itemAdded.value = true;
}
return po;
}
/すべてのMLEモジュールと同様に、SQLおよびPL/SQLでコール仕様を使用する前にコール仕様を作成する必要があります。次の例では、add_free_item()へのコールをパッケージにラップします。このファンクションは、OUTパラメータを含む多数のパラメータを受け入れ、PL/SQL型をMLE型にマップする拡張シグネチャ句を必要とします。SQLデータ型JSONは、MLE ANY型にマップされます。JavaScriptにはOUTパラメータという概念がないため、最後のパラメータp_item_addedはOutインタフェースを使用して渡す必要があります。JavaScriptでのバインド・パラメータの使用の詳細は、「OUTおよびIN OUTパラメータ」を参照してください。
CREATE OR REPLACE PACKAGE purchase_order_pkg AS
FUNCTION add_free_item(
p_po IN JSON,
p_free_item IN JSON,
p_threshold IN NUMBER,
p_item_added OUT BOOLEAN
)
RETURN JSON AS
MLE MODULE purchase_order_mod
SIGNATURE 'addFreeItem(any, any, number, Out<boolean>)';
--additional code
END purchase_order_pkg;
/脚注凡例
脚注1: JSONスキーマを、データベース・スキーマの概念と混同しないでください。Oracle Databaseのデータベース・スキーマは、データベース・ユーザーがネーミングの競合のリスクなしに、表、索引、ビューなどのオブジェクトを作成するための個別のネームスペースです。