この記事で記載すること
LiveLabsの「Choose your Own JSON Adventure: Relational or Document Store Workshop」を参考にAutonomous Database でJSONデータを扱うときのメモを記載
主に以下の内容を記載
- JSONファイルのロード
- Database Actions（SQL Developer Web）のData Loading機能を利用してPC上のJSONファイルを表にロード
- Database Actions（Data Tools / Load) を用いてObject Storage上のJSONファイルを表にロード＋JSONを2次元化したViewの自動生成
- JSON in tableへのSQL操作
- Simple Dot-Notation
- SQL/JSON Path Expressions
- JSON Collectionへのアクセス
- Collectionの作成
- Collectionへのデータロード
- QBEを用いたデータアクセス
- JSONデータ更新
- Serach Indexを用いた検索
- Sort
前提/環境
Cloudリソース
以下のCloudリソースが利用できる状態となっていることを前提とします。
-
Autonomous Database: ATPが以下コンディションで作成されている
・Database Version: 19c
・Access Type: Allow secure access from everywhere -
Object Storage: JSONファイルの格納用バケットが準備されている
・Bucket Name: common
JSONファイル
以下からダウンロードしたファイルを用いてJSONデータを処理
[
{
"id": 1,
"AirportCode": "ATL",
"Name": "Atlanta, GA: Hartsfield-Jackson Atlanta International",
"Time": {
"Label": "2003/06",
"Month": 6,
"Month Name": "June",
"Year": 2003
},
"Statistics": {
"# of Delays": {
"Carrier": 1009,
"Late Aircraft": 1275,
"National Aviation System": 3217,
"Security": 17,
"Weather Codes":["SNW","RAIN","SUN","CLDY"],
"Weather": 328
},
"Carriers": {
"Aircraft Types": [{"make" : "Boeing", "models" : ["717","737","757","767","777","787"]},
{"make" : "Airbus", "models" : ["A320","A321","A330","A340","A350","A380"]}],
"Names": "American Airlines Inc.,JetBlue Airways,Continental Air Lines Inc.,Delta Air Lines Inc.,Atlantic Southeast Airlines,AirTran Airways Corporation,America West Airlines Inc.,Northwest Airlines Inc.,ExpressJet Airlines Inc.,United Air Lines Inc.,US Airways Inc.",
"Total": 11
},
"Flights": {
"Cancelled": 216,
"Delayed": 5843,
"Diverted": 27,
"On Time": 23974,
"Total": 30060
},
"Minutes Delayed": {
"Carrier": 61606,
"Late Aircraft": 68335,
"National Aviation System": 118831,
"Security": 518,
"Total": 268764,
"Weather Codes":["SNW","RAIN","SUN","CLDY"],
"Weather": 19474
}
}
},
{
"id": 2,
"AirportCode": "BOS",
"Name": "Boston, MA: Logan International",
"Time": {
"Label": "2003/06",
"Month": 6,
"Month Name": "June",
"Year": 2003
},
"Statistics": {
"# of Delays": {
"Carrier": 374,
"Late Aircraft": 495,
"National Aviation System": 685,
"Security": 3,
"Weather Codes":["SNW","RAIN","SUN","CLDY"],
"Weather": 66
},
"Carriers": {
"Aircraft Types": [{"make" : "Boeing", "models" : ["717","737","757","767","777","787"]},
{"make" : "Airbus", "models" : ["A320","A321","A330","A340","A350","A380"]}],
"Names": "American Airlines Inc.,Alaska Airlines Inc.,Continental Air Lines Inc.,Atlantic Coast Airlines,Delta Air Lines Inc.,Atlantic Southeast Airlines,AirTran Airways Corporation,America West Airlines Inc.,American Eagle Airlines Inc.,Northwest Airlines Inc.,ExpressJet Airlines Inc.,ATA Airlines d/b/a ATA,United Air Lines Inc.,US Airways Inc.",
"Total": 14
},
"Flights": {
"Cancelled": 138,
"Delayed": 1623,
"Diverted": 3,
"On Time": 7875,
"Total": 9639
},
"Minutes Delayed": {
"Carrier": 20319,
"Late Aircraft": 28189,
"National Aviation System": 24400,
"Security": 99,
"Total": 77167,
"Weather Codes":["SNW","RAIN","SUN","CLDY"],
"Weather": 4160
}
}
},
----
作業ユーザの作成
Database Actionsからユーザを作成
１．Database Actionsを起動してAdminユーザでログイン
２．Administration > Database Usersを選択 > Create Userをクリック
３．以下のとおりユーザを作成
ユーザ情報を登録
・User Name: GARY
・Web Access: 有効化
・Quota: Unlimited
権限を付与
・PDB_BDA
・SODA_APP
JSONファイルのロード
- Database Actions（SQL Developer Web）のData Loading機能を利用してPC上のJSONファイルを表にロード
１．Database ActionsのSQLを選択
２．Data Loading機能を用いてAirport Delays 2003-2016をロード
→ 「ID列をPKに設定する」を除きデフォルト設定でデータロードを実行
３．テーブルが作成され、データがロードされていることを確認
- （参考）Database Actions（Data Tools / Load) を用いてObject Storage上のJSONファイルを表にロード＋JSONを2次元化したViewの自動生成
０．Object Storage/BucketにJSONファイル（Airport Delays 2003-2016）をアップロード
１．Database ActionsのData Tools/Data Loadを選択
２．CLOUD STORAGEを選択してNextをクリック
３．ロードするファイルをアップするObject Storage/Bucketを登録
４．対象ファイルを選択して右にドラッグ＆ドロップ
５．Startボタンをクリック
６．SQL Developer WebからテーブルとViewができていることを確認
自動生成されたViewのDDL
CREATE OR REPLACE FORCE EDITIONABLE VIEW "ADMIN"."AIRPORTDELAYS" ("id", "Name", "Year", "Label", "Month", "Month Name", "Total", "Delayed", "On Time", "Diverted", "Cancelled", "Names", "Total_16", "Carrier", "Weather", "Security", "Late Aircraft", "National Aviation System", "Total_25", "Carrier_26", "Weather_27", "Security_28", "Late Aircraft_29", "National Aviation System_31", "AirportCode", "make", "scalar_string", "scalar_string_23", "scalar_string_30") DEFAULT COLLATION "USING_NLS_COMP" AS
SELECT JT."id",JT."Name",JT."Year",JT."Label",JT."Month",JT."Month Name",JT."Total",JT."Delayed",JT."On Time",JT."Diverted",JT."Cancelled",JT."Names",JT."Total_16",JT."Carrier",JT."Weather",JT."Security",JT."Late Aircraft",JT."National Aviation System",JT."Total_25",JT."Carrier_26",JT."Weather_27",JT."Security_28",JT."Late Aircraft_29",JT."National Aviation System_31",JT."AirportCode",JT."make",JT."scalar_string",JT."scalar_string_23",JT."scalar_string_30"
FROM "ADMIN"."AIRPORTDELAYS_COLLECTION" RT,
JSON_TABLE("JSON_DOCUMENT", '$[*]' COLUMNS
"id" number path '$.id',
"Name" varchar2(128) path '$.Name',
"Year" number path '$.Time.Year',
"Label" varchar2(8) path '$.Time.Label',
"Month" number path '$.Time.Month',
"Month Name" varchar2(16) path '$.Time."Month Name"',
"Total" number path '$.Statistics.Flights.Total',
"Delayed" number path '$.Statistics.Flights.Delayed',
"On Time" number path '$.Statistics.Flights."On Time"',
"Diverted" number path '$.Statistics.Flights.Diverted',
"Cancelled" number path '$.Statistics.Flights.Cancelled',
"Names" varchar2(512) path '$.Statistics.Carriers.Names',
"Total_16" number path '$.Statistics.Carriers.Total',
NESTED PATH '$.Statistics.Carriers."Aircraft Types"[*]' COLUMNS (
"make" varchar2(8) path '$.make',
NESTED PATH '$.models[*]' COLUMNS (
"scalar_string" varchar2(4) path '$[*]')),
"Carrier" number path '$.Statistics."# of Delays".Carrier',
"Weather" number path '$.Statistics."# of Delays".Weather',
"Security" number path '$.Statistics."# of Delays".Security',
"Late Aircraft" number path '$.Statistics."# of Delays"."Late Aircraft"',
NESTED PATH '$.Statistics."# of Delays"."Weather Codes"[*]' COLUMNS (
"scalar_string_23" varchar2(4) path '$[*]'),
"National Aviation System" number path '$.Statistics."# of Delays"."National Aviation System"',
"Total_25" number path '$.Statistics."Minutes Delayed".Total',
"Carrier_26" number path '$.Statistics."Minutes Delayed".Carrier',
"Weather_27" number path '$.Statistics."Minutes Delayed".Weather',
"Security_28" number path '$.Statistics."Minutes Delayed".Security',
"Late Aircraft_29" number path '$.Statistics."Minutes Delayed"."Late Aircraft"',
NESTED PATH '$.Statistics."Minutes Delayed"."Weather Codes"[*]' COLUMNS (
"scalar_string_30" varchar2(4) path '$[*]'),
"National Aviation System_31" number path '$.Statistics."Minutes Delayed"."National Aviation System"',
"AirportCode" varchar2(4) path '$.AirportCode')JT
;
JSONデータのSQLアクセス
- Simple Dot-Notation
Dot notation is designed for easy, general use and common use cases. Queries of JSON data that use dot-notation syntax return JSON values whenever possible.
特定のフィールドのValue値を条件として、JSON DocumentationのFieldを返すSQL
select a.statistics
from airportdelays a
where a.airportcode = 'SFO'
fetch first 1 row only;
{
"# of Delays": {
"Carrier": 416,
"Late Aircraft": 312,
"National Aviation System": 1080,
"Security": 14,
"Weather Codes": [
"SNW",
"RAIN",
"SUN",
"CLDY"
],
"Weather": 59
},
"Carriers": {
"Aircraft Types": [
{
"make": "Boeing",
"models": [
"717",
"737",
"757",
"767",
"777",
"787"
]
},
{
"make": "Airbus",
"models": [
"A320",
"A321",
"A330",
"A340",
"A350",
"A380"
]
}
],
"Names": "American Airlines Inc.,Alaska Airlines Inc.,Continental Air Lines Inc.,Delta Air Lines Inc.,America West Airlines Inc.,American Eagle Airlines Inc.,Northwest Airlines Inc.,SkyWest Airlines Inc.,ATA Airlines d/b/a ATA,United Air Lines Inc.,US Airways Inc.",
"Total": 11
},
"Flights": {
"Cancelled": 83,
"Delayed": 1880,
"Diverted": 9,
"On Time": 8211,
"Total": 10183
},
"Minutes Delayed": {
"Carrier": 22673,
"Late Aircraft": 17879,
"National Aviation System": 45407,
"Security": 1138,
"Total": 90487,
"Weather Codes": [
"SNW",
"RAIN",
"SUN",
"CLDY"
],
"Weather": 3390
}
}
Nestされたフィールドの値を条件として設定
select a.statistics
from airportdelays a
where a.airportcode = 'SFO'
and a.time."Month Name" = 'June'
and a.time.Year = '2010';
time fieldのValue
{"Label":"2010/06","Month":6,"Month Name":"June","Year":2010}
analytics function
select a.name,
sum(a.statistics."Minutes Delayed".Carrier) "Minutes Delayed" from airportdelays a
group by a.name
order by a.name;
NAME Minutes Delayed
______________________________________________________________________ __________________
Atlanta, GA: Hartsfield-Jackson Atlanta International 16425906
Baltimore, MD: Baltimore/Washington International Thurgood Marshall 3067381
Boston, MA: Logan International 4629447
Charlotte, NC: Charlotte Douglas International 4043589
Chicago, IL: Chicago Midway International 2251158
Chicago, IL: Chicago O'Hare International 12458548
Dallas/Fort Worth, TX: Dallas/Fort Worth International 11162806
....
- SQL/JSON Path Expressions
Oracle also has many built in JSON functions for working with document data which elevates the functionality found with Dot-Notation.
SQL/JSON condition json_exists returns true if at least one value matches, and false if no value matches. If a single value matches, then SQL/JSON function json_value returns that value if it is scalar and raises an error if it is non-scalar. If no value matches the path expression then json_value returns SQL NULL.
SQL/JSON function json_query returns all of the matching values, that is, it can return multiple values. You can think of this behavior as returning a sequence of values, as in XQuery, or you can think of it as returning multiple values. (No user-visible sequence is manifested.)
json_value
The SQL/JSON function JSON_VALUE finds a specified scalar JSON value in JSON data and returns it as a defined SQL value (date, number, timestamp, sdo_geometry, etc).
特定のfieldの特定のValueを指定してitemを取得
例：id field = 5を指定してtime JSON Block内のLabel itemを取得
SQL> select json_value (
2 time,
3 '$.Label'
4 ) date_label
5 from airportdelays a
6 where a.id = 5;
DATE_LABEL
_____________
2003/06
戻り値のデータ型を指定
SQL> select json_value (
2 Statistics,
3 '$.Flights.Cancelled' returning number
4 ) cancled_flights
5 from airportdelays a
6 where a.id = 5;
CANCLED_FLIGHTS
__________________
74
注意：json_valueはscalarのみreturn可能 → multiple valueの場合はエラーとなる（ORA-40470: JSON_VALUE evaluated to multiple values ）
SQL> select json_value(statistics, '$."Carriers"."Aircraft Types"[*].models' error on error)
2 from airportdelays
3 where id = 1032;
Error starting at line : 1 in command -
select json_value(statistics, '$."Carriers"."Aircraft Types"[*].models' error on error)
from airportdelays
where id = 1032
Error report -
ORA-40470: JSON_VALUE evaluated to multiple values
json_mergepatch
特定のValueを更新
更新前のデータ
SQL> select json_value (
2 Statistics,
3 '$.Carriers.Names'
4 ) "Airline Names"
5 from airportdelays a
6 where a.id = 10;
Airline Names
____________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________
American Airlines Inc.,JetBlue Airways,Continental Air Lines Inc.,Delta Air Lines Inc.,AirTran Airways Corporation,America West Airlines Inc.,Northwest Airlines Inc.,ATA Airlines d/b/a ATA,United Air Lines Inc.,US Airways Inc.,Southwest Airlines Co.
United Air Lines Inc.をOracle Air Lines Inc.に更新
update airportdelays
set statistics = json_mergepatch (
statistics,
'{"Carriers" : {"Names" : "'||
(select replace(json_value (
Statistics, '$.Carriers.Names')
,'United Air Lines Inc.,','Oracle Air Lines Inc.,')
from airportdelays a
where id = 10)||'"}
}'
)
where id = 10;
更新後のデータ
SQL> select json_value (
2 Statistics,
3 '$.Carriers.Names'
4 ) "Airline Names"
5 from airportdelays a
6* where a.id = 10;
Airline Names
____________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________
American Airlines Inc.,JetBlue Airways,Continental Air Lines Inc.,Delta Air Lines Inc.,AirTran Airways Corporation,America West Airlines Inc.,Northwest Airlines Inc.,ATA Airlines d/b/a ATA,Oracle Air Lines Inc.,US Airways Inc.,Southwest Airlines Co.
JSON Query
The SQL/JSON function json_query selects and returns one or more values from JSON data and returns those values. You can thus use json_query to retrieve fragments of a JSON document in JSON.
SQL> select JSON_QUERY(statistics, '$."# of Delays"')
2 from airportdelays
3 where id = 1032;
JSON_QUERY(STATISTICS,'$."#OFDELAYS"')
___________________________________________________________________________________________________________________________________________
{"Carrier":485,"Late Aircraft":686,"National Aviation System":545,"Security":2,"Weather Codes":["SNW","RAIN","SUN","CLDY"],"Weather":50}
json_valueでエラーとなったQueryをjson_queryで実行
SQL> select json_query(statistics, '$."Carriers"."Aircraft Types"[*].models' error on error)
2 from airportdelays
3 where id = 1032;
Error starting at line : 1 in command -
select json_query(statistics, '$."Carriers"."Aircraft Types"[*].models' error on error)
from airportdelays
where id = 1032
Error report -
ORA-40480: result cannot be returned without array wrapper
arrayデータのreturn値は、arrayでwrapする必要がある
SQL> select JSON_QUERY(statistics, '$."Carriers"."Aircraft Types"[*].models' with array wrapper error on error)
2 from airportdelays
3 where id = 1032;
JSON_QUERY(STATISTICS,'$."CARRIERS"."AIRCRAFTTYPES"[*].MODELS'WITHARRAYWRAPPERERRORONERROR)
______________________________________________________________________________________________
[["717","737","757","767","777","787"],["A320","A321","A330","A340","A350","A380"]]
JSON Table
The SQL/JSON function JSON_TABLE creates a relational view of JSON data. It maps the result of a JSON data evaluation into relational rows and columns.
time JSONをテーブルに変換
select a.name, v.*
from airportdelays a, json_table (
a.time, '$'
columns (
Label,
Year,
Month,
"Month Name"
) )v
where a.id = 100;
10行データを取得
SQL> select a.name, v.*
2 from airportdelays a, json_table (
3 a.time, '$'
4 columns (
5 Label,
6 Year,
7 Month,
8 "Month Name"
9 ) )v
10 fetch first 10 rows only;
NAME LABEL YEAR MONTH Month Name
______________________________________________________________________ __________ _______ ________ _____________
New York, NY: John F. Kennedy International 2003/06 2003 6 June
Las Vegas, NV: McCarran International 2003/06 2003 6 June
Los Angeles, CA: Los Angeles International 2003/06 2003 6 June
Atlanta, GA: Hartsfield-Jackson Atlanta International 2003/06 2003 6 June
Boston, MA: Logan International 2003/06 2003 6 June
Baltimore, MD: Baltimore/Washington International Thurgood Marshall 2003/06 2003 6 June
Charlotte, NC: Charlotte Douglas International 2003/06 2003 6 June
Washington, DC: Ronald Reagan Washington National 2003/06 2003 6 June
Denver, CO: Denver International 2003/06 2003 6 June
Dallas/Fort Worth, TX: Dallas/Fort Worth International 2003/06 2003 6 June
10 rows selected.
nestされたarrayからdataを取得
SQL> select t.*
2 from airportdelays,
3 json_table(Statistics, '$.Carriers."Aircraft Types"[*]'
4 columns(
5 make varchar2(400) path '$.make',
6 nested path '$.models[*]'
7 columns(
8 models varchar2(400) path '$'
9 )
10 )
11 ) as t
12 where id = 100;
MAKE MODELS
_________ _________
Boeing 717
Boeing 737
Boeing 757
Boeing 767
Boeing 777
Boeing 787
Airbus A320
Airbus A321
Airbus A330
Airbus A340
Airbus A350
Airbus A380
12 rows selected.
複数のjson_tableを用いてViewを作成
SQL> select a.airportcode, t.*, md.*
2 from airportdelays a,
3 json_table (
4 a.time, '$'
5 columns (
6 Label,
7 "Month Name",
8 Year
9 )
10 ) t,
11 json_table (
12 a.Statistics, '$."Minutes Delayed"'
13 columns (
14 Carrier,
15 "National Aviation System",
16 "Late Aircraft",
17 Security,
18 Weather,
19 "Weather Codes" varchar2(200) format json path '$."Weather Codes"',
20 nested path '$."Weather Codes"'
21 columns (
22 code1 varchar2(100) path '$[0]',
23 code2 varchar2(100) path '$[1]',
24 code3 varchar2(100) path '$[2]',
25 code4 varchar2(100) path '$[3]'
26 ),
27 Total
28 )
29 ) md
30 where a.id = 100;
AIRPORTCODE LABEL Month Name YEAR CARRIER National Aviation System Late Aircraft SECURITY WEATHER Weather Codes TOTAL CODE1 CODE2 CODE3 CODE4
______________ __________ _____________ _______ __________ ___________________________ ________________ ___________ __________ ______________________________ ________ ________ ________ ________ ________
JFK 2003/09 September 2003 13176 14391 7732 93 996 ["SNW","RAIN","SUN","CLDY"] 36388 SNW RAIN SUN CLDY
JSON Exists
The SQL/JSON condition json_exists lets you use a SQL/JSON path expression as a row filter, to select rows based on the content of JSON documents.
simpleな実行例
SQL> select a.id
2 from airportdelays a
3 where json_exists(a.time, '$?(@.Year == "2004" && @.Month == "6")')
4* fetch first 5 rows only;
ID
______
349
350
351
352
353
複数のjson_existsを用いた実行例
SQL> select a.id, a.airportcode, a.Statistics."Minutes Delayed".Total
2 from airportdelays a
3 where json_exists(a.time, '$?(@.Year == "2004" && @.Month == "6")')
4 and json_exists(a.Statistics, '$?(@."Minutes Delayed".Total > 500000)');
ID AIRPORTCODE Minutes Delayed
______ ______________ __________________
349 ATL 714316
355 DFW 520185
369 ORD 554653
SQL/JSON explain plan:
SQL> select a.id, a.airportcode, a.Statistics."Minutes Delayed".Total
2 from airportdelays a
3 where a.time.Year = 2004
4 and a.time.Month = 6
5 and a.Statistics."Minutes Delayed".Total > 500000;
ID AIRPORTCODE Minutes Delayed
______ ______________ __________________
349 ATL 714316
355 DFW 520185
369 ORD 554653
Dot-Notation explain plan
↑ 上記実行計画から確認できるとおり、SQL/JSON path expressionsのほうがはるかに効率がいいことがわかる
json_object
テーブルのデータをJSONでoutput
select json_object ( * ) jdoc
from airportdelays;
JSON transform(21c機能)
You can use the json_transform function to change input JSON data (or pieces of JSON data), by specifying one or more modifying operations that perform changes to the JSON data. Unlike json_mergepatch, json_transform can target the specific attributes you want to change.
Continuing our underhanded data alterations, lets work with the Minutes Delayed JSON section and see if we can make our numbers look a bit more favorable. Here is a sample of the JSON we will be working with:
"Minutes Delayed": {
"Carrier": 61606,
"Late Aircraft": 68335,
"National Aviation System": 118831,
"Security": 518,
"Total": 268764,
"Weather": 19474
}
update airportdelays
set statistics = json_transform (
statistics,
replace '$."Minutes Delayed".Total' = '0'
)
where id = 10;
JSON Collection
- Collectionの作成
Database Actions/Development/JSONを用いたCollectionの作成
１．Database ActionsからDevelopment > JSONを選択
２．JSON Workshieetの「Create Collection」をクリック
３．New Collection画面にCollection Nameを登録 > 「Create」をクリック
SODA for REST APIを用いたCollectionの作成
０．事前準備
・curlを実行できるターミナルを準備（OCI Cloud Shellも可能）
・ADBのRESTエンドポイントを確認: Database Actions > REST > Module > Create Module > Preview URL
１．curlを用いてCollectionを作成
$ curl -u "gary:PASSWORD" -i -X PUT https://(RESTエンドポイント)/ords/gary/soda/latest/airportdelayscollection
HTTP/1.1 201 Created
Date: Mon, 26 Apr 2021 15:53:46 GMT
Content-Length: 0
Connection: keep-alive
X-Frame-Options: SAMEORIGIN
Cache-Control: private,must-revalidate,max-age=0
Location: https://(RESTエンドポイント)/ords/gary/soda/latest/airportdelayscollection/
Collectionへのデータロード
０．ロード用JSONファイルをロード
curl -o airportDelays.json https://objectstorage.us-ashburn-1.oraclecloud.com/p/rQEg_sXqFmCO8Swe5vVesi3-hmiCfLsY0Yc8mr_k0zdX2NWhhm34zrwhs0uvg8K-/n/c4u03/b/developer-library/o/airportDelays.json
１．データロード
$ curl -u "gary:PASSWORD" -i -X POST -d @airportDelays.json -H "Content-Type: application/json" "https://(RESTエンドポイント)/ords/gary/soda/latest/airportdelayscollection?action=insert"
HTTP/1.1 100 Continue
HTTP/1.1 200 OK
Date: Mon, 26 Apr 2021 16:46:50 GMT
Content-Type: application/json
Content-Length: 736197
Connection: keep-alive
X-Frame-Options: SAMEORIGIN
Cache-Control: private,must-revalidate,max-age=0
{"items":[{"id":"6E7AD2EE1E6B452D954AB4F87C1B1953","etag":"3C144144B1374205A6635923E92E2546","lastModified":"2021-04-26T16:46:30.428087","created":"2021-04-26T16:46:30.428087"},{"id":"5C8FD6CE99774C6D940B82F94D960E11","etag":"53C68394591B49E88A21D64BDE5D102F","lastModified":"2021-04-26T16:46:30.428087","created":"2021-04-26T16:46:30.428087"}........
QBEを用いたデータアクセス
QBEを使ってフィルタしたデータを表示
Database Actions/JSON
１．Database Actions/Development/JSONを起動
２．該当するCollectionを選択
３．特定のFieldでフィルタ
４．comparison operator でフィルタ
SODA for REST API
IDを指定したQuery
$ curl -X GET -u "gary:WelCome123#123#" "https://(RESTエンドポイント)/ords/gary/soda/latest/airportdelayscollection/962B7D9D663F4731AD6B9A139DC38157"
{"type":"http://www.w3.org/Protocols/rfc2616/rfc2616-sec10.html#sec10.4.5","status":404,"title":"Key 962B7D9D663F4731AD6B9A139DC38157 not found in collection airportdelayscollection.","o:errorCode":"REST-02001"}
QBEを指定したQuery
$ curl -u "gary:WelCome123#123#" "https://(RESTエンドポイント)/ords/gary/soda/latest/airportdelayscollection?action=query" -H "Content-Type: application/json" --data-binary \
'{"AirportCode": "DCA","Statistics.Flights.Cancelled": {"$gt": 400},"Time.Year": 2011}'
{"items":[{"id":"445F2E22649A475DA22A115CF1652837","etag":"D59DF9AF2F454E58BEFA9B8A2403CA37","lastModified":"2021-05-21T01:37:09.803870000Z","created":"2021-05-21T01:37:09.803870000Z","links":[{"rel":"self","href":"https://(RESTエンドポイント):443/ords/gary/soda/latest/airportdelayscollection/445F2E22649A475DA22A115CF1652837"}],"value":{"id":2644,"AirportCode":"DCA","Name":"Washington, DC: Ronald Reagan Washington National","Time":{"Label":"2011/01","Month":1,"Month Name":"January","Year":2011},"Statistics":{"# of Delays":{"Carrier":331,"Late Aircraft":314,"National Aviation System":348,"Security":4,"Weather Codes":["SNW","RAIN","SUN","CLDY"],"Weather":38},"Carriers":{"Aircraft Types":[{"make":"Boeing","models":["717","737","757","767","777","787"]},{"make":"Airbus","models":["A320","A321","A330","A340","A350","A380"]}],"Names":"American Airlines Inc.,Alaska Airlines Inc.,JetBlue Airways,Continental Air Lines Inc.,Delta Air Lines Inc.,Atlantic Southeast Airlines,Frontier Airlines Inc.,AirTran Airways Corporation,American Eagle Airlines Inc.,United Air Lines Inc.,US Airways Inc.,ExpressJet Airlines Inc.","Total":12},"Flights":{"Cancelled":550,"Delayed":1038,"Diverted":22,"On Time":5173,"Total":6783},"Minutes Delayed":{"Carrier":16829,"Late Aircraft":18648,"National Aviation System":12615,"Security":165,"Total":50961,"Weather Codes":["SNW","RAIN","SUN","CLDY"],"Weather":2704}}}}],"hasMore":false,"count":1}
QBE Operator $not
{"Statistics.Flights.Cancelled": {"$gt": 400},
"AirportCode":{"$not" : {"$eq" : "DCA"}}
}
QBE Operator $and / $or
{ "$and" : [
{"Statistics.Flights.Cancelled": {"$gt": 400}},
{"Time.Year": 2010 },
{"AirportCode": "ORD"}
]}
{"AirportCode": "DCA",
"Time.Year": 2012,
"$or" : [{"Time.Month": {"$eq": 6}}, {"Time.Month": {"$eq": 7}}]
}
JSONデータ更新
Database Actions/JSON - 直接編集
１．Database Actions/Development/JSONを起動
QBEで変更対象のDocumentを検索するためのフィルターを設定のうえ、Run Queryボタンを実行
編集ボタンをクリック
任意のアイテムを編集のうえ、右下にあるSaveボタンをクリック
（例） NameをPhonexからTokyoに変更
SODA for REST
SODAのpatchを用いてデータを更新
curl -u "gary:WelCome123#123#" "https://g5730e937dc2295-atp0611.adb.ap-tokyo-1.oraclecloudapps.com/ords/gary/soda/latest/airportdelayscollection?action=query" -H "Content-Type: application/json" --data-binary \
'{
"$query": {"id": 1},
"$patch": [
{
"op": "test",
"path": "/Statistics/# of Delays/Carrier",
"value": 1009
},
{
"op": "replace",
"path": "/Statistics/# of Delays/Carrier",
"value": 1019
}
]
}'
Search Indexを用いた検索
Search Indexの設定
以下のとおりSeach Indexを設定
$contains operatorを用いたQBE検索
{"Name": {"$contains": "Tokyo"}}
Sort
"$orderby": {
"$fields": [
{
"path": "AirportCode",
"datatype": "varchar2",
"order": "asc"
}
],
"$scalarRequired": false,
"$lax": false
}
補足
JSONファイルのロード
JSONファイルの表へのロードは、以下の方法で実施
- DBMS_CLOUD.COPY_COLLECTIONを用いたロード
- Database Actionsを用いたロード
- REST
JSONファイルロードに対応するJSONフォーマット
- line-delimited JSON
- array of JSON documents
"line-delimited_JSON"
{ "name" : "apple", "count": 20 }
{ "name" : "orange", "count": 42 }
{ "name" : "pear", "count": 10 }
"array_of_JSON_documents"
[{"name" : "apple", "count": 20 },
{"name" : "orange", "count": 42 },
{"name" : "pear", "count": 10 }]
（補足）
以下のようにpretty-printed outputなフォーマットとなっているJSONファイルはそのままでは取り込めない
{
"id": "0001",
"type": "donut",
"name": "Cake",
"ppu": 0.55,
"batters":
{
"batter":
[
{ "id": "1001", "type": "Regular" },
{ "id": "1002", "type": "Chocolate" },
{ "id": "1003", "type": "Blueberry" },
{ "id": "1004", "type": "Devil's Food" }
]
},
"topping":
[
{ "id": "5001", "type": "None" },
{ "id": "5002", "type": "Glazed" },
{ "id": "5005", "type": "Sugar" },
{ "id": "5007", "type": "Powdered Sugar" },
{ "id": "5006", "type": "Chocolate with Sprinkles" },
{ "id": "5003", "type": "Chocolate" },
{ "id": "5004", "type": "Maple" }
]
}
上のようなファイルをロードする場合は、以下のようにフォーマットの変換が必要
cat test.json | jq -c
{"id":"0001","type":"donut","name":"Cake","ppu":0.55,"batters":{"batter":[{"id":"1001","type":"Regular"},{"id":"1002","type":"Chocolate"},{"id":"1003","type":"Blueberry"},{"id":"1004","type":"Devil's Food"}]},"topping":[{"id":"5001","type":"None"},{"id":"5002","type":"Glazed"},{"id":"5005","type":"Sugar"},{"id":"5007","type":"Powdered Sugar"},{"id":"5006","type":"Chocolate with Sprinkles"},{"id":"5003","type":"Chocolate"},{"id":"5004","type":"Maple"}]}
QBE Comparison Operators
$all — whether an array field value contains all of a set of values
$between — whether a field value is between two string or number values (inclusive)
$eq — whether a field value is equal to a given scalar
$exists — whether a given field exists
$gt — whether a field value is greater than a given scalar value
$gte — whether a field value is greater than or equal to a given scalar
$hasSubstring — whether a string field value has a given substring (same as $instr)
$in — whether a field value is a member of a given set of scalar values
$instr — whether a string field value has a given substring (same as $hasSubstring)
$like — whether a field value matches a given SQL LIKE pattern
$lt — whether a field value is less than a given scalar value
$lte — whether a field value is less than or equal to a given scalar value
$ne — whether a field valueis different from a given scalar value
$nin — whether a field value is not a member of a given set of scalar values
$regex — whether a string field value matches a given regular expression
$startsWith — whether a string field value starts with a given substring
参考
Documentation
- Load JSON Documents with Autonomous Database
- Simple Oracle Document Access (SODA)
- 19c JSON Developer's Guide
- Query JSON Data
- Performance Tuning for JSON
- Partitioning JSON Data
- Indexes for JSON Data
- JSON Query Rewrite To Use a Materialized View Over JSON_TABLE
- In-Memory JSON Data
- Oracle Database JSON Restrictions
- 21c JSON Developer's Guide