25.3 SQL/JSONファンクションJSON_OBJECT
SQL/JSONファンクションjson_objectは、引数のSQL式を評価した結果からJSONオブジェクトを構成します。
任意の数の引数を指定でき、各引数は次のいずれかです。
-
明示的なフィールド名/値ペア。例:
answer : 42。名前/値ペア引数は、生成されたJSONオブジェクトのオブジェクト・メンバーを指定します(値式がSQL
NULLに評価される場合、およびABSENT ON NULL句が適用される場合を除く)。名前と値はSQL式です。名前式は、SQL文字列に評価される必要があります。値式は、JSONデータ型のSQL値、またはJSON値として表すことができるSQL値に評価される必要があります。名前式と値式は、キーワードVALUEまたはコロン(:)で区切られます。ノート:
一部のクライアント・ドライバは、問合せをデータベースに送信する前に、問合せテキストのスキャンとバインド変数の特定を試みる場合があります。その場合、バインド変数を導入するときに、
json_object内の名前/値セパレータであるコロンが誤解釈されることがあります。この問題を回避するために、キーワードVALUEをセパレータとして使用('Name' VALUE Diderot)するか、単にペアの値部分をカッコで囲みます('Name':(Diderot))。 -
リレーショナル列名。その前に表名または別名、あるいはビュー名の後にドット(
.)が続く場合があります。例:t1.address。この場合、指定されたデータ行について、列名引数で指定されたJSONオブジェクト・メンバーにフィールド名としての列名とフィールド値としての列値が含まれます。
引用符で囲まれているかどうかに関係なく、指定する列名は大/小文字を区別して解釈されます。たとえば、
Emailを列名引数として使用する場合、EMAIL列のデータは、フィールド名Email(EMAILではない)を持つオブジェクト・メンバーの生成に使用されます。 -
表名または別名、あるいはビュー名の後にドットとアスタリスクのワイルドカード(
*)。例:t1.*。(名前や別名には、myschema.t1.*のように、データベース・スキーマ名の接頭辞を付けることができます。)この場合、表またはビューの列はすべて入力として使用されます。それぞれが明示的に指定されたかのように処理されます。特に、列名は大/小文字を区別して解釈されます。
あるいは、json_objectでは、次のいずれかである単一の引数を受け入れます:
-
ユーザー定義のSQLオブジェクト型のインスタンス。例:
json_object(my_mailing_address_type)。この場合、結果のJSONオブジェクト・フィールド名はSQLオブジェクト属性名から付けられ、その値は(JSON生成が再帰的に適用される)SQLオブジェクト属性値から取得されます。
オブジェクト型インスタンス引数の後に、キーワード
WITH TYPENAMEを使用できます。これにより、結果オブジェクトには、値がユーザー定義型を指定する文字列であるフィールドtypeを持つメンバーも含まれます。例25-7に、これを示します。 -
アスタリスク・ワイルドカード(
*)。例:json_object(*)。ワイルドカードは、表またはビューのすべての列を明示的に指定してオブジェクト・メンバーを生成するためのショートカットとして機能します。結果のJSONオブジェクト・フィールド名は、大文字の列名です。ワイルドカードは、
FROMリストからわかる表、ビューまたは表の別名とともに使用できます。列は任意のSQLデータ型にできます。この場合(
json_object(*))と前述の場合(アスタリスクの前に、明示的な表名またはビュー名(または表の別名)に続いてドットがある:json_object(t.*))の違いに注意してください。json_object(*)の場合、列名は大/小文字を区別して解釈されません。
アスタリスク・ワイルドカードをjson_objectとともに使用する方法を説明するもう1つの方法は、SQL SELECT一覧でワイルドカードに許可されているものに従っているということです。
SQL/JSON条件is jsonと同様に、キーワードSTRICTおよびWITH UNIQUE KEYSはファンクションjson_objectおよびjson_objectaggとともに使用できます。それぞれの動作はis jsonと同じです。
例25-2 JSON_OBJECTでの名前/値ペアの使用
この例では、表hr.employees (標準のデータベース・スキーマHRから)の従業員のうち、給与が15000を超える従業員それぞれのJSONオブジェクトが構成されます。
明示的な名前/値のペアを渡してJSONオブジェクトのメンバーを指定します。このオブジェクトには、フィールドcontactInfoの値として、フィールドmailとphoneのオブジェクトが含められています。
ここではRETURNING JSONを使用することで、デフォルトの戻り型VARCHAR2(4000)ではなく、JSONデータがJSONデータ型として返されるように指定しています。
SELECT json_object('id' : employee_id,
'name' : first_name || ' ' || last_name,
'contactInfo' : json_object('mail' : email,
'phone' : phone_number),
'hireDate' : hire_date,
'pay' : salary
RETURNING JSON)
FROM hr.employees
WHERE salary > 15000;問合せは、次のような行を返します(ここではわかりやすくするためフォーマット出力しています):
{"id" : 101,
"name" : "Neena Kochhar",
"contactInfo" : {"mail" : "NKOCHHAR",
"phone" : "515.123.4568"},
"hireDate" : "21-SEP-05",
"pay" : 17000}ノート:
ファンクションjson_objectは常にJSONデータを返すため、入力フィールドcontactInfoの値に対してFORMAT JSONを指定する必要はありません。ただし、そのフィールドの値が'{"mail":' || email ', "phone":' || phone_number || '}'のように指定されていた場合、その後にFORMAT JSONを続けてその文字列値をJSONデータとして解釈する必要があります。
"contactInfo" :
'{"mail":' || email ', "phone":' || phone_number || '}'
FORMAT JSON,JSONデータの戻り型はJSONであるため、これは同じ問合せの代替の構文です。
SELECT JSON { 'id' : employee_id,
'name' : first_name || ' ' || last_name,
'contactInfo' : JSON { 'mail' : email,
'phone' : phone_number }
'hireDate' : hire_date,
'pay' : salary }
FROM hr.employees
WHERE salary > 15000;例25-3 JSON_OBJECTでの列名の使用
この例では、employee_idが101である従業員のJSONオブジェクトを構成します。生成されるフィールドには列の名前が付けられ、大/小文字が区別されます。
ここではRETURNING JSONを使用することで、デフォルトの戻り型VARCHAR2(4000)ではなく、JSONデータがJSONデータ型として返されるように指定しています。
SELECT json_object(last_name,
'contactInfo' : json_object(email, phone_number),
hire_date,
salary,
RETURNING JSON)
FROM hr.employees
WHERE employee_id = 101;
問合せは、次のような行を返します(ここではわかりやすくするためフォーマット出力しています):
{"last_name" : "Kochhar",
"contactInfo" : {"email" : "NKOCHHAR",
"phone_number" : "515.123.4568"},
"hire-date" : "21-SEP-05",
"salary" : 17000}JSONデータの戻り型はJSONであるため、これは同じ問合せの代替の構文です。
SELECT JSON { last_name,
'contactInfo' : JSON { email, phone_number },
hire_date,
salary}
FROM hr.employees
WHERE employee_id = 101;例25-4 JSON_OBJECTでのワイルドカード(*)の使用
この例では、給与が15000を超える従業員ごとにJSONオブジェクトを構成します。従業員表の各列は、そのフィールド名が(大文字)列名である1つのオブジェクト・メンバーの構成に使用されます。SQL NULL値によってJSONフィールド値がnullになることに注意してください。
ここではRETURNING JSONを使用することで、デフォルトの戻り型VARCHAR2(4000)ではなく、JSONデータがJSONデータ型として返されるように指定しています。
SELECT json_object(* RETURNING JSON)
FROM hr.employees
WHERE salary > 15000;
問合せは、次のような行を返します(ここではわかりやすくするためフォーマット出力しています):
JSON_OBJECT(*)
--------------
{"EMPLOYEE_ID":100,
"FIRST_NAME":"Steven",
"LAST_NAME":"King",
"EMAIL":"SKING",
"PHONE_NUMBER":"515.123.4567",
"HIRE_DATE":"2003-06-17T00:00:00",
"JOB_ID":"AD_PRES",
"SALARY":24000,
"COMMISSION_PCT":null,
"MANAGER_ID":null,
"DEPARTMENT_ID":90}
{"EMPLOYEE_ID":101,
"FIRST_NAME":"Neena",
"LAST_NAME":"Kochhar",
"EMAIL":"NKOCHHAR",
"PHONE_NUMBER":"515.123.4568",
"HIRE_DATE":"2005-09-21T00:00:00",
"JOB_ID":"AD_VP",
"SALARY":17000,
"COMMISSION_PCT":null,
"MANAGER_ID":100,
"DEPARTMENT_ID":90}
{"EMPLOYEE_ID":102,
"FIRST_NAME":"Lex",
"LAST_NAME":"De Haan",
"EMAIL":"LDEHAAN",
"PHONE_NUMBER":"515.123.4569",
"HIRE_DATE":"2001-01-13T00:00:00",
"JOB_ID":"AD_VP",
"SALARY":17000,
"COMMISSION_PCT":null,
"MANAGER_ID":100,
"DEPARTMENT_ID":90}JSONデータの戻り型はJSONであるため、これは同じ問合せの代替の構文です。
SELECT JSON { * }
FROM hr.employees
WHERE salary > 15000;例25-5 ABSENT ON NULLを指定したJSON_OBJECTの使用
この例では、標準のデータベース・スキーマHRの表hr.locationsに対する問合せを実行して、フィールドcityとprovinceが含まれるJSONオブジェクトを作成します。
json_objectに対するNULL処理のデフォルト動作は、NULL ON NULLです。
JSON値nullが含まれるフィールドが作成されないようにするために、この例ではABSENT ON NULLを使用します。列cityの値が'Singapore'のときに列state_provinceがSQL値NULLになるということは、その場所に対してprovinceフィールドが作成されていないことを意味します。
SELECT JSON_OBJECT('city' : city,
'province' : state_province ABSENT ON NULL)
FROM hr.locations
WHERE city LIKE 'S%';問合せの出力は次のとおりです:
JSON_OBJECT('CITY'ISCITY,'PROVINCE'ISSTATE_PROVINCEABSENTONNULL)
----------------------------------------------------------------
{"city":"Southlake","province":"Texas"}
{"city":"South San Francisco","province":"California"}
{"city":"South Brunswick","province":"New Jersey"}
{"city":"Seattle","province":"Washington"}
{"city":"Sydney","province":"New South Wales"}
{"city":"Singapore"}
{"city":"Stretford","province":"Manchester"}
{"city":"Sao Paulo","province":"Sao Paulo"}
この例にはRETURNING句がないため、JSONデータはデフォルトでVARCHAR2(4000)として返されます。RETURNING JSONを使用した場合は、問合せにこの代替の構文を使用できます。
SELECT JSON {'city' : city,
'province' : state_province ABSENT ON NULL}
FROM hr.locations
WHERE city LIKE 'S%';例25-6 JSON_OBJECTでのユーザー定義のオブジェクト型インスタンスの使用
この例では、オブジェクト型がshipping_tの列shippingを使用して表po_shipを作成します。(SQL/JSONファンクションjson_valueを使用してJSONデータからshipping_tインスタンスを構築します。例20-5を参照)。
次に、json_objectを使用して、po_ship.shipping列にあるSQLオブジェクト型インスタンスからJSONオブジェクトを生成し、JSONデータ型のインスタンスとして返します。
CREATE TABLE po_ship
AS SELECT json_value(data, '$.ShippingInstructions'
RETURNING shipping_t)
shipping
FROM j_purchaseorder;
DESCRIBE po_ship;
Name Null? Type
--------- ------- ----------
SHIPPING SHIPPING_TSELECT json_object(shipping RETURNING JSON)
FROM po_ship;これは問合せ出力です(ここではわかりやすくするためフォーマット出力しています)。
JSON_OBJECT(SHIPPING)
---------------------
{"NAME":"Alexis Bull",
"ADDRESS":{"STREET":"200 Sporting Green",
"CITY":"South San Francisco"}}
{"NAME":"Sarah Bell",
"ADDRESS":{"STREET":"200 Sporting Green",
"CITY":"South San Francisco"}}json_objectの戻り型はJSONであるため、これは同じ問合せの代替の構文です。
SELECT JSON {shipping} FROM po_ship;例25-7 JSON_OBJECTでのWITH TYPENAMEの使用
この例では、ユーザー定義オブジェクト引数の後にキーワードWITH TYPENAMEを使用する効果を示します。フィールドtypeが含まれ、値は、JSONオブジェクトの生成元であるユーザー定義オブジェクト型を指定する文字列です。
この例では、オブジェクト型my_mailing_address_typeを定義し、その型の列を含む表を作成し、そのようなオブジェクトを含む行を表に挿入します。この例では、オブジェクト型がデータベース・ユーザー(スキーマ)user_1によって作成されていることを前提としています。
次に、2つの問合せでファンクションjson_objectを使用して、表列のユーザー定義オブジェクトからJSONオブジェクトを生成します。2番目の問合せは最初の問合せと同じですが、キーワードWITH TYPENAMEを使用しているため、結果オブジェクトには文字列値フィールドtypeを持つメンバーが含まれます。文字列値は"USER_1.MY_MAILING_ADDRESS_TYPE"です(型がスキーマuser_1によって定義および所有されていることを示しています)。ここではわかりやすくするため、問合せの出力をフォーマット出力しています。
CREATE OR REPLACE TYPE my_mailing_address_type
AS OBJECT(Street VARCHAR2(80),
City VARCHAR2(80),
State CHAR(2),
Zip VARCHAR2(10));
CREATE TABLE t1 (col1 my_mailing_address_type);
INSERT INTO t1 VALUES (my_mailing_address_type('street1', 'city1', 'CA',
'12345'));
SELECT json_object(col1) FROM t1;JSON_OBJECT(COL1)
-----------------
{"STREET" : "street1",
"CITY" : "city1",
"STATE" : "CA",
"ZIP" : "12345"}SELECT json_object(col1 WITH TYPENAME) FROM t1;JSON_OBJECT(COL1WITHTYPENAME)
-----------------------------
{"type" : "USER_1.MY_MAILING_ADDRESS_TYPE",
"STREET" : "street1",
"CITY" : "city1",
"STATE" : "CA",
"ZIP" : "12345"}関連項目:
-
select_list構文の詳細は、Oracle Database SQL言語リファレンスを参照してください -
SQL/JSONファンクション
json_objectおよび同等のJSONコンストラクタ{...}構文の詳細は、『Oracle Database SQL言語リファレンス』のOracle Database SQL言語リファレンスに関する項を参照してください -
SQL識別子の構文については、『Oracle Database SQL言語リファレンス』のOracle Database SQL言語リファレンスに関する項を参照してください
関連トピック
親トピック: SQLを使用したJSONデータの生成