Thursday, September 26, 2013

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)


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

  • 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

  • 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'
NULL ON ERROR) from t1;

select JSON_VALUE('$' 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)
ph_type varcahar2(10) path '$.type',
ph_num … path '$.number') jtab;

No comments:

Post a Comment