Create JSON from Cursor
Declare
c_cur Sys_Refcursor;
l_json_clob Clob;
Begin
Open c_cur For
Select 1 As cl01
,Cursor (Select 2 As cl02_1
,Cursor (
Select 1 As cl02_2
From dual
Union All
Select 2
From dual
Union All
Select 3
From dual) As cl02_2
From dual) As cl02
From dual;
apex_json.initialize_clob_output(p_indent => 2);
apex_json.open_object;
apex_json.write('rws', c_cur);
apex_json.close_object;
l_json_clob := apex_json.get_clob_output();
dbms_output.put_line(l_json_clob);
apex_json.free_output;
End;
/
-- use "returning clob" behind each () to avoid max 4000 character error
SELECT json_arrayagg(json_object('region' :region_name, 'countries' :(
SELECT json_arrayagg(json_object('id' :country_id,
'name' :country_name))
FROM oehr_countries c
WHERE c.region_id = r.region_id), region_name :(
SELECT json_arrayagg(country_name)
FROM oehr_countries c
WHERE c.region_id = r.region_id), 'multi_d_array' :(
select json_arrayagg(json_arrayagg(c.country_name
order by
c.country_name))
from oehr_countries c
inner join oehr_regions r
on (r.region_id = c.region_id)
group by r.region_name)) returning JSON) -- remove "returning JSON" gives string
FROM oehr_regions r;
-- to create multi dimensional array
select json_arrayagg(json_arrayagg(c.country_name order by c.country_name ))
from oehr_countries c
inner join oehr_regions r
on (r.region_id = c.region_id)
group by r.region_name;
JSON_TABLES
To convert json nested objects with arrays and sub arrays, Format JSON can be used
Select *
From json_table('{
"DECLARATION": {
"FUNCTIONALREFERENCEID": 50286,
"ID": 1,
"LANGUAGECODE": "NL",
"GOODSSHIPMENT": {
"SEQUENCENUMERIC": 1,
"TRANSACTIONNATURECODE": 99,
"GOVERNMENTAGENCYGOODSITEM": [
{
"SEQUENCENUMERIC": 1,
"STATISTICALVALUEAMOUNT": 23,
"TRANSACTIONNATURECODE": 90001,
"DISPATCHCOUNTRYCODE": 90002,
"ACCEPTANCEDATETIME": 90003,
"DUTYTAX_FEES": [
{
"SEQUENCE": 1,
"TYPECODE": "X",
"PAYMENT_METHOD": "AR",
"TAX_BASE": [
{
"SEQUENCE": 1,
"AMOUNT": 10,
"ADAMOUNT": 22
},
{
"SEQUENCE": 2,
"AMOUNT": 20,
"ADAMOUNT": 32
}
]
},
{
"SEQUENCE": 2,
"TYPECODE": "Y",
"PAYMENT_METHOD": "AT",
"TAX_BASE": [
{
"SEQUENCE": 1,
"AMOUNT": 10,
"ADAMOUNT": 22
}
]
}
]
},
{
"SEQUENCENUMERIC": 2,
"STATISTICALVALUEAMOUNT": 23,
"TRANSACTIONNATURECODE": 900088,
"DISPATCHCOUNTRYCODE": 90002,
"ACCEPTANCEDATETIME": 90003,
"DUTYTAX_FEES": [
{
"SEQUENCE": 1,
"TYPECODE": "XXXSSS",
"PAYMENT_METHOD": "TTTT",
"TAX_BASE": [
{
"SEQUENCE": 1,
"AMOUNT": 10,
"ADAMOUNT": 22
},
{
"SEQUENCE": 2,
"AMOUNT": 20,
"ADAMOUNT": 32
}
]
},
{
"SEQUENCE": 2,
"TYPECODE": "@#!@",
"PAYMENT_METHOD": "@SDSD",
"TAX_BASE": [
{
"SEQUENCE": 1,
"AMOUNT": 10,
"ADAMOUNT": 22
}
]
}
]
}
]
}
}
}'
,'$."DECLARATION"."GOODSSHIPMENT"."GOVERNMENTAGENCYGOODSITEM"[*]'
columns(sequence Varchar2(600) path '$."SEQUENCENUMERIC"'
,statistical_value_amount Varchar2(600) path '$."STATISTICALVALUEAMOUNT"'
,transaction_nature_code Varchar2(600) path '$."TRANSACTIONNATURECODE"'
,dispatch_country_code Varchar2(600) path '$."DISPATCHCOUNTRYCODE"'
,commodity_description Varchar2(600) path '$."COMMODITY"."DESCRIPTION"'
,duty_tax_fee_array Clob format json path '$."DUTYTAX_FEES"'
,Nested path '$."DUTYTAX_FEES"[*]'
columns(duty_tax_sequence Varchar2(600) path '$."SEQUENCE"'
,typecode Varchar2(600) path '$."TYPECODE"'
,tax_base Clob format json path '$."TAX_BASE"'
,Nested path '$."TAX_BASE"[*]' columns(tax_base_seq Varchar2(600) path '$."SEQUENCE"')))) jt;
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 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";
---------------------------------------------------------------------------
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")
);
---
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';------ Json Column -----
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;
------ json pretty -----
Select json_serialize(t.po_document Returning Clob pretty)
From pwc_purchaseorder t;
------ Remove Node -----
Select json_transform(t.po_document, remove '$."Special Instructions"' Returning Clob pretty)
From pwc_purchaseorder t;Update JSON column node values
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);
----- 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');
----- Modifying JSON Data On the Fly With JSON_MERGEPATCH
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 from JSON column
----- Query Json data
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;
JSON propertity
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; -- array
Select json_value('[19, "Oracle", {"a":1},[1,2,3]]'
,'$.type()')
From dual; -- array
Select json_value('[19, "Oracle", {"a":1},[1,2,3]]'
,'$.size()')
From dual; --4
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;JSON 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)');
*/
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)))');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;JSON PLSQL Types
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;