OOW Notes - JSON data in Oracle Database

Wednesday 17:00 – Storing and Querying JSON Data in Oracle Database 12c


Understanding schema-less data management


growing trend: semi-structured data without string schema requirements
data first → schema later


growing trend adopting semistructured data tinto the database

• xml
  
• json (nosql - mongodb)
  

needs


structural and nonstructural coexist

• using single RDBMS to store both
  
• using one declarative set oriented SQL
  
• parallel replication security transaction in one infrastructure
  

data can be stored without schema

• store semi structural data in its native aggregated form without shredding
  

data can be queried without schema

• use sql extended with semi structured query languages
  

example

• SQL/XML embedding XQUERY/XPATH
  
• SQL/SJON embedding JSON path language
  

data can be indexed without schema

• indexing XML using structural XML index based on XMLTABLE expression
  
• indexing XML using XML full text index
  

road map of managing JSON data using oracle XML/JSON db

• JSON can be persisted without schema-less storing JSON data in varchar/raw/clob/blob columns
  
• JSON data can be queried without schema (SQL/JSON embedding)
  
• Function based indexes
  

overview of JSON


growing JSON adoption

• strong usage in web client programming
  
• rest interfaces (Facebook API)
  
• starts to influence server side coding
  
• data model for nosql stores like couchDB, mongodb
  
• very well suited for rapid application development
  

goal

• make rdbms JSON aware storage queries indexing
  
• leverage relational features sql indexed joins transactions
  
• enable schema-flexiable app development (data first schema later)
  

storing JSON data

• no new datatype use existing: varchar2, raw, blob, clob, bfile
  
• existing OCI, .NET and JDBC api works on JSON documents
  

check constraint to make ensure only valid JSON gets inserted


create table jtab (col1 varchar2(4000)) constraint jcons check (col1 is JSON);
insert into jtab values '{“name”:”oracle”}';


querying with JSON data with JSON/SQL


SQL/JSON emerging standard by Oracle+IBM
native JSON processing no rewrite to Xquery etc..


Oracle: streaming evaluation (no need to load all data into the memory )


path expression similar to javascript

• starts with symbol $
  
• . to native inside an object
  
• [] to navigate inside an array
  
• $.phoneNumber[1].type
  
• wildcards $.phonenumbers[*]?(type=”home”).number
  

JSON functionalities


use a path expression to select a value from the JSON data and return it as a SQL value


JSON_VALUE operator


select JSON_VALUE ('$.creditHistory[1].creditScore'
PASSING JCOL
RETURNING NUMBER
NULL ON ERROR) from t1;


select JSON_VALUE('$.geo.lat' passing col DEFAULT 'this is not a value' ON ERROR;


JSON_QUERY operator


select JSON_QUERY ('$.creditHistory[1]' PASSING col) from table_name;

• return JSON object '{“year”:2011, “creditScore”:650}'
  
• can return multiple values
  

JSON_EXISTS operator


select count(*) from table_name where JSON_EXISTS('$.creditScore' PASSING col);


JSON_TABLE rowsource

• project JSON data as relational table
  
• items inside array becomes rows
  
• JSON tables can be chained
  

select jtab.*
from table_name, JSON_table ('$.phoneNumbers[*]' passing col)
columns
ph_type varcahar2(10) path '$.type',
ph_num … path '$.number') jtab;