Invitation to the Wedding of JSON and Relational Data [LRN1569].pdf

《Invitation to the Wedding of JSON and Relational Data [LRN1569].pdf》由會員分享，可在線閱讀，更多相關《Invitation to the Wedding of JSON and Relational Data [LRN1569].pdf（104頁珍藏版）》請在三個皮匠報告上搜索。

1、Invitation to the Wedding of JSON and Relational Data Kim Berg HansenKim Berg Hansen SQL&PL/SQL developer since 2000 Author of“Practical Oracle SQL”Cegal Danmark since 2022 Oracle ACE Director Core member of MASH Program Member of Symposium 42 SQL quizmaster at OCE in SQL Likes to cook and read sci-

2、fi Member of Danish Beer Enthusiasts Twitterkibeha Bloghttps:/kibeha.dk Other linkshttps:/linktr.ee/kibehaLogoDescription automatically generatedSelect from SQL quizzes on Oracle Dev Gym3 membership tiersConnect:oracleaceF experts helping peers globallyThe Oracle ACE Program recognizes and rewards c

3、ommunity members for their technical and community contributions to the Oracle communityNominateyourself or someone you know: more details on Oracle ACE Program:A tech powerhouse,specialized in energyAgendaInvitation to the Wedding ofJSON and Relational Data 12.1.0.2 JSON arrives 12.2 Courtship begi

4、ns 18 Going steady 19 Popping the question 21 Announcing engagement 23 The Wedding 23 Married lifeNOTE Code executed on a 23ai FREE instance version 23.4.12.1.0.2 JSON arrivesNative JSON support XML DB(!)Developers Guide:https:/ Oracle Database 12c Release 1 New Features:https:/ Changes for Oracle D

