JSON Information Guide in Oracle Database 12c Launch 2 (12.2 )

|||||||Database 12c Release 2( 12.2) Streamline the communication with JSON information kept in

the data source using the JSON Information Guide capability introduced in Oracle Data source 12c Release 2( 12.2). Relevant articles.Setup Produce and inhabit the following table to provide some JSON information to collaborate with. For the information guide capability to work the table needs to consist of the IS JSON check restriction on the column

holding the

JSON data. DECLINE TABLE json_documents REMOVE; DEVELOP TABLE json_documents( id RAW (16) NOT VOID, data CLOB, RESTRICTION json_documents_pk PRIMARY KEY (id),. RESTRICTION json_documents_json_chk CHECK( information IS JSON ).);. PUT RIGHT INTO json_documents

( id, data). VALUES( SYS_GUID(),.

' ');. PUT
INTO json_documents (id, data). WORTHS (SYS_GUID(),.
' " FirstName": "Jayne",. "LastName":" Doe",." Job":" Supervisor",.
" Address": 
,.
" ContactDetails": ,.
" DateOfBirth": "01-JAN-1982",.
" Energetic": false.
');

.

DEDICATE;. DIRECTOR DBMS_STATS. gather_table_stats( USER, 'json_documents');

Develop a JSON Look Index

The information guide details is included in the JSON search index by default.

CREATE SEARCH INDEX json_docs_search_idx ON json_documents (information) FOR JSON;

If you want the persistent data guide details without the search index, you can develop the JSON search index with the adhering to criteria.

DECLINE INDEX json_docs_search_idx;.
PRODUCE SEARCH INDEX json_docs_search_idx ON json_documents (information) FOR JSON.
PARAMETERS (' SEARCH_ON NONE');

You could remove and also include the information guide capability from an existing JSON search index utilizing the ALTER INDEX command.

MODIFY INDEX json_docs_search_idx REBUILD CRITERIA (' DATAGUIDE OFF');.
CHANGE INDEX json_docs_search_idx REBUILD CRITERIA (' DATAGUIDE ON');

Replace the index with a regular JSON search index.

DECREASE INDEX json_docs_search_idx;.
DEVELOP SEARCH INDEX json_docs_search_idx ON json_documents (data) FOR JSON;

Show JSON Data Guide (GET_INDEX_DATAGUIDE, JSON_DATAGUIDE)

The DBMS_JSON. GET_INDEX_DATAGUIDE feature returns the information overview summary for the data in the JSON column. The result could be changed utilizing the input parameters, consisting of showing the outcome in a flat or hierarchical format in addition to rather print.

COLLECTION LONG 1000000 PAGESIZE 1000.

SELECT DBMS_JSON. get_index_dataguide(
.' json_documents',.' information',.
DBMS_JSON. format_flat,.
DBMS_JSON.

pretty) AS dg. FROM dual;. DG. --------------------------------------------------------------------------------.
[,.

,.

,. 
" o: course":"$. Address.City",.
" kind":" string",." o: size": 8,.
" o: preferred_column_name":" DATA$ City".
,.

" o: path": "$. Address.Street",.
" type":" string",." o: size":
16,.
" o: preferred_column_name": "DATA$ Street".
,. " o: path":
"$. Address.Country",." type": "string",.
" o: length": 2,.
" o: preferred_column_name": "DATA$ Country".
,.

" o: path": "$. Address.Postcode",.
" kind":" string",." o: length":
8,.
" o: preferred_column_name": "DG_POSTCODE".
,. " o: path":
"$. LastName",." type": "string",.
" o: length": 4,.
" o: preferred_column_name": "DATA$ LastName".
,. 
,. 
" o: course"
:"$. DateOfBirth",.
" type":" string",.
" o: length":

16,
." o: preferred_column_name":" DATA$ DateOfBirth".,. ,.

,. " o: path":
"$.
ContactDetails.Phone",.
" kind":" string",." o: length
": 16,.
" o: preferred_column_name":" DATA$ Phone".

,.
] SQL >.
COLLECTION LONG 1000000 PAGESIZE 1000.
PICK DBMS_JSON.
get_index_dataguide(.' json_documents',.
' data',.
DBMS_JSON.
format_hierarchical,.
DBMS_JSON.
pretty) AS dg. FROM twin;. DG. --------------------------------------------------------------------------------. 
" type":" object",." buildings ":.
" Task":.
,.
" Energetic":.

" kind": "boolean",.
" o: length": 8,.
" o: preferred_column_name":" DG_ACTIVE".

,."><
SQL > The information can also be inquiries making use of the JSON_DATAGUIDE feature. CHOOSE json_dataguide (information) dg_doc. FROM json_documents;

. DG_DOC. ——————————————————————————–. [code> “string”,<, , " o: path ":"$. Address"," kind ": " item" ," o: size":128, , , " o: path":"$. Address.Country ", " kind":" string ", " o.: length":2 , " o: course ":"$. Address.Postcode "," kind ":" string"," o: length":8 , " o: rub. h":"$. LastName "," kind ": " string"," o: size ":4 , "o: course": "$. FirstName "," kind ": " st. ring",<, "o: course ":"$. DateOfBirth", "kind":" string ", "o: size ":16, " o.: course":" $. ContactDetails "," type"<, pre>

“:” string”, “o: size”:32, , ” o: course”:”$. ContactDetails.Twitter”,” type”: “strin.
g”,” o: size”:8
] SQL > The paperwork recommends utilizing this information together with JSON_TABLE to display the details in a flat fashion. WITH dg_t AS(. SELECT json_dataguide( data) dg_doc
. FROM json_documents.
). CHOOSE jt.
*.
FROM dg_t,. json_table( dg_doc,’
$ [*]’. COLUMNS. jpath VARCHAR2( 40) COURSE’$.
” o: course”‘,. type VARCHAR2( 10) COURSE
‘$.

” kind” ‘,. tlength NUMBER PATH ‘$.” o: length”‘) jt. ORDER BY jt.jpath;. JPATH TYPE TLENGTH.

—————————————- ———- ———-.
$.
Energetic boolean 8.$. Address object 128
.
$.
Address.City
string 8.
$. Address.Country string 2. $. Address.Postcode string 8.$. Address.Street string 16.
$. ContactDetails object 128.$.
ContactDetails.Email string 32.
$. ContactDetails.Phone string 16.
$. ContactDetails.Twitter string 8.$.
DateOfBirth string 16.$.
FirstName string 8.$.
Work string 8.$. LastName string 4. SQL > We can do the very same with the GET_INDEX_DATAGUIDE feature. WITH dg_t AS(.

PICK DBMS_JSON. get_index_dataguide(.’ json_documents’,.’ data’,.
DBMS_JSON. format_flat) AS dg_doc. FROM twin.). CHOOSE jt. *. FROM dg_t,. json_table( dg_doc,’$ [*]. COLUMNS. jpath VARCHAR2( 40) COURSE’$. “o: path”‘,. type VARCHAR2( 10) PATH’$. “kind”‘,. tlength NUMBER COURSE’ $. “o: size”‘) jt. ORDER BY jt.jpath;. JPATH KIND TLENGTH. —————————————- ———- ———-. $. Active boolean 8.
$. Address item 128.$. Address.City string 8.
$. Address.Country string 2.$. Address.Postcode string 8.
$. Address.Street string 16.$. ContactDetails object 128.
$. ContactDetails.Email string 32.$. ContactDetails.Phone string 16.
$. ContactDetails.Twitter string 8.$. DateOfBirth string 16.$. FirstName string 8.
$. Task string 8.$. LastName string 4. SQL > By default the information guide doesn’t include stats for the aspects, however these could be included by collecting stats on the JSON search index as adheres to. DIRECTOR DBMS_STATS.
gather_index_stats( INDIVIDUAL,’ json_docs_search_idx’, estimate_percent= > 99); You will certainly now see statistical details in the data overview, as shown listed below in 2 components drawn from the information guide. Notification the “Twitter” component is only present in
50% of the files. “Phone”:.
,. “Twitter”:. pre>

” o: regularity”: 50,. “o: low_value”:” @johndoe”,. “o: high_value”:” @johndoe”,. “o: num_nulls”: 0,. “o: last_analyzed”: “2018-01-01T13:12:30”. We could layout this as rows making use of JSON_TABLE as previously. SET LINESIZE 160.

WITH dg_t AS(. SELECT DBMS_JSON. get_index_dataguide(.’ json_documents ‘,.’ information ‘,. DBMS_JSON. format_flat) AS dg_doc. FROM double.). SELECT jt. *. FROM dg_t,. json_table( dg_doc, '$ [*]. COLUMNS.

jpath VARCHAR2( 30) PATH'$. "o: path"',
. type VARCHAR2( 10 )COURSE'$.
"type" ',.
tlength NUMBER PATH'$. "o: length" '>,. regularity NUMBER COURSE'$.
"o: frequency"',. low_value VARCHAR2( 25) COURSE'$.
"o

: low_value”‘,. high_value VARCHAR2 (25) COURSE’$. “o: high_value” ‘,. num_nulls PHONE NUMBER COURSE ‘$.

"o: num_nulls"',.
last_analyzed VARCHAR2 (20) PATH '$.
"o: last_analyzed"') jt. ORDER BY jt.jpath;. JPATH KIND TLENGTH REGULARITY LOW_VALUE HIGH_VALUE NUM_NULLS LAST_ANALYZED. ------------------------------ ---------- ---------- ---------- ------------------------- ------------------------- ---------- --------------------.$. Active boolean 8 100 real incorrect 0 2018-01-01T13:17:50.$. Address things 128 100 2018 -01 -01 T13:17:50.
$. Address.City string 8 100 My City My City 0 2018-01-01T13:17:50.
$. Address.Country string 2 100 UK 0 2018-01-01T13:17:50.
$. Address.Postcode string 8 100 A12 34B A12 34B 0 2018-01-01T13:17:50.$. Address.Street string 16 100 99 My Road 100 My Road 0 2018-01-01T13:17:50.$. ContactDetails object 128 100 2018 -01 -01 T13:17:50.
$. ContactDetails.Email string 32 100 john.doe@example.com jayne.doe@example.com 0 2018-01-01T13:17:50.
$. ContactDetails.Phone string 16 100 44 123 12345 6 0 2018-01-01T13:17:50.
$. ContactDetails.Twitter string 8 50 @johndoe @johndoe 0 2018-01-01T13:17:50.
$. DateOfBirth string 16 100 01-JAN-1980 01-JAN-1982 0 2018-01-01T13:17:50.

$. FirstName string 8 100 John Jayne 0 2018-01-01T13:17:50. $. Work string 8 100 Clerk Manager 0 2018-01-01T13:17:50.$. LastName string 4 100 Doe 0 2018-01-01T13:17:50. 14 rows selected. SQL > Include as well as Get Rid Of Digital Columns( ADD_VIRTUAL_COLUMNS,
DROP_VIRTUAL_COLUMNS, RENAME_COLUMN) The JSON information guide information could be used> to include digital columns to the table> holding the JSON information utilizing the ADD_VIRTUAL_COLUMNS procedure. BEGIN. DBMS_JSON. add_virtual_columns (. tablename= >' json_documents'

,. jcolname= > 'data',.
dataguide => DBMS_JSON
. get_index_dataguide(.' json_documents',.' data',. DBMS_JSON. format_hierarchical))
;. END;.
/ If we define the table we>

can see it currently has some additional columns. SQL > DESC json_documents. Name Null? Kind. ----------------------------------------- -------- ----------------------------.
ID NOT NULL RAW( 16). DATA CLOB. DATA$ Job VARCHAR2( 8). DATA$ Energetic VARCHAR2( 8). DATA$ City VARCHAR2( 8). DATA$ Street VARCHAR2( 16).
DATA$ Country VARCHAR2( 2). DATA$ Postal Code VARCHAR2( 8). DATA$ LastName VARCHAR2( 4). DATA$ FirstName VARCHAR2( 8).
DATA$ DateOfBirth VARCHAR2( 16). DATA$ Email VARCHAR2 (32). DATA$ Phone VARCHAR2( 16). DATA$ Twitter VARCHAR2( 8).
SQL > The digital columns can be gotten rid of utilizing the DROP_VIRTUAL_COLUMNS treatment. BEGIN. DBMS_JSON. drop_virtual_columns(.
tablename= >' json_documents',. jcolname= >' information');. END;./ If we describe the table we could see the digital columns are now gone. SQL > DESC json_documents. Name Null? Kind. ----------------------------------------- -------- ----------------------------. ID NOT NULL RAW( 16). INFORMATION CLOB. SQL > The RENAME_COLUMN
procedure could be utilized to alter the name of the virtual columns developed.-- Define the needed column names. BEGIN.
DBMS_JSON. rename_column(' json_documents', 'data', '$. Work', DBMS_JSON. TYPE_STRING, 'DG_JOB');.
DBMS_JSON. rename_column(' json_documents', 'information', '$. Active', DBMS_JSON. TYPE_BOOLEAN,' DG_ACTIVE');. DBMS_JSON. rename_column(' json_documents',' data', '$. Address.City ', DBMS_JSON. TYPE_STRING,' DG_CITY');. DBMS_JSON. rename_column(' json_documents ',' information','$.
Address.Street ', DBMS_JSON.
TYPE_STRING,' DG_STREET');.
DBMS_JSON.> rename_column(' json_documents ','
data','$. Address.Country', DBMS_JSON.
TYPE_STRING,' DG_COUNTRY');. DBMS_JSON.
rename_column( 'json_documents
',' information ','$.
Address.Postcode', DBMS_JSON. TYPE_STRING,' DG_POSTCODE');. DBMS_JSON.
rename_column( 'json_documents ',' information','$. LastName', DBMS_JSON. TYPE_STRING,' DG_LAST_NAME');. DBMS_JSON.
rename_column( 'json_documents',' information',' $.
FirstName', DBMS_JSON.
TYPE_STRING, 'DG_FIRST_NAME ');
. DBMS_JSON.
rename_column( 'json_documents ',' data', '$.
DateOfBirth ', DBMS_JSON.

TYPE_STRING,'

DG_DOB');. DBMS_JSON. rename_column (' json_documents ',' data ', '$. ContactDetails.Email', DBMS_JSON.
TYPE_STRING,'
DG_EMAIL');.
DBMS_JSON. rename_column(' json_documents ',' information','$.
ContactDetails.Phone', DBMS_JSON. TYPE_STRING,' DG_PHONE'
);. DBMS_JSON. rename_column(' json_documents ',' information','$. ContactDetails.Twitter', DBMS_JSON.

TYPE_STRING,'

DG_TWITTER ');.

END;.
/.-- Add the online columns
. BEGIN. DBMS_JSON. add_virtual_columns(. tablename= > 'json_documents
'

,. jcolname =>' information ',. dataguide= > DBMS_JSON. get_index_dataguide(. 'json_documents',.' information',. DBMS_JSON. format_hierarchical));. END;./. DESC json_documents. Name Null? Type. ----------------------------------------- -------- ----------------------------. ID NOT NULL RAW( 16 ). INFORMATION CLOB. DG_JOB VARCHAR2( 8). DG_ACTIVE VARCHAR2 (8). DG_CITY VARCHAR2 (8).

DG_STREET VARCHAR2( 16). DG_COUNTRY VARCHAR2 (2). DG_POSTCODE VARCHAR2 (8).

DG_LAST_NAME VARCHAR2( 4).
DG_FIRST_NAME VARCHAR2( 8).
DG_DOB VARCHAR2 (16).
DG_EMAIL VARCHAR2 (32).
DG_PHONE

VARCHAR2( 16).
DG_TWITTER VARCHAR2 (8).
SQL > With the virtual columns in place we could quiz the JSON information as if it were a normal table. PICK id,. dg_first_name,. dg_last_name,.
dg_job. FROM json_documents;.
ID DG_FIRST DG_L DG_JOB.
-------------------------------- ---------- --------.
619198B94C4C4C75E0538838A8C0CFC3 John Doe Clerk. 619198B94C4D4C75E0538838A8C0CFC3 Jayne Doe Manager. SQL > Eliminate the digital columns. BEGIN. DBMS_JSON.
drop_virtual_columns(.
tablename= >' json_documents
',. jcolname= > 'data' );
. END;./ We can also choose which columns need to be forecasted based upon the frequency of the aspects in the documents saved>

in the JSON column.

We need to collect data for the JSON search index
for this to function.> In the instance below we project columns for components that appear in 100 % of the files

, so the DG_TWITTER column is no longer existing. EXEC DBMS_STATS. gather_index_stats( CUSTOMER,' json_docs_search_idx', NULL, 99)

;. BEGIN. DBMS_JSON. add_virtual_columns (. tablename= >' json_documents ',. jcolname= > 'information ',. frequency => 100 );. END;./. DESC json_documents. Name Null? Type. ----------------------------------------- -------- ----------------------------. ID NOT NULL RAW (16). DATA CLOB. DG_JOB VARCHAR2 (8). DG_ACTIVE VARCHAR2( 8 ). DG_CITY VARCHAR2( 8). DG_STREET VARCHAR2 (16).

DG_COUNTRY VARCHAR2( 2). DG_POSTCODE VARCHAR2 (8).
DG_LAST_NAME VARCHAR2( 4). DG_FIRST_NAME VARCHAR2( 8). DG_DOB VARCHAR2( 16). DG_EMAIL VARCHAR2( 32). DG_PHONE VARCHAR2( 16).
SQL > Get rid of the virtual columns. BEGIN. DBMS_JSON. drop_virtual_columns(. tablename= >' json_documents ',.
jcolname= >' data');. END;./ Create Sight (CREATE_VIEW, CREATE_VIEW_ON_PATH) Rather than including online columns to the table we could create a sight based on the JSON information guide information utilizing
the CREATE_VIEW procedure. The column names are specified utilizing the RENAME_COLUMN procedure, like we made use of throughout the creation of the online columns.-- Specify the called for column names.
BEGIN. DBMS_JSON. rename_column(' json_documents', 'data', '$.
Job', DBMS_JSON. TYPE_STRING,' JOB');. DBMS_JSON. rename_column (' json_documents', 'data', '$. Energetic', DBMS_JSON. TYPE_BOOLEAN, 'ACTIVE');.
DBMS_JSON. rename_column(' json_documents', 'information', '$. Address.City', DBMS_JSON. TYPE_STRING, 'CITY');
. DBMS_JSON. rename_column(' json_documents',' data', '$. Address.Street ', DBMS_JSON. TYPE_STRING,' STREET');. DBMS_JSON. rename_column(' json_documents ',' data','$. Address.Country', DBMS_JSON. TYPE_STRING, 'NATION ');.
DBMS_JSON

. rename_column(' json_documents',' data', '$.
Address.Postcode', DBMS_JSON. TYPE_STRING,' POSTCODE');. DBMS_JSON.
rename_column( 'json_documents','
data ','
$. LastName', DBMS_JSON. TYPE_STRING,' LAST_NAME');.
DBMS_JSON. rename_column(' json_documents',' information
','$.
FirstName',

DBMS_JSON.
TYPE_STRING,' FIRST_NAME');.
DBMS_JSON. rename_column(' json_documents',' data','$. DateOfBirth', DBMS_JSON. TYPE_STRING,' DOB');. DBMS_JSON.
rename_column(' json_documents ', 'data','$.
ContactDetails.Email', DBMS_JSON.
TYPE_STRING, 'EM AIL ');
. DBMS_JSON.
rename_column(' json_documents',' data', '$
. ContactDetails.Phone', DBMS_JSON.
TYPE_STRING,' PHONE');.
DBMS_JSON. rename_column( 'json_documents ',' information', '$.
ContactDetails.Twitter', DBMS_JSON.
TYPE_STRING,' TWITTER ');
. END;./.

DECREASE SIGHT json_documents_v1;.-- Produce the sight. BEGIN. DBMS_JSON. create_view(. viewname= >' json_documents_v1',. tablename= > 'json_documents',. jcolname => 'data',. dataguide= > DBMS_JSON. get_index_dataguide (. 'json_documents',.' data',. DBMS_JSON. format_hierarchical ));. END;./. DESC json_documents_v1. Call Null? Kind. ----------------------------------------- -------- ----------------------------. ID NOT NULL RAW( 16).

WORK VARCHAR2( 8 ).
ACTIVE
VARCHAR2( 8). CITY VARCHAR2( 8
). STREET VARCHAR2( 16).
NATION VARCHAR2 (2).
POSTCODE VARCHAR2( 8).
LAST_NAME VARCHAR2( 4). FIRST_NAME VARCHAR2
( 8).
DOB
VARCHAR2( 16).
E-MAIL VARCHAR2( 32
). PHONE VARCHAR2( 16). TWITTER VARCHAR2( 8). SQL > Conversely the CREATE_VIEW_ON_PATH procedure can be utilized, which allows us to produce the view based on the frequency of
the components.
In the following example we just
consist of columns in the sight if the equivalent element remains in
all files saved in the JSON column. DROP VIEW json_documents_v2;.-- Create the view.
BEGIN. DBMS_JSON. create_view_on_path(. viewname= >' json_documents_v2',.
tablename= >' json_documents',. jcolname= >' information',.
path= >' $',. regularity= > 100);.
END;./.
DESC json_documents_v2. Call Null? Kind. ----------------------------------------- -------- ----------------------------. ID NOT NULL RAW( 16 ).
WORK VARCHAR2( 8 ).

ENERGETIC VARCHAR2 (8). CITY VARCHAR2 (8). STREET VARCHAR2 (16).>

NATION VARCHAR2 (2). POSTAL CODE VARCHAR2 (8). LAST_NAME VARCHAR2 (4). FIRST_NAME VARCHAR2 (8). DOB VARCHAR2 (16). EMAIL VARCHAR2 (32). PHONE VARCHAR2 (16). SQL >

Miscellaneous Some miscellaneous points to remember.This capability

  • just really makes sense if there is some consistency in the kinds of JSON documents held in a column. If each document were completely different, this isn't really the performance for you.Existing data overviews could be displayed making use of the
  • DBA _ JSON_DATAGUIDES views.Data overviews are preserved dynamically. If you include a brand-new row including additional
  • aspects, these will be shown in the information guide. If the data overview consists of stats, these statistics will not be upgraded up until the stats are collected again.When you develop virtual columns or views making use of the data guide, these are fixed based upon the information
  • guide interpretation at the time of creation. If the record framework modifications, for example adding new aspects, these will not be shown in the digital columns or sights based on the data overview unless they are recreated.You can index and gather data on digital columns developed using the data overview, which offers the optimizer with even more information.Remember, stats are only present in the information guide if you collect data on the JSON search index.For even more information see: Hope this aids. Regards Tim ... Back to the Top.