json_table
- Personal Chao yu
- Sep 2, 2021
- 9 min read
Updated: Jun 20, 2022
Full example how to convert json to a table
Select jt.total_entries
,jt.dialog_id
,jt.user_id
,jt.last_message_sender
,jt.last_message_senderid
,jt.last_message
,to_date('19700101'
,'YYYYMMDD') + (1 / 24 / 60 / 60) * jt.last_message_date_sent
From json_table('
{
"total_entries": 1,
"skip": 0,
"limit": 100,
"items": [
{
"_id": "627426fdecbc270011a6cd21",
"user_id": 5768417,
"created_at": "2022-05-05T19:35:25Z",
"updated_at": "2022-05-05T19:35:32Z",
"name": "Leon Luiken",
"type": 3,
"photo": null,
"occupants_ids": [
5768417,
5772962
],
"occupants_count": 2,
"is_e2ee": false,
"is_muted": false,
"last_message": "Thanks for accepting my request!",
"last_message_date_sent": 1651779332,
"last_message_id": "627427048bc0af33d8000001",
"last_message_user_id": 5772962,
"last_message_status": null,
"unread_messages_count": 1,
"pinned_messages_ids": [],
"description": null,
"xmpp_room_jid": null,
"admins_ids": []
}
]
}'
,'$' columns(total_entries Number path '$."total_entries"'
,Nested path '$.items[*]'
columns(dialog_id Varchar2 path '$."_id"'
,user_id Number path '$."user_id"'
,last_message_sender Varchar2 path '$."name"'
,last_message_senderid Number path '$."last_message_user_id"'
,last_message Varchar2 path '$."last_message"'
,last_message_date_sent Number path '$."last_message_date_sent"'))) jt;
Select t.po_document
From pwc_purchaseorder t;
Select po.po_document."ShippingInstructions"."Phone"
From pwc_purchaseorder po;
Select po.po_document."ShippingInstructions"."Phone"."number"
From pwc_purchaseorder po;
Select po.po_document."ShippingInstructions"."Phone"."type"
From pwc_purchaseorder po;
Select *
From all_json_columns t
Where t.table_name Like '%PWC%';
-- json pretty -----
Select json_serialize(po_document Returning Clob pretty)
From pwc_purchaseorder;
Select json_transform(po_document
,remove '$."Special Instructions"' Returning Clob pretty)
From pwc_purchaseorder;
--- update json column cell ----
-- create on missing key value pairs
Update pwc_purchaseorder
Set po_document = json_transform(po_document
,Set '$.chaoyu' = 'ss');
-- create new json object within json
Update pwc_purchaseorder t
Set t.po_document = json_transform(t.po_document
,Set '$.JonSnow' = '{"Name":"John Snow","Age":28,"From Show":"GOT"}' format json);
-- replace an existing filed value
Update pwc_purchaseorder t
Set t.po_document = json_transform(po_document
,Set '$.Addresses' = '{"street":"8 Timbly Rd.","city":"Penobsky","state":"UT"}'
format json ignore On missing); -- is not found ignore
-- set boolean to value
Update pwc_purchaseorder t
Set t.po_document = json_transform(po_document
,Set '$.AllowPartialShipment' = 'true' format json);
-- update array
Update pwc_purchaseorder t
Set t.po_document = json_transform(po_document
,Set '$.ShippingInstructions.Phone[0]' = '909-555-1212');
-- update with where clause ---
Update pwc_purchaseorder t
Set t.po_document = json_transform(po_document
,Set '$.Requestor' = 'Sarah Bell')
Where json_exists(t.po_document
,'$?(@.PONumber == 1700)');
-- prepending to array
/*This prepends element "909-555-1212" to array Phone. Insertion at position 0 shifts all
existing elements to the right: element N becomes element N+1.*/
Update pwc_purchaseorder t
Set t.po_document = json_transform(po_document
,Insert '$.ShippingInstructions.Phone[0]' = '909-555-1212');
-- appending to array
Update pwc_purchaseorder t
Set t.po_document = json_transform(po_document
,append '$.ShippingInstructions.Phone' = '909-555-1212');
Update pwc_purchaseorder t
Set t.po_document = json_transform(po_document
,Insert '$.ShippingInstructions.Phone[last+1]' = '909-555-1212')
Select json_mergepatch(po_document
,'{"Special Instructions":null}' Returning Clob pretty)
From pwc_purchaseorder;
-- remove element by set it to null
Update pwc_purchaseorder
Set po_document = json_mergepatch(po_document
,'{"Special Instructions":null}');
Update pwc_purchaseorder
Set po_document = json_mergepatch(po_document
,'{"Category" : "Platinum",
"ShippingInstructions" : null,
"Special Instructions" : "Contact User SBELL",
"LineItems" : [],
"AllowPartialShipment" : null,
"Allow Partial Shipment" : false }');
Update pwc_purchaseorder
Set po_document = json_mergepatch(po_document
,'{ "PONumber" : 1600,
"Reference" : "ABULL-20140421",
"Requestor" : "Alexis Bull",
"User" : "ABULL",
"CostCenter" : "A50",
"Special Instructions" : "Contact User SBELL",
"Allow Partial Shipment" : false,
"LineItems" : [],
"Category" : "Platinum" }
');
-- Query Json data
-- dot notation
Select t.po_document."LineItems" [ 1 ]
,t.po_document."LineItems" [ * ]
,t.po_document."ShippingInstructions".name
From pwc_purchaseorder t;
Select json_value(po_document
,'$.PONumber') -- return scalar
From pwc_purchaseorder;
Select po.po_document."PONumber"
From pwc_purchaseorder po;
Select po.po_document."ShippingInstructions"."Phone"
From pwc_purchaseorder po;
Select json_query(po_document
,'$.ShippingInstructions.Phone')
From pwc_purchaseorder;
Select po.po_document."ShippingInstructions"."Phone".type As t
From pwc_purchaseorder po;
Select json_query(po_document
,'$.ShippingInstructions.Phone.type' With Array wrapper) As t -- array with wrapper
From pwc_purchaseorder;
-- query filtering
Select json_query(t.po_document
,'$.LineItems[*]?(@.Quantity == 5)' With Array wrapper)
From pwc_purchaseorder t;
Select json_query(t.po_document
,'$.LineItems[*]?(@.Quantity > 5)' With Array wrapper)
From pwc_purchaseorder t;
Select json_query(t.po_document
,'$.ShippingInstructions.Phone[*]?(@.type != "Office").number' With Array wrapper)
From pwc_purchaseorder t;
Select json_query(t.po_document
,'$.ShippingInstructions?(@.name == "Alexis Bull").Phone' With Array wrapper)
From pwc_purchaseorder t;
Select json_query(t.po_document
,'$.ShippingInstructions?(@.name == "Alexis Bull").Phone.type' With Array wrapper)
From pwc_purchaseorder t;
Select json_query('["alpha", 42, "10.4"]'
,'$[*].stringOnly()' With conditional Array wrapper)
From dual;
Select json_value('[19, "Oracle", {"a":1},[1,2,3]]'
,'$.type()')
From dual;
Select json_value('[19, "Oracle", {"a":1},[1,2,3]]'
,'$.type()')
From dual;
Select json_value('[19, "Oracle", {"a":1},[1,2,3]]'
,'$.size()')
From dual;
Select json_value(json_query(t.po_document
,'$.ShippingInstructions?(@.name == "Alexis Bull").Phone.type' With Array wrapper)
,'$.size()') As array_size
From pwc_purchaseorder t;
Select to_char(json_value('{"a" : "2019-01-02T12:34:56"}'
,'$.a' Returning Timestamp)
,'DD-MON-YYYY HH24:MI:SS TZR')
From dual;
Select Cast(json_value('{"a" : "2019-01-02T12:34:56"}'
,'$.a' Returning Timestamp) As Date)
From dual;
---- where clause ---
Select po.po_document
From pwc_purchaseorder po
Where json_exists(po.po_document
,'$?(@.PONumber == 1700)');
/*
WHERE json_exists(po.po_document,
'$?(@.LineItems.Part.UPCCode == 85391628927)');
WHERE json_exists(po.po_document,
'$.LineItems?(@.Part.UPCCode == 85391628927)');
WHERE json_exists(po.po_document,
'$.LineItems.Part?(@.UPCCode == 85391628927)');
*/
** ** ** remeber To Disable Variable substitution ** ** ** ** ** ** ** **
Select po.po_document
From pwc_purchaseorder po
Where json_exists(po.po_document
,'$?(@.LineItems.Part.UPCCode == 85391628927
&& @.LineItems.Quantity > 3)');
Select po.po_document
From pwc_purchaseorder po
Where json_exists(po.po_document
,'$.LineItems[*]?(@.Part.UPCCode == 85391628927
&& @.Quantity > 3)');
Select po.po_document
From pwc_purchaseorder po
Where json_exists(po.po_document
,'$?(@.User == "ABULL"
&& exists(@.LineItems?(@.Part.UPCCode == 85391628927
&& @.Quantity > 3)))');
--- JSON_VALUE ----
Declare
b Boolean;
jsondata Clob;
Begin
Select po_document
Into jsondata
From pwc_purchaseorder
Where rownum = 1;
b := json_value(jsondata
,'$.AllowPartialShipment' Returning Boolean error On error);
End;
/
Select json_value(po_document
,'$.AllowPartialShipment') --- JSON_VALUE: Returning a JSON Boolean Value to SQL as VARCHAR2
From pwc_purchaseorder;
-- create type to match and return into ---
Create Or Replace Type pwc_shipping_t As Object(Name Varchar2(30)
,address pwc_addr_t);
Create Or Replace Type pwc_addr_t As Object(street Varchar2(100)
,city Varchar2(30));
Select json_value(po_document
,'$.ShippingInstructions' Returning pwc_shipping_t) As t
From pwc_purchaseorder;
-- create array type ----
Create Or Replace Type pwc_part_t As Object(description Varchar2(30)
,unitprice Number);
Create Or Replace Type pwc_item_t As Object(itemnumber Number
,part);
Create Or Replace Type pwc_items_t As Varray(10) Of pwc_item_t;
Select json_value(po_document
,'$.LineItems' Returning pwc_items_t)
From pwc_purchaseorder;
--- json_value expressed using json_table----
Select json_value(column
,json_path Returning data_type error_hander On error)
From Table;
Select jt.column_alias
From Table
,json_table(column
,'$' error_handler On error columns("COLUMN_ALIAS" data_type path json_path)) As "JT";
--- json_query ---
Select json_query(po_document
,'$.ShippingInstructions.Phone[*].type' With wrapper)
From pwc_purchaseorder;
--- JSON_QUERY expressed using json_table----
Select json_query(column
,json_path Returning data_type array_wrapper error_hander On error)
From Table;
Select jt.column_alias
From Table
,json_table(column
,'$' error_handler On error
columns("COLUMN_ALIAS" data_type format json array_wrapper path json_path)) As "JT";
Select jt.*
From pwc_purchaseorder po
,json_table(po.po_document columns(
"AllowPartialShipment"
,"PONumber"
,Nested
"LineItems" [*]
columns(
itemnumber varchar2 path "ItemNumber"
,descriptions path "Part"."Description"
,unitprice path "Part"."UnitPrice"
,qty number path "Quantity"))) As "JT";
--- filter in json_table ---
Select jt.*
From pwc_purchaseorder po
,json_table (po.po_document
,'$'COLUMNS (
"ponum" number Path '$.PONumber'
,requester varchar2(30) path '$.Requestor'
,"Special Instructions" VARCHAR2(4000) PATH '$."Special Instructions"'
,NESTED PATH '$.LineItems[*]?(@.ItemNumber > 1)' COLUMNS (
ItemNumber NUMBER PATH '$.ItemNumber',
Description VARCHAR(4000) PATH '$.Part.Description'))) AS "JT";
Select id
,"Requestor"
,"type"
,"number"
From pwc_purchaseorder
Left Outer Join json_table(
po_document columns(
"Requestor"
, Nested "ShippingInstructions"."Phone" [ * ] columns(
"type"
,"number"
)
)
)
On 1 = 1;
Select id
,"Requestor"
,"type"
,"number"
From pwc_purchaseorder
Nested po_document columns(
"Requestor"
, Nested "ShippingInstructions"."Phone" [ * ] columns(
"type"
,"number")
);
-- Accessing JSON Data Multiple Times to Extract Data -- 44444444 times
Select json_value(po_document
,'$.Requestor' Returning Varchar2(32))
,json_query(po_document
,'$.ShippingInstructions.Phone' Returning Varchar2(100))
From pwc_purchaseorder
Where json_exists(po_document
,'$.ShippingInstructions.Address.zipCode')
And json_value(po_document
,'$.AllowPartialShipment' Returning Varchar2(5 Char)) = 'true';
-- Using JSON_TABLE to Extract Data Without Multiple Parses --
Select jt.requestor
--,jt.phones
,jt.*
From pwc_purchaseorder
,json_table(po_document
,'$' columns(requestor Varchar2(32 Char) path '$.Requestor'
,phones Varchar2(100 Char) format json path '$.ShippingInstructions.Phone' -- return json in column ---
,partial Varchar2(5 Char) path '$.AllowPartialShipment'
,has_zip Varchar2(5 Char) Exists path '$.ShippingInstructions.Address.zipCode')) jt
Where jt.partial = 'false'
And jt.has_zip = 'true';
-- Projecting an Entire JSON Array as JSON Data --
Select jt.*
From pwc_purchaseorder
,json_table(po_document
,'$' columns(requestor Varchar2(32 Char) path '$.Requestor'
,ph_arr Varchar2(100 Char) format json path '$.ShippingInstructions.Phone')) As "JT";
-- Projecting Elements of a JSON Array --
Select jt.*
From pwc_purchaseorder
,json_table(po_document
,'$.ShippingInstructions.Phone[*]' columns(
phone_type Varchar2(10) path '$.type'
,phone_num Varchar2(20) path '$.number'
)) As "JT";
Select jt.*
From pwc_purchaseorder
,json_table(po_document
,'$?(@.Requestor like "Sara%").ShippingInstructions.Phone[*]?(@.type == "Office")' columns(
phone_type Varchar2(10) path '$.type'
,phone_num Varchar2(20) path '$.number'
)) As "JT";
-- Projecting Elements of a JSON Array Plus Other Data --
Select jt.*
From pwc_purchaseorder
,json_table(po_document
,'$' columns(requestor Varchar2(32 Char) path '$.Requestor'
,phone_type Varchar2(50 Char) format json With wrapper path '$.ShippingInstructions.Phone[*].type'
,phone_num Varchar2(50 Char) format json With wrapper path '$.ShippingInstructions.Phone[*].number')) As "JT";
Select jt.*
From pwc_purchaseorder
,json_table(po_document
,'$?(@.Requestor == "Sarah Bell")' columns(requestor Varchar2(32 Char) path '$.Requestor'
,phone_type Varchar2(50 Char) format json With wrapper path '$.ShippingInstructions.Phone[*].type'
,phone_num Varchar2(50 Char) format json With wrapper path '$.ShippingInstructions.Phone[*].number')) As "JT";
-- Projecting Array Elements Using NESTED --
Select jt.*
From pwc_purchaseorder po
,json_table(po.po_document columns("Requestor"
,Nested "ShippingInstructions"."Phone" [ * ] columns( "type" ,"number"))) As "JT";
Select jt."Type"
,jt."Number"
,jt.requestor
From pwc_purchaseorder po
,json_table(po.po_document
,'$' columns(requestor Varchar2(4000) path '$.Requestor'
,Nested path '$.ShippingInstructions.Phone[*]' columns(
"Type" Varchar2(4000) path '$.type'
,"Number" Varchar2(4000) path '$.number'
)
)
) As "JT";
-- Creating a View Over JSON Data --
CREATE or replace force VIEW pwc_purchaseorder_detail_viewb AS
Select jt.*
From pwc_purchaseorder po
,json_table(po.po_document
,'$' columns(po_number Number(10) path '$.PONumber'
,reference Varchar2(30 Char) path '$.Reference'
,requestor Varchar2(128 Char) path '$.Requestor'
,userid Varchar2(10 Char) path '$.User'
,costcenter Varchar2(16) path '$.CostCenter'
,ship_to_name Varchar2(20 Char) path '$.ShippingInstructions.name'
,ship_to_street Varchar2(32 Char) path '$.ShippingInstructions.Address.street'
,ship_to_city Varchar2(32 Char) path '$.ShippingInstructions.Address.city'
,ship_to_county Varchar2(32 Char) path '$.ShippingInstructions.Address.county'
,ship_to_postcode Varchar2(10 Char) path '$.ShippingInstructions.Address.postcode'
,ship_to_state Varchar2(2 Char) path '$.ShippingInstructions.Address.state'
,ship_to_zip Varchar2(8 Char) path '$.ShippingInstructions.Address.zipCode'
,ship_to_country Varchar2(32 Char) path '$.ShippingInstructions.Address.country'
,ship_to_phone Varchar2(24 Char) path '$.ShippingInstructions.Phone[0].number'
,Nested path '$.LineItems[*]'
columns(itemno Number(38) path '$.ItemNumber'
,description Varchar2(256 Char) path '$.Part.Description'
,upc_code Number path '$.Part.UPCCode'
,quantity Number(12
,4) path '$.Quantity'
,unitprice Number(14
,2) path '$.Part.UnitPrice'))) jt;
-- Creating a Materialized View Over JSON Data --
CREATE or replace MATERIALIZED VIEW pwc_purchaseorder_materialized_view BUILD IMMEDIATE REFRESH FAST ON STATEMENT WITH PRIMARY KEY AS
Select jt.*
From pwc_purchaseorder po
,json_table(po.po_document
,'$' columns(po_number Number(10) path '$.PONumber'
,reference Varchar2(30 Char) path '$.Reference'
,requestor Varchar2(128 Char) path '$.Requestor'
,userid Varchar2(10 Char) path '$.User'
,costcenter Varchar2(16) path '$.CostCenter'
,ship_to_name Varchar2(20 Char) path '$.ShippingInstructions.name'
,ship_to_street Varchar2(32 Char) path '$.ShippingInstructions.Address.street'
,ship_to_city Varchar2(32 Char) path '$.ShippingInstructions.Address.city'
,ship_to_county Varchar2(32 Char) path '$.ShippingInstructions.Address.county'
,ship_to_postcode Varchar2(10 Char) path '$.ShippingInstructions.Address.postcode'
,ship_to_state Varchar2(2 Char) path '$.ShippingInstructions.Address.state'
,ship_to_zip Varchar2(8 Char) path '$.ShippingInstructions.Address.zipCode'
,ship_to_country Varchar2(32 Char) path '$.ShippingInstructions.Address.country'
,ship_to_phone Varchar2(24 Char) path '$.ShippingInstructions.Phone[0].number'
,Nested path '$.LineItems[*]'
columns(itemno Number(38) path '$.ItemNumber'
,description Varchar2(256 Char) path '$.Part.Description'
,upc_code Number path '$.Part.UPCCode'
,quantity Number(12
,4) path '$.Quantity'
,unitprice Number(14
,2) path '$.Part.UnitPrice'))) jt;
-- Oracle SQL Function JSON_SERIALIZE --
SELECT json_serialize(po_document PRETTY) FROM pwc_purchaseorder;
-- Specifying Preferred Column Names For Some JSON Fields --
begin
dbms_json.rename_column(tableName => ,
jcolName => ,
path => ,
type => ,
preferred_name => )
end;
/
-- Creating a View Using a Hierarchical Data Guide Obtained With GET_INDEX_DATAGUIDE --
begin
dbms_json.create_view(viewName => 'PWC_VIEW1',
tableName => 'pwc_purchaseorder',
jcolName => 'PONumber',
dataguide => DBMS_JSON.get_index_dataguide(owner => 'DEMO',
tableName => 'pwc_purchaseorder',
jcolName => 'PONumber',
format => DBMS_JSON.FORMAT_HIERARCHICAL )
);
end;
/
begin
DBMS_JSON.create_view_on_path('VIEW2',
'J_PURCHASEORDER',
'PO_DOCUMENT',
'$');
end;
begin
DBMS_JSON.create_view_on_path('VIEW4',
'J_PURCHASEORDER',
'PO_DOCUMENT',
'$.LineItems.Part');
end;
/
-- Declaring an Input Value To Be JSON --
Select json_object('name' Value ename || ' ' || job
,'hasCommission' Value Case
When comm Is Null Then
'false'
Else
'true'
End format json)
From emp
Where ename Like 'A%';
Select json_object('id' Value employee_id
,'name' Value first_name || ' ' || last_name
,'contactInfo' Value json_object('mail' Value email
,'phone' Value phone_number)
,'hireDate' Value hire_date
,'pay' Value salary
)
From oehr_employees
Where salary > 15000;
Select json_object(last_name
,'contactInfo' value json_object(email
,phone_number)
,hire_date
,salary)
From oehr_employees
Where employee_id = 101;
-- Using a Wildcard (*) with JSON_OBJECT ---
Select json_object(*)
From oehr_employees
Where salary > 15000;
-- Using JSON_OBJECT With ABSENT ON NULL --
SELECT JSON_OBJECT('city' VALUE city,
'province' value state_province ABSENT ON NULL)
FROM oehr_locations
WHERE city LIKE 'S%';
SELECT JSON_OBJECT('city' VALUE city,
'province' value state_province )
FROM oehr_locations
WHERE city LIKE 'S%';
-- Using a User-Defined Object-Type Instance with JSON_OBJECT --
CREATE TABLE pwc_po_ship AS
Select json_value(po_document
,'$.ShippingInstructions' Returning pwc_shipping_t) shipping
From pwc_purchaseorder;
SELECT json_object(shipping) from pwc_po_ship;
-- Using JSON_ARRAY to Construct a JSON Array --
Select json_object('title' Value o.job_title
,'salaryRange' Value json_array(o.min_salary,o.max_salary,o.job_id,o.job_title,json_object(o.job_title)))
From oehr_jobs o;
-- Using JSON_OBJECTAGG to Construct a JSON Object --
Select json_objectagg(department_name Value department_id)
From oehr_departments;
-- Using JSON_ARRAYAGG to Construct a JSON Array --
Select mgr.employee_id
,mgr.first_name
,mgr.last_name
,mgr.job_id
,mgr.salary
,mgr.department_id
,Count(1)
From oehr_employees mgr
,oehr_employees rpt
Where mgr.employee_id = rpt.manager_id
Group By mgr.employee_id
,mgr.first_name
,mgr.last_name
,mgr.job_id
,mgr.salary
,mgr.department_id;
Select json_object('id' Value mgr.employee_id
,'manager' Value(mgr.first_name || ' ' || mgr.last_name)
,'numReports' Value Count(rpt.employee_id)
,'reports' Value json_arrayagg(rpt.employee_id Order By rpt.employee_id))
From oehr_employees mgr
,oehr_employees rpt
Where mgr.employee_id = rpt.manager_id
Group By mgr.employee_id
,mgr.last_name
,mgr.first_name
Having Count(rpt.employee_id) > 6;
-- Constructing and Serializing an In-Memory JSON Object --
Declare
je json_element_t;
jo json_object_t;
Begin
je := json_element_t.parse('{"name":"Radio controlled plane"}');
If (je.is_object)
Then
jo := treat(je As json_object_t);
jo.put('price'
,149.99);
End If;
dbms_output.put_line(je.to_string);
End;
/
-- Using Method GET_KEYS() to Obtain a List of Object Fields --
Declare
jo json_object_t;
ja json_array_t;
keys json_key_list;
keys_string Varchar2(100);
Begin
ja := New json_array_t;
jo := json_object_t.parse('{"name":"Beda","jobTitle":"codmonki","projects":["json", "xml"]}');
keys := jo.get_keys;
For i In 1 .. keys.count
Loop
ja.append(keys(i));
End Loop;
keys_string := ja.to_string;
dbms_output.put_line(keys_string);
End;
/
-- Using Method PUT() to Update Parts of JSON Documents --
Create Or Replace Function pwc_add_totals(purchaseorder In Varchar2) Return Varchar2 Is
po_obj json_object_t;
li_arr json_array_t;
li_item json_element_t;
li_obj json_object_t;
unitprice Number;
quantity Number;
totalprice Number := 0;
totalquantity Number := 0;
Begin
po_obj := json_object_t.parse(purchaseorder);
li_arr := po_obj.get_array('LineItems');
For i In 0 .. li_arr.get_size - 1
Loop
li_obj := json_object_t(li_arr.get(i));
quantity := li_obj.get_number('Quantity');
unitprice := li_obj.get_object('Part').get_number('UnitPrice');
totalprice := totalprice + (quantity * unitprice);
totalquantity := totalquantity + quantity;
End Loop;
po_obj.put('totalQuantity'
,totalquantity);
po_obj.put('totalPrice'
,totalprice);
Return po_obj.to_string;
End;
/
UPDATE pwc_purchaseorder SET (po_document) = pwc_add_totals(po_document);
Select json_serialize(po_document PRETTY)
From pwc_purchaseorder po
Where po.po_document."PONumber" = 1600;
--- A Table With GeoJSON Data --
CREATE TABLE pwc_geo
(id VARCHAR2 (32) NOT NULL,
geo_doc VARCHAR2 (4000) CHECK (geo_doc is json));
Insert Into pwc_geo
Values
(1
,'{"type" : "FeatureCollection",
"features" : [{"type" : "Feature",
"geometry" : {"type" : "Point",
"coordinates" : [-122.236111, 37.482778]},
"properties" : {"Name" : "Redwood City"}},
{"type" : "Feature",
"geometry" : {"type" : "LineString",
"coordinates" : [[102.0, 0.0],
[103.0, 1.0],
[104.0, 0.0],
[105.0, 1.0]]},
"properties" : {"prop0" : "value0",
"prop1" : 0.0}},
{"type" : "Feature",
"geometry" : {"type" : "Polygon",
"coordinates" : [[[100.0, 0.0],
[101.0, 0.0],
[101.0, 1.0],
[100.0, 1.0],
[100.0, 0.0]]]},
"properties" : {"prop0" : "value0",
"prop1" : {"this" : "that"}}}]}');
Select json_value(geo_doc
,'$.features[0].geometry' Returning sdo_geometry error On error),
json_serialize(geo_doc PRETTY)
From pwc_geo;
Comments