5、atabase SQL Language Reference:https:/ table products(id integer constraint products_pk primary key,name varchar2(20 char)not null,attribsblob check(attribs is json);Storage Store in VARCHAR2,CLOB or BLOB (A dedicated datatype comes later)IS JSON condition for predicate or check constraint BLOB has

6、advantages:No character encoding conversion JSON is always UTF-8 Half size of CLOB in multi-byte characterset database12.1.0.2insert into products values(4160,Reindeer Fuel ,142,utl_raw.cast_to_raw(data:OG:1057,FG:1012,IBU:33,ABV:5.1,malts:Maris Otter,Munich,Victory,Pale Chocolate,hops:Nugget,Fuggle

7、s);Insert data Just insert the JSON text If BLOB,insert binary(RAW)in AL32UTF8 If there is IS JSON constraint,error is raised on invalid JSON12.1.0.2selectp.id,p.name,json_value(p.attribs,$.data.IBU returning number)as ibu,json_value(p.attribs,$.data.ABV returning number)as abvfrom products porder b

8、y p.id;JSON_VALUE Retrieve the value of a JSON object or array element JSON path expression specifies what piece of the JSON12.1.0.2selectjson_query(p.attribs,$.malts0 to 2 with wrapper)as first_malts,json_query(p.attribs,$.hops*pretty with wrapper)as all_hops,json_query(p.attribs,$.additions)as an_

9、array,json_query(p.attribs,$.additions*)as contents_becomes_null,json_query(p.attribs,$.additions*with wrapper)as contents_wrapped,json_query(p.attribs,$.additions with wrapper)as array_wrapped,json_query(p.attribs,$.additions with conditional wrapper)as an_array_2,json_query(p.attribs,$.additions*w

10、ith conditional wrapper)as contents_wrapped_2from products porder by p.id;JSON_QUERY Retrieve snippet of JSON WITH CONDITIONAL WRAPPER means:If content of JSON path is an array or object,nothing is done If it is scalar or multiple values,it is wrapped in an array12.1.0.2selectp.id,p.name,json_value(

11、p.attribs,$.data.IBU returning number)as ibu,json_query(p.attribs,$.hops)as hopsfrom products pwhere json_exists(p.attribs,$.hops4)order by p.id;JSON_EXISTS Predicate on whether a given JSON path exists12.1.0.2select p.id,p.name,a.*from products p,json_table(p.attribs,$.datacolumns(ibu integerpath$.

12、IBU,abv number(3,1)path$.ABV)aorder by p.id;JSON_TABLE Instead of multiple JSON_VALUE and JSON_QUERY,retrieve multiple columns from the JSON in a single call12.1.0.2selectp.id,p.name,a.*from products p,json_table(p.attribs,$columns(ibu integerpath$.data.IBU,nested path$.malts*columns(malt_no for ord

13、inality,malt varchar2(20)path$)aorder by p.id;JSON_TABLE nested Values from arrays can be retrieved with NESTED Result is similar to an OUTER JOIN to a child table12.1.0.2create index products_attribs_ix on products(attribs)indextype is ctxsys.contextparameters(section group ctxsys.json_section_grou

14、p sync(on commit);JSON Search Index A JSON Search Index is prerequisite to JSON_TEXTCONTAINS In 12.1 created as a CONTEXT index,just with added parameter12.1.0.2select*from products pwhere json_textcontains(p.attribs,$,Chocolate);JSON_TEXTCONTAINS Operator for JSON full text search of values in a JS

15、ON path Searches for whole words or whole numeric values12.1.0.2selectp.id,p.name,p.attribs.data.IBU as ibu_as_numeric_value,p.attribs.data.ibu as non_existent_path,p.attribs.dataas a_json_object,p.attribs.maltsas a_json_arrayfrom products porder by p.id;JSON Object Access Expressions Dot-Notation A

16、ccess-Requires table alias Returns value if path is a scalar Otherwise returns JSON object or array12.1.0.212.2 Courtship beginsMany enhancements JSON Developers Guide(now dedicated doc):https:/ JSON Developers Guide New Features:https:/ Oracle Database 12c Release 2(12.2)New Features:https:/ Partit

17、ion key of a table can be a virtual column defined with a JSON_VALUE expression on a table column containing JSON JSON Search Index can be created on a partitioned table12.2select p.id,p.name,a.*from products p,json_table(p.attribs,$columns(addresssdo_geometrypath$.brewery.address.geojson,createddat

18、e path$.metadata.created,lastvisit timestamp path$.metadata.lastvisit)a;Additional datatypes supported JSON_VALUE and JSON_TABLE can return more datatypes SDO_GEOMETRY,DATE,TIMESTAMP,and TIMESTAMP WITH TIME ZONE(In JSON date and time is just text in ISO 8601 format)12.2select sdo_geom.sdo_area(geo.g

19、eojson,0.005,units=SQ_KM)sq_kmfrom json_table(httpuritype(utl_url.escape(http:/nominatim.openstreetmap.org/search?|format=json&q=steuben county&polygon_geojson=1).getblob()format json,$*columns(geojson sdo_geometry path$.geojson)geo;GeoJSON and Spatial JSON data in GeoJSON format can be read as SDO_

20、GEOMETRY https:/geojson.org/Enables use of spatial functionality on JSON data12.2JSON columns In-Memory JSON table columns can be placed In-Memory Requires JSON columns has IS JSON constraint Requires MAX_STRING_SIZE=EXTENDED Requires parameter INMEMORY_EXPRESSIONS_USAGE to be STATIC_ONLY or ENABLE

21、Requires parameter INMEMORY_VIRTUAL_COLUMNS to be ENABLE12.2Materialized views on JSON Views projecting values from JSON DATA can be materialized Using JSON_VALUE or JSON_TABLE VARCHAR2 or NUMBER columns Must be read-only12.2selectp.id,p.name,json_value(p.attribs,$.data.FG returning number)as FG,jso

22、n_value(p.attribs,$.data.IBU?(50)as high_ibu_datafrom products pwhere json_exists(p.attribs,$.data?(.FG 1010)or json_exists(p.attribs,$.data?(.IBU$MIN_IBU)passing 70 as min_ibu)order by p.id;JSON Path-Filter expressions Conditions in JSON path expressions Can be used in JSON_EXISTS for filtering the

23、 query Or in JSON_VALUE/QUERY/TABLE for filtering returned value12.2selectp.id,p.name,json_value(p.attribs,$.data.ABV.floor()as abv_int,json_query(p.attribs,$.malts.length()with wrapper)as malt_lengthsfrom products porder by p.id;JSON Path Item methods Functions usable within JSON paths Calculations

24、,like abs(),lower(),length(),etc.Datatype representations,like number(),string(),etc.12.2selectp.id,p.name,p.attribs.malts1,2 as second_third_maltfrom products porder by p.id;Dot-Notation Array Access JSON Object Access Expressions support accessing specificarray elements12.2selectjson_dataguide(p.a

25、ttribs)as dgfrom products p;JSON Data Guide Analyzes JSON data and returns a“schema”of the JSON Function JSON_DATAGUIDE to get a flat data guide12.2create search index products_attribs_ix on products(attribs)for jsonparameters(dataguide on search_on text_value sync(on commit);JSON Search Index In 12

26、.2 dedicated syntax rather than CTXSYS parameter Parameters to control if search index is created or persistent data guide or both12.2declaredg clob;begindg:=dbms_json.get_index_dataguide(PRODUCTS,ATTRIBS,dbms_json.format_hierarchical,dbms_json.pretty);dbms_output.put_line(dg);end;/Hierarchical Data

27、 Guide Show a hierarchical JSON schema from the persistent Data Guide12.2begindbms_json.rename_column(tablename=products,jcolname=attribs,path=$.data.FG,type =dbms_json.type_number,preferred_name=fermented_gravity);end;/PL/SQL API for Data Guide PL/SQL API can be used to create virtual columns or vi

28、ews Based on JSON“schema”discovered by Data Guide Before doing so,it can make sense to add column metadata12.2dbms_json.add_virtual_columns(tablename=products,jcolname=attribs,dataguide =dbms_json.get_index_dataguide(products,attribs,dbms_json.format_hierarchical);Create virtual columns Automaticall

29、y add virtual columns to the table Scalar attributes found by data guide will be created Obeys metadata given by dbms_json.rename_column12.2dbms_json.create_view(viewname=products_view,tablename=products,jcolname=attribs,dataguide =dbms_json.get_index_dataguide(products,attribs,dbms_json.format_hier

30、archical),path=$.data);Create views Automatically create views on a given JSON path Obeys metadata given by dbms_json.rename_column12.2selectjson_object(key groupId value id,key groupName value name)as j_objfrom product_groups;JSON_OBJECT Create a JSON object One or more key/value pairs Keyword KEY

31、is optional12.2selectjson_objectagg(key name value id)as name_to_id_map,json_objectagg(key to_char(id,TM9)value name)as id_to_name_mapfrom product_groups;JSON_OBJECTAGG Aggregate function to create a JSON object Each row becomes a key/value pair12.2selectjson_array(id,name,group_id)as j_arrfrom prod

32、ucts;JSON_ARRAY Create a JSON array Each parameter becomes an element in the array Elements in JSON arrays need not all be same datatype12.2selectjson_arrayagg(name)as j_arrfrom products;JSON_ARRAYAGG Aggregate function to create a JSON array Each row becomes an element in the array12.2select json_o

33、bject(key groupId value g.id,key groupName value g.name,key products value(select json_arrayagg(json_object(key productId value p.id,key productName value p.name)from products pwhere p.group_id=g.id)as j_objfrom product_groups g;Putting it together JSON object having a JSON array as one of the attri

34、butes JSON array having JSON objects as elements12.2beginfor product in(select id,name,attribs from products)loopdbms_output.put_line(json_value(product.attribs,$.brewery.name);dbms_output.put_line(json_query(product.attribs,$.malts);end loop;end;/begindbms_output.put_line(json_object(key dryhopped

35、value true);dbms_output.put_line(json_array(Baltic Porter,4.2,date 2023-09-30);end;/SQL/JSON functions in PL/SQL JSON functions from SQL also available in PL/SQL12.2declareobjjson_object_t;arrjson_array_t;beginobj:=json_object_t.parse(beer:Kumpel,ABV:8.4,hops:Citra,Vienna);dbms_output.put_line(obj.g

36、et_size();dbms_output.put_line(obj.get_string(beer);dbms_output.put_line(obj.get_number(ABV);arr:=obj.get_array(hops);dbms_output.put_line(arr.get_size();dbms_output.put_line(arr.get_string(0);dbms_output.put_line(arr.get_string(1);end;/PL/SQL object types Parse JSON text into JSON_*_T object types

37、Retrieve metadata and data via object methods12.2declareelemjson_element_t;procedure out_type(e json_element_t)is begincasewhen e.is_object then dbms_output.put_line(Is object);when e.is_arraythen dbms_output.put_line(Is array);when e.is_scalar then dbms_output.put_line(Is scalar);end case;end;begin

38、elem:=json_element_t.parse(beer:Kumpel,ABV:8.4,hops:Citra,Vienna);out_type(elem);elem:=treat(elem as json_object_t).get(hops);out_type(elem);elem:=treat(elem as json_array_t).get(0);out_type(elem);end;/JSON_ELEMENT_T Supertype of JSON_OBJECT_T,JSON_ARRAY_T,JSON_SCALAR_T Useful to find out what a JSO

39、N consists of12.2declarebeerjson_object_t:=json_object_t();hops json_array_t:=json_array_t();beginbeer.put(beer,Kumpel);beer.put(ABV,8.4);hops.append(Citra);hops.append(Vienna);beer.put(hops,hops);dbms_output.put_line(beer.to_clob();end;/Construct JSON with PL/SQL types Construct JSON piecewise usin

40、g JSON_*_T object types12.2declareattr blob;objjson_object_t;beginselect p.attribs into attr from products p where id=4160;obj:=json_object_t.parse(attr);obj.put(dryhopped,true);attr:=obj.to_blob();update products pset p.attribs=attrwhere id=4160;commit;end;/Manipulate JSON with PL/SQL types Manipul

41、ate JSON in PL/SQL12.218 Going steadyMore enhancements JSON Developers Guide:https:/ JSON Developers Guide New Features:https:/ Oracle Database Release 18c New Features:https:/ as json)as beerfrom dual)b;TREAT Tell the SQL engine that“this is JSON”Even when you do not have an IS JSON constraint Exam

42、ple:Allow dot notation on a view“AS JSON”column18cselect p.*from products pwhere json_equal(p.attribs,dryhopped:true,data:IBU:33,OG:1057,FG:1012,ABV:5.1,hops:Nugget,Fuggles,malts:Maris Otter,Munich,Victory,Pale Chocolate);JSON_EQUAL Condition to test if two JSON are equal Ignores whitespace differen

43、ce and insignificant ordering i.e.ignore key/value pair ordering in objects but ordering in arrays are significant and not ignored18cselectp.id,p.name,a.*from products p,json_table(p.attribs.datacolumns(ibu integerpath IBU,abv number(3,1)path ABV)aorder by p.id;JSON_TABLE simplified path Use dot not

44、ation as path Simpler when you dont need full JSON path syntax18cselectp.id,p.name,a.*from products p,json_table(p.attribs.datacolumns(IBU integer,ABV number(3,1)aorder by p.id;JSON_TABLE inferred path Skip path if column name is path(case sensitive!)Can even skip datatype if you want a maxsize stri

45、ng column18cselect*from json_table(Simcoe,8.8,42,true,$*columns(only_number number path$.numberOnly(),all_numbernumber path$.number(),only_string varchar2(10)path$.stringOnly(),all_stringvarchar2(10)path$.string(),only_boolvarchar2(10)path$.booleanOnly(),data_typevarchar2(20)path$.type();JSON Path N

46、ew item methods type()gives datatype of the JSON size()gives number of elements in object or array numberOnly(),stringOnly(),booleanOnly()returns value only if the datatype matches18cLOB support Querying JSON can return LOBs JSON_VALUE can return CLOBs JSON_VALUE can return CLOBs or BLOBs JSON_TABLE

47、 can return either depending on column Generating JSON can create LOBs JSON_OBJECT,JSON_OBJECTAGG,JSON_ARRAY,JSON_ARRAYAGG can return CLOBs or BLOBs18cselectjson_dataguide(p.attribs,dbms_json.format_hierarchical,dbms_json.pretty)dgfrom products p;Hierarchical Data Guide Standalone JSON_DATAGUIDE fun

48、ction now supports hierarchical format like persistent dataguide Also supports prettyprinting18cselect table_name,column_name,path,type,lengthfrom user_json_dataguide_fields;Data dictionary views Dictionary views on dataguides expanded with new views to show the fields,not just show existence of dat

49、aguides DBA/ALL/USER_JSON_DATAGUIDE_FIELDS18cselectj.text,j.tmtz,to_utc_timestamp_tz(j.text)from json_table(2023-09-29T18:08:53.239628Z,2023-09-29T20:08:53.239628+02:00,$*columns(tmtz timestamp with time zone path$,text varchar2(40)path$)j;TO_UTC_TIMESTAMP_TZ New SQL function TO_UTC_TIMESTAMP_TZ Han

50、dles ISO 8601 using UTC as default time zone Same effect as JSON_TABLE column timestamp with time zone18c19 Popping the questionFurther enhancements JSON Developers Guide:https:/ JSON Developers Guide New Features:https:/ Oracle Database Release 19c New Features:https:/ patched_attribsfrom products

51、pwhere p.id=7790;JSON_MERGEPATCHupdate products pset p.attribs=json_mergepatch(p.attribs,data:FG:1001,IBU:null,malts:2-row,Munich,dryhopped:false)where p.id=7790;Removes elements if theyre null in JSON patch Modifies elements if they have different value in JSON patch Adds elements if there are new

52、in JSON patch19cselectjson_serialize(p.attribs returning varchar2(1000)pretty)as json_vcfrom products pwhere p.id=7790;JSON_SERIALIZE Produce a textual representation of JSON data No matter what datatype it is stored as Optional prettyprinting19cselectjson_object(groupId:g.id,groupName:any_value(g.n

53、ame),products:json_arrayagg(json_object(productId:p.id,productName:p.name)as j_objfrom product_groups g join products p on p.group_id=g.idgroup by g.id;JSON_OBJECT simplified syntax Use:instead of VALUE Makes the code look more like JSON itself19cselect id,name,dryhopped,og,fg,ibu,abvfrom productsne

54、sted attribs columns(dryhopped,nested data columns(OG number,FG number,IBU number,ABV number);NESTED clause of SELECT Simplified syntax without needing JSON_TABLE Works like a LEFT OUTER JOIN/OUTER APPLY to JSON_TABLE19ccreate or replace type hops_list_t as table of varchar2(20);/create or replace t

55、ype beer_t as object(beer varchar2(20),abvnumber,hops hops_list_t);/selectjson_object(beer_t(Kumpel,8.4,hops_list_t(Citra,Vienna)from dual;SQL Object Type to JSON Create JSON from a SQL Object Type with JSON_OBJECT19cselectjson_value(b.beer,$returning beer_t)as beerfrom(selecttreat(beer:Kumpel,ABV:8

56、.4,hops:Citra,Vienna as json)as beerfrom dual)b;JSON to SQL Object Type Create SQL Object Type from JSON with JSON_VALUE Match of JSON elements to type attributes is case insensitive Ordering of elements in JSON object is insignificant19c21 Announcing engagementContinuing enhancements JSON Developer

57、s Guide:https:/ Release 21 Learning Database New Features:https:/ JSON Document Store:https:/ table products(id integer constraint products_pk primary key,name varchar2(20 char)not null,group_idnot null constraint products_product_groups_fkreferences product_groups,attribsjson);JSON datatype Dedicat

58、ed datatype JSON Stored in optimized OSON format(minus insignificant whitespace)https:/ OCI and JDBC drivers can work directly with this format21cinsert into products values(4160,Reindeer Fuel ,142,json(data:OG:1057,FG:1012,IBU:33,ABV:5.1,malts:Maris Otter,Munich,Victory,Pale Chocolate,hops:Nugget,F

59、uggles);selectjson(beer:Kumpel,ABV:8.4,hops:Citra,Vienna)as j1,json beer:Kumpel,ABV:8.4,hops:Citra,Vienna as j2from dual;JSON datatype constructor JSON constructor accepts VARCHAR2,CLOB,BLOB Constructs JSON data type Dont need to test IS JSON or use TREAT AS JSON21cselect json groupId:g.id,groupName

60、:g.name,products:select json productId:p.id,productName:p.namefrom products pwhere p.group_id=g.id as j_objfrom product_groups g;Alternative to JSON_OBJECT/ARRAY/AGG JSON constructor also accepts simplified syntax similar to JSON_OBJECT allowing construction without JSON_*functions21cupdate products

61、 pset p.attribs=json_transform(p.attribs,remove$.data.IBU,replace$.malts1=Munich,set$.dryhopped=false)where p.id=7790;JSON_TRANSFORM Performs manipulations on JSON Either on-the-fly for transforming JSON for output Or in UPDATE,where OSON format allows updating without necessarily overwriting the en

62、tire JSON21cselect json_scalar(Caramunich)as json_malt from dual;JSON_SCALAR New function JSON_SCALAR to create a JSON datatype scalar21cselectp.id,p.name,p.attribs.malts*.count()as malt_count,json_value(p.attribs,$.hops*.maxString()as max_hopfrom products p;Aggregate item methods Item methods that

63、aggregate on the values in the path avg(),count(),minNumber(),maxNumber(),minString(),maxString(),and sum()Can be used both in JSON path and dot notation21ccreate multivalue index products_hops_ix on products p(p.attribs.hops);select id,namefrom products pwhere json_exists(p.attribs,$.hops?(=Citra);

64、Multivalue Index Index targeting scalars in an array21cbegindbms_json.create_view(viewname=products_mview,tablename=products,jcolname=attribs,dataguide =dbms_json.get_index_dataguide(products,attribs,dbms_json.format_hierarchical),materialize=true,mvrefreshmode=dbms_json.MV_REFRESH_ON_COMMIT);end;DB

65、MS_JSON.CREATE_VIEW materialized New parameters to DBMS_JSON.CREATE_VIEW Materialized view log must exist21c23 The WeddingComplete merge of JSON and relational JSON Developers Guide:https:/ Oracle Database New Features:https:/ JSON-Relational Duality Developers Guide:https:/ Duality Special type of

66、view created on relational tables View generates JSON from one or more tables Generated JSON also contains metadata Tables are single source of truth single place for data Can be manipulated as relational data Can be manipulated as JSON data Think of it as a turbocharged version of a view constructi

67、ng JSON and having built-in INSTEAD OF triggersMore details to follow later23aiselectp.id,p.name,p.attribsfrom products pwhere p.attribs is json validatetype:object,properties:data:type:object,malts:type:array,hops:type:array,additions:type:array,required:additions;JSON Schema validation IS JSON has

68、 clause VALIDATE to validate if it matches schema Can be in IS JSON check constraint to enforce JSON matches schema23aiselect p.id,p.name,p.attribs,dbms_json_schema.is_valid(p.attribs,json type:object,properties:data:type:object,malts:type:array,hops:type:array,additions:type:array,required:addition

69、s)as is_validfrom products p;JSON Schema validation Check validity with DBMS_JSON_SCHEMA.IS_VALID Supports schema as JSON type,where IS JSON VALIDATE requires literal23aiJSON_TRANSFORM 21c operations APPEND INSERT KEEP REMOVE RENAME REPLACE SET 23ai added operations CASE COPY INSERSECT KEEP MERGE MI

70、NUS NESTED PATH PREPEND SORT UNION23aicreate table beers_ext(data json)organization external(type oracle_bigdataaccess parameters(com.oracle.bigdata.fileformat=jsondoc)location(dump_file_dir:beers.json)reject limit unlimited;JSON in External Tables Easy load of JSON documents from files23aideclarety

71、pe beer_group_t is table of varchar2(20)index by varchar2(20);type hops_tis varray(10)of varchar2(20);beer_groupsbeer_group_t;hops hops_t:=hops_t(Citra,Simcoe);beginbeer_groups(Hoppy Crude Oil):=Stout;beer_groups(Summer in India):=IPA;dbms_output.put_line(json_serialize(json(beer_groups);dbms_output

72、.put_line(json_serialize(json(hops);end;/JSON Constructor and PL/SQL collections Associative arrays become JSON objects with key/value pairs VARRAYs become JSON arrays23aideclaretype beer_group_t is table of varchar2(20)index by varchar2(20);type hops_tis varray(10)of varchar2(20);beer_groupsbeer_gr

73、oup_t;hops hops_t;beginhops:=json_value(Citra,Simcoe,$returning hops_t);dbms_output.put_line(hops(1);dbms_output.put_line(hops(2);beer_groups:=json_value(Hoppy Crude Oil:Stout,Summer in India:IPA,$returning beer_group_t);dbms_output.put_line(beer_groups(Hoppy Crude Oil);dbms_output.put_line(beer_gro

74、ups(Summer in India);end;JSON_VALUE and PL/SQL collections JSON_VALUE in PL/SQL can use PL/SQL collection types in RETURNING clause23aiselect json_object(key groupId value g.id,key groupName value g.name,key products value json_array(select json_object(key productId value p.id,key productName value

75、p.name)from products pwhere p.group_id=g.id)as j_objfrom product_groups g;JSON_ARRAY from subquery A subquery can be input to JSON_ARRAY to build the array without needing a JSON_ARRAYAGG in the subquery23aiselectjson_serialize(p.attribs ordered)as serialized_ordered,json_serialize(p.attribs pretty

76、ordered)as serialized_pretty_orderedfrom products p;ORDERED in JSON_SERIALIZE Specifying ORDERED in JSON_SERIALIZE orders key/value pairs in JSON objects by key Easier for comparison by human eye or tools/version control Can be combined with PRETTY23aijson_type_convertible_check Recommended to conve

77、rt textual JSON columns to JSON type Process in 3 steps:Run DBMS_JSON.json_type_convertible_check for columnThis validates that the data can actually be converted to JSON type Migrate the data CTAS,Data Pump,DBMS_REDEFINITION,add/drop/rename column Handle dependencies on original column23ai23-Marrie

78、d lifecreate table customers(id integer constraint customer_pk primary key,name varchar2(20 char)not null);create table orders(id integer constraint order_pk primary key,customer_id not null constraint order_customer_fkreferences customers,ordered date,delivery date);Relational tables Normal relatio

79、nal tables23aicreate json relational duality view customer_dv asselectjson _id:c.id,name:c.name,orders:selectjson orderId:o.id,ordered:o.ordered,delivery:o.deliveryfrom orders o with insert update deletewhere o.customer_id=c.idfrom customers c with insert update delete;JSON-Relational Duality View23

80、aiinsert into customers values(50042,The White Hart);insert into customers values(51069,Der Wichtelmann);insert into customers values(50741,Hygge og Humle);insert into customers values(51007,Boom Beer Bar);insert into orders values(421,50042,date 2019-01-15,null);insert into orders values(422,51069,

81、date 2019-01-17,null);insert into orders values(423,50741,date 2019-01-18,null);insert into orders values(424,51069,date 2019-01-28,null);insert into orders values(425,51069,date 2019-02-17,null);insert into orders values(426,50741,date 2019-02-26,null);insert into orders values(427,50042,date 2019-

82、03-02,null);insert into orders values(428,50741,date 2019-03-12,null);insert into orders values(429,50042,date 2019-03-22,null);insert into orders values(430,50741,date 2019-03-29,null);Insert Relational23aiselectjson_serialize(data pretty)as json_datafrom customer_dv;Query JSON with SQL_metadata:et

83、ag:D893627CCA279BA9AC688B35DD5AABF5,asof:000000000026F0FE,_id:50042,name:The White Hart,orders:orderId:421,ordered:2019-01-15T00:00:00,delivery:null,orderId:427,ordered:2019-03-02T00:00:00,delivery:null,orderId:429,ordered:2019-03-22T00:00:00,delivery:null23aiinsert into customer_dv values(_id:52422

84、,name:Kings Arms,orders:orderId:503,ordered:2023-09-29T00:00:00,orderId:504,ordered:2023-09-30T00:00:00);Insert JSON with SQL23aiselect*from customers where id=52422;ID NAME -52422 Kings Arms select*from orders where customer_id=52422;ID CUSTOMER_ID ORDERED DELIVERY -503 52422 2023-09-29 00:00:00 50

85、4 52422 2023-09-30 00:00:00 Query relational See the inserted data from the relational side23aicurl-request POST-url http:/localhost:8080/ords/kibeha/customer_dv/-header Content-Type:application/json-data customerId:52422,name:Kings Arms,orders:orderId:503,ordered:2023-09-29T00:00:00,orderId:504,ord

86、ered:2023-09-30T00:00:00Insert JSON with REST23aiMigrate JSON to Duality Take existing JSON documents Infer a schema from the content Generate relational tables Generate JSON Relational Duality View Copy data from JSON docs to view Result:JSON data migrated to relational data JSON docs still availab

87、le via duality view23aicreate table cust(data json);insert into cust(data)values(json _id:50042,name:The White Hart,orders:orderId:421,ordered:2019-01-15T00:00:00,orderId:427,ordered:2019-03-02T00:00:00,orderId:429,ordered:2019-03-22T00:00:00),(json .Original JSON Work table containing original JSON

88、 documents23aideclareddl clob;beginddl:=dbms_json_duality.infer_and_generate_schema(json(tableNames:CUST,viewNames:CUST_DV,outputFormat:executable,updatability:true,useFlexFields:false);dbms_output.put_line(ddl);execute immediate ddl;end;/Migrate structure Infers schema Generates relational tables a

89、nd duality view23aiselect object_type,object_namefrom user_objectswhere object_name like CUST_DV%order by object_type,object_name;OBJECT_TYPE OBJECT_NAME-TABLE CUST_DV_ORDERSTABLE CUST_DV_ROOTVIEW CUST_DVMigrated structure Created*_ROOT table and child*_ORDERS table Created duality view generating J

90、SON docs from relational tables23aibegindbms_json_duality.import(table_name=CUST,view_name=CUST_DV);end;/select json_serialize(data pretty)as json_datafrom cust_dv;select*from cust_dv_root;select*from cust_dv_orders;Migrate data Copies JSON to relational via duality view23aiAnd they lived happily ev

91、er afterLinks and Q&A This Presentationhttps:/bit.ly/jsonwedding2 Companion scripthttps:/bit.ly/jsonwedding2_script Twitterkibeha Bloghttps:/kibeha.dk Other linkshttps:/linktr.ee/Author of Practical Oracle SQL Not a SQL-101 book Not a reference manual replacement For developers knowing basic SQL-92

92、syntax but wanting to advance further More elaborate examples relating to daily life as very simple examples are difficult to relate to work Useful SQL features that arent widely used-but should be More background in an interview in NoCOUG Journal:http:/nocoug.org/Journal/NoCOUG_Journal_202002.pdf#p

93、age=4 The book:https:/ AND SPEAKER HUBOur goal is to connect speakers with mentorsto assist in preparing technical sessionsand improving presentation skillsInterested?Read more and get in touchhttps:/Created by the community,to support the communitySharing of reliable knowledgeSupporting the various user groups and individualshttps:/sym42.org/sym_42