Json in 18c and 19c

JSON in Database 18c/19c JSON as it was meant to be Stew Ashton OUGN 2019 Can you read the following line? If not, please move closer. It's much better when you can read the code ;)

Who am I? • 1981-2015: – Developer / application architect – From Mainframe to client / server to Web • 2005-present: focus on Oracle DB development – Advocate of data-centric application architecture – Contribute to asktom & ODC/OTN SQL forum – Presented at OOW, UKOUG tech, DOAG, OUGN, ilOUG – Author of in-house CSV and XML handler 2

JSON is Everywhere • APEX_JSON • ORDS : REST-enabled SQL services • SQL Developer / sqlcl JSON output • GeoJSON • Storage of JSON in database tables – JSON Data Guide • SODA (Simple Oracle Document Access): NoSQL type development in the database • PL/SQL Object Types for JSON • SQL/JSON : query (JSONSQL) or generate (JSONSQL) 3

Why "JSON as it was meant to be"? • json.org : "JSON is a lightweight data-interchange format." – Not meant to store data – Deals in data structures, not "documents" – Interchange between programming languages • Many advantages over CSV or XML • So: use JSON to exchange data to or from SQL 👍 4

Data Exchange Headaches CSV Encoding ✗ Who knows? Decimal ✗ NLS settings Datetime formats ✗ NLS settings Field terminator ✗ Open bar Field enclosure ✗ Open bar Escaping ✗ field enclosure * 2 Record delimiter ✗ Open bar 5

Data Exchange Headaches CSV XML Encoding ✗ Who knows? ✗ Open bar Decimal ✗ NLS settings ✗ NLS settings Datetime formats ✗ NLS settings ISO 8601 ✗ except decimal! Field terminator ✗ Open bar ✓ </tag> Field enclosure ✗ Open bar ✓ <tag></tag> Escaping ✗ field enclosure * 2 ✓ Standard Record delimiter ✗ Open bar Root element? 6

Data Exchange Headaches CSV XML JSON Encoding ✗ Who knows? ✗ Open bar ✓ UTF-8 (AL32UTF8) Decimal ✗ NLS settings ✗ NLS settings ✓ Period, period! Datetime formats ✗ NLS settings ISO 8601 ✗ except decimal! ✓ ISO 8601 (Oracle) Field terminator ✗ Open bar ✓ </tag> ✓ comma Field enclosure ✗ Open bar ✓ <tag></tag> ✓ Double quotes Escaping ✗ field enclosure * 2 ✓ Standard ✓ Standard Record delimiter ✗ Open bar Root element? ✓ {} or [] 7 JSON alone is independent of database parameters

JSON is a data structure • Object • Array 8

JSON is a data structure • Object: unordered collection of name/value pairs – {"Empno":7369,"Ename":"SMITH"} {"Ename":"SMITH","Empno":7369} – XML: <Empno>7369</Empno><Ename>SMITH</Ename> – Path expression: '$.Ename' , '$.Empno' • Array: ordered list of values – [7369,"SMITH","CLERK"] – CSV: 7369,"SMITH","CLERK" – Path expression: '$[0]' , '$[1]' , '$[2]' 9 JSON_EQUAL

JSON is a data structure • Object: unordered collection of name/value pairs – {"Empno":7369,"Ename":"SMITH"} {"Ename":"SMITH","Empno":7369} – XML: <Empno>7369</Empno><Ename>SMITH</Ename> – Path expression: '$.Ename' , '$.Empno' • Array: ordered list of values – [7369,"SMITH","CLERK"] – CSV: 7369,"SMITH","CLERK" – Path expression: '$[0]' , '$[1]' , '$[2]' • "value" can be another object or array 10

JSON Data in SQL Statements • It’s not a special data type – VARCHAR2 – CLOB – BLOB in AL32UTF8 (18c) • "Oracle recommends that you use AL32UTF8 as the database character set if at all possible." • When does Oracle consider data to be JSON? '{ }' could be an empty JSON object or a JSON string value "{ }" – Stored data: column with IS JSON constraint – Output: Result of JSON generation function – Input: FORMAT JSON in JSON generation function – TREAT(… AS JSON) (18c) 11

SQL <> JSON Transformation Direction Source Function Returns Prefer for Generate 1 row JSON_OBJECT object OLTP JSON_ARRAY array ETL 12

SQL <> JSON Transformation Direction Source Function Returns Prefer for Generate 1 row JSON_OBJECT object OLTP JSON_ARRAY array ETL N rows JSON_OBJECTAGG object OLTP JSON_ARRAYAGG array ETL 13

SQL <> JSON Transformation Direction Source Function Returns Prefer for Generate 1 row JSON_OBJECT object OLTP JSON_ARRAY array ETL N rows JSON_OBJECTAGG object OLTP JSON_ARRAYAGG array ETL Query JSON JSON_QUERY 1 JSON PRETTY (1) JSON_VALUE 1 Scalar (2) JSON_EXISTS true/false 14 (1) 19c: JSON_SERIALIZE(2) 19c: 1 SQL object or collection!

SQL <> JSON Transformation Direction Source Function Returns Prefer for Generate 1 row JSON_OBJECT object OLTP JSON_ARRAY array ETL N rows JSON_OBJECTAGG object OLTP JSON_ARRAYAGG array ETL Query JSON JSON_QUERY 1 JSON PRETTY JSON_VALUE 1 Scalar JSON_EXISTS true/false JSON_TABLE Multiple ETL / OLTP 15

SQL Data Type to JSON 16 Input data types JSON result Example VARCHAR2 string "varchar2" NVARCHAR2 "nvarchar2" CLOB "clob"

SQL Data Type to JSON 17 Input data types JSON result Example VARCHAR2 string "varchar2" NVARCHAR2 "nvarchar2" CLOB "clob" RAW hex string "1F2F3F4F" BLOB "1F2F3F4F5F6F7F8F"

SQL Data Type to JSON 18 Input data types JSON result Example VARCHAR2 string "varchar2" NVARCHAR2 "nvarchar2" CLOB "clob" RAW hex string "1F2F3F4F" BLOB "1F2F3F4F5F6F7F8F" NUMBER number 6 BINARY_DOUBLE 7 BINARY_FLOAT 8

SQL Data Type to JSON 19 Input data types JSON result Example VARCHAR2 string "varchar2" NVARCHAR2 "nvarchar2" CLOB "clob" RAW hex string "1F2F3F4F" BLOB "1F2F3F4F5F6F7F8F" NUMBER number 6 BINARY_DOUBLE 7 BINARY_FLOAT 8 DATE ISO 8601 string "2018-10-08T20:00:07" TIMESTAMP "2018-10-08T20:00:07.795339" TIMESTAMP WITH LOCAL TZ "2018-10-08T18:00:07.795339Z" TIMESTAMP WITH TZ "2018-10-08T18:00:07.795339Z" INTERVAL DAY TO SECOND "P13D" INTERVAL YEAR TO MONTH "P1Y2M" Converted to UTC

create table t ( C_VARCHAR2 VARCHAR2(16), C_NVARCHAR2 NVARCHAR2(16), C_CLOB CLOB, C_RAW RAW(16), C_BLOB BLOB, C_NUMBER NUMBER(3,1), C_BINARY_DOUBLE BINARY_DOUBLE, C_BINARY_FLOAT BINARY_FLOAT, C_DATE DATE, C_TIMESTAMP TIMESTAMP, C_TIMESTAMP_WITH_LOCAL_TIME_ZO TIMESTAMP WITH LOCAL TIME ZONE, C_TIMESTAMP_WITH_TIME_ZONE TIMESTAMP WITH TIME ZONE, C_INTERVAL_DAY_TO_SECOND INTERVAL DAY TO SECOND, C_INTERVAL_YEAR_TO_MONTH INTERVAL YEAR TO MONTH ); 20 Table

select C_VARCHAR2||','|| C_NVARCHAR2||',’|| C_CLOB||','|| rawtohex(C_RAW)||',’|| <blob is complicated>||','|| to_char(C_NUMBER, 'TM', 'nls_numeric_characters=''. ''')||','|| to_char(C_BINARY_DOUBLE, 'TM', 'nls_numeric_characters=''. ''')||','|| to_char(C_BINARY_FLOAT, 'TM', 'nls_numeric_characters=''. ''')||','|| to_char(C_DATE, 'yyyy-mm-dd"T"hh24:mi:ss')||','|| to_char(C_TIMESTAMP, 'yyyy-mm-dd"T"hh24:mi:ssxff')||','|| to_char(C_TIMESTAMP_WITH_LOCAL_TIME_ZO, 'yyyy-mm-dd"T"hh24:mi:ssxff')||','|| to_char(C_TIMESTAMP_WITH_TIME_ZONE, 'yyyy-mm- dd"T"hh24:mi:ssxffTZH:TZM')||','|| C_INTERVAL_DAY_TO_SECOND||','|| C_INTERVAL_YEAR_TO_MONTH csv_text from T; 21 CSV

select xmlforest( C_VARCHAR2, C_NVARCHAR2, C_CLOB, rawtohex(C_RAW), <blob is complicated>, to_char(C_NUMBER, 'TM', 'nls_numeric_characters=''. ''') as C_NUMBER, to_char(C_BINARY_DOUBLE, 'TM', 'nls_numeric_characters=''. ''') as C_BINARY_DOUBLE, to_char(C_BINARY_FLOAT, 'TM', 'nls_numeric_characters=''. ''') as C_BINARY_FLOAT, to_char(C_DATE, 'yyyy-mm-dd"T"hh24:mi:ss') as C_DATE, to_char(C_TIMESTAMP, 'yyyy-mm-dd"T"hh24:mi:ssxff', 'nls_numeric_characters=''. ''') as C_TIMESTAMP, to_char(to_timestamp_tz(C_TIMESTAMP_WITH_LOCAL_TIME_ZO), 'yyyy-mm- dd"T"hh24:mi:ssxffTZH:TZM', 'nls_numeric_characters=''. ''') as C_TIMESTAMP_WITH_LOCAL_TIME_ZO, to_char(C_TIMESTAMP_WITH_TIME_ZONE, 'yyyy-mm-dd"T"hh24:mi:ssxffTZH:TZM', 'nls_numeric_characters=''. ''') as C_TIMESTAMP_WITH_TIME_ZONE, C_INTERVAL_DAY_TO_SECOND, C_INTERVAL_YEAR_TO_MONTH ) T_XML from T; 22 XML

select json_array( C_VARCHAR2, C_NVARCHAR2, C_CLOB, C_RAW, C_BLOB, C_NUMBER, C_BINARY_DOUBLE, C_BINARY_FLOAT, C_DATE, C_TIMESTAMP, C_TIMESTAMP_WITH_LOCAL_TIME_ZO, C_TIMESTAMP_WITH_TIME_ZONE, C_INTERVAL_DAY_TO_SECOND, C_INTERVAL_YEAR_TO_MONTH ) sql_to_json from T; [ "varchar2", "nvarchar2", "clob", "1F2F3F4F", "1F2F3F4F5F6F7F8F", 6, 7, 8, "2018-10-08T20:00:07", "2018-10-08T20:00:07.795339", "2018-10-08T18:00:07.795339Z", "2018-10-08T18:00:07.795339Z", "P13D", "P1Y2M" ] 23 What if C_BINARY_FLOAT is null?

select json_array( C_VARCHAR2, C_NVARCHAR2, C_CLOB, C_RAW, C_BLOB, C_NUMBER, C_BINARY_DOUBLE, C_BINARY_FLOAT, C_DATE, C_TIMESTAMP, C_TIMESTAMP_WITH_LOCAL_TIME_ZO, C_TIMESTAMP_WITH_TIME_ZONE, C_INTERVAL_DAY_TO_SECOND, C_INTERVAL_YEAR_TO_MONTH ) sql_to_json from T; [ "varchar2", "nvarchar2", "clob", "1F2F3F4F", "1F2F3F4F5F6F7F8F", 6, 7, "2018-10-08T20:00:07", "2018-10-08T20:00:07.795339", "2018-10-08T18:00:07.795339Z", "2018-10-08T18:00:07.795339Z", "P13D", "P1Y2M" ] 24 Why?

select json_array( C_VARCHAR2, C_NVARCHAR2, C_CLOB, C_RAW, C_BLOB, C_NUMBER, C_BINARY_DOUBLE, C_BINARY_FLOAT, C_DATE, C_TIMESTAMP, C_TIMESTAMP_WITH_LOCAL_TIME_ZO, C_TIMESTAMP_WITH_TIME_ZONE, C_INTERVAL_DAY_TO_SECOND, C_INTERVAL_YEAR_TO_MONTH ABSENT ON NULL) sql_to_json from T; [ "varchar2", "nvarchar2", "clob", "1F2F3F4F", "1F2F3F4F5F6F7F8F", 6, 7, "2018-10-08T20:00:07", "2018-10-08T20:00:07.795339", "2018-10-08T18:00:07.795339Z", "2018-10-08T18:00:07.795339Z", "P13D", "P1Y2M" ] 25 ABSENT ON NULL is the default

select json_array( C_VARCHAR2, C_NVARCHAR2, C_CLOB, C_RAW, C_BLOB, C_NUMBER, C_BINARY_DOUBLE, C_BINARY_FLOAT, C_DATE, C_TIMESTAMP, C_TIMESTAMP_WITH_LOCAL_TIME_ZO, C_TIMESTAMP_WITH_TIME_ZONE, C_INTERVAL_DAY_TO_SECOND, C_INTERVAL_YEAR_TO_MONTH NULL ON NULL) sql_to_json from T; [ "varchar2", "nvarchar2", "clob", "1F2F3F4F", "1F2F3F4F5F6F7F8F", 6, 7, null, "2018-10-08T20:00:07", "2018-10-08T20:00:07.795339", "2018-10-08T18:00:07.795339Z", "2018-10-08T18:00:07.795339Z", "P13D", "P1Y2M" ] 26 Always add this for arrays.

select json_object(*) from t; {"C_VARCHAR2" : "varchar2", "C_NVARCHAR2" : "nvarchar2", "C_CLOB" : "clob", "C_RAW" : "44444444444444444444444444444444", "C_BLOB" : "55555555555555555555555555555555", "C_NUMBER" : 6, "C_BINARY_DOUBLE" : 7, "C_BINARY_FLOAT" : null, "C_DATE" : "2019-03-16T01:12:24", "C_TIMESTAMP" : "2019-03-16T01:12:24.854455", "C_TIMESTAMP_WITH_LOCAL_TIME_ZO" : "2019-03-16T08:12:24.854455Z", "C_TIMESTAMP_WITH_TIME_ZONE" : "2019-03-16T08:12:24.854455Z", "C_INTERVAL_DAY_TO_SECOND" : "P13D", "C_INTERVAL_YEAR_TO_MONTH" : "P1Y2M"} 27 19c

JSON to SQL Data Type 28 Original SQL data type JSON Target SQL data type VARCHAR2 string VARCHAR2 NVARCHAR2 CLOB CLOB RAW (hex) string BLOB NUMBER number NUMBER BINARY_DOUBLE BINARY_FLOAT DATE ISO 8601 string DATE TIMESTAMP TIMESTAMP TIMESTAMP WITH LOCAL TZ TIMESTAMP WITH TZ TIMESTAMP WITH TZ INTERVAL DAY TO SECOND INTERVAL YEAR TO MONTH

select jt.* from json_data, json_table(sql_to_json, '$' columns ( "Varchar2" VARCHAR2(16 BYTE) path '$[0]', "Clob" CLOB path '$[2]', "N" NUMBER path '$[5]', "Date" DATE path '$[8]', "Timestamp" TIMESTAMP path '$[9]', "Timestamp with time zone" TIMESTAMP WITH TIME ZONE path '$[11]' )) jt; 29 Varchar2 ClobN Date Timestamp Timestamp with time zone varchar2 clob 6 2018-10-08 00:00 2018-10-08 23:26:22,67459 2018-10-08 21:26:22,67459 GMT Time portion lost Original time zone lost <= if 18c, NLS_NUMERIC_CHARACTERS='.,’ Round trip

JSON to SQL Data Type Input data types JSON result Return type CONVERT? VARCHAR2 string VARCHAR2 NVARCHAR2 VARCHAR2 to_nchar() CLOB CLOB RAW hex string VARCHAR2(nn CHAR) hextoraw() BLOB CLOB must write function NUMBER number NUMBER BINARY_DOUBLE NUMBER to_binary_double() BINARY_FLOAT NUMBER to_binary_float() DATE ISO 8601 string TIMESTAMP cast(<value> as date) TIMESTAMP TIMESTAMP TIMESTAMP WITH LOCAL TZ TIMESTAMP WITH TZ cast(<value> as timestamp with local TZ) TIMESTAMP WITH TZ TIMESTAMP WITH TZ INTERVAL DAY TO SECOND VARCHAR2(16 BYTE) to_dsinterval() INTERVAL YEAR TO MONTH VARCHAR2(16 BYTE) to_yminterval() 30

Hex_to_blob function create or replace FUNCTION hex_to_blob (hex CLOB) RETURN BLOB AUTHID CURRENT_USER IS b BLOB := NULL; s VARCHAR2(32766 BYTE) := NULL; l NUMBER := 32766; BEGIN if hex is not null then dbms_lob.createtemporary(b, FALSE); FOR i IN 0 .. floor(LENGTH(hex) / l) LOOP dbms_lob.read(hex, l, i * l + 1, s); dbms_lob.append(b, hextoraw(s)); END LOOP; end if; RETURN b; END hex_to_blob; 31

So Far so Good • Oracle can generate JSON from all scalar data types (within reason). • Oracle (with our help) can extract all scalar data types from JSON. • 19c: support for SQL objects and collections • Data goes from SQL to JSON to SQL without loss – But for time zones (and careful with time part of date) • Note: except for "proof of concept", do not use JSON to exchange data between Oracle DBs. 32

ETL: CLOB & array of arrays Extract select json_arrayagg( json_array(<columns>) returning clob ) from <table>; (write clob to file) Load select <converted columns> from json_table( bfilename('<dir>', '<filename>'), '$[*]' columns ( a varchar2(99) path '$[0]', b varchar2(99) path '$[1]', ... )); 33

ETL: external table, 1 array per row Extract Select json_array(<columns>) from <table>; (write to file) Load select <converted columns> from <external table>, json_table(json_data, '$' columns ( a varchar2(99) path '$[0]', b varchar2(99) path '$[1]', ... )); 34 Not "standard" but much faster at volume.

Just Join? with m as ( SELECT employee_id manager_id, last_name FROM employees WHERE employee_id = 103 ) select MANAGER_ID, m.LAST_NAME, d.DEPARTMENT_ID, d.DEPARTMENT_NAME, e.EMPLOYEE_ID, e.FIRST_NAME from m join departments d using(manager_id) join employees e using(manager_id); 36

37 MANAGER_ID LAST_NAME DEPARTMENT_ID DEPARTMENT_NAME EMPLOYEE_ID FIRST_NAME 103Hunold 10Administration 104Bruce 103Hunold 20Marketing 104Bruce 103Hunold 30Purchasing 104Bruce 103Hunold 60IT 104Bruce 103Hunold 10Administration 105David 103Hunold 20Marketing 105David 103Hunold 30Purchasing 105David 103Hunold 60IT 105David 103Hunold 10Administration 106Valli 103Hunold 20Marketing 106Valli 103Hunold 30Purchasing 106Valli 103Hunold 60IT 106Valli 103Hunold 10Administration 107Diana 103Hunold 20Marketing 107Diana 103Hunold 30Purchasing 107Diana 103Hunold 60IT 107Diana

with m as ( SELECT employee_id manager_id, last_name FROM employees WHERE employee_id = 103 ) SELECT json_object( 'manager_id' value manager_id, 'last_name' value last_name, 'departments' value ( ? ), 'employees' value ( ? ) ) ) json_data FROM m; 38 SELECT JSON_ARRAYAGG( JSON_OBJECT( 'department_id' value department_id, 'department_name' value department_name ) ) FROM departments WHERE manager_id = m.manager_id SELECT JSON_ARRAYAGG( JSON_OBJECT( 'employee_id' value employee_id, 'first_name' value first_name ) ) FROM employees WHERE manager_id = m.manager_id

{ "manager_id" : 103, "last_name" : "Hunold", "departments" : [ { "department_id" : 10, "department_name" : "Administration" }, ... { "department_id" : 60, "department_name" : "IT" } ], "employees" : [ { "employee_id" : 104, "first_name" : "Bruce" }, ... { "employee_id" : 107, "first_name" : "Diana" } ] } 39

Just the Object select j.* from mgr_json, json_table(json_data, '$' columns manager_id, last_name ) j; 40 MANAGER_ID LAST_NAME 103 Hunold

Nested path: parent / child select j.* from mgr_json, json_table(json_data, '$' columns manager_id, last_name, nested path '$.departments[*]' columns ( department_id, department_name ) ) j; 41 MANAGER_ID LAST_NAME DEPARTMENT_ID DEPARTMENT_NAME 103 Hunold 10 Administration 103 Hunold 20 Marketing 103 Hunold 30 Purchasing 103 Hunold 60 IT

Nested path: Ordinality select j.* from mgr_json, json_table(json_data, '$' columns manager_id, last_name, nested path '$.departments[*]' columns ( d_ord for ordinality, department_id, department_name ) ) j; 42 MANAGER_ID LAST_NAME D_ORD DEPARTMENT_ID DEPARTMENT_NAME 103 Hunold 110 Administration 103 Hunold 220 Marketing 103 Hunold 330 Purchasing 103 Hunold 460 IT

Nested paths: Siblings select j.* from mgr_json, json_table(json_data, '$' columns manager_id number, last_name, nested path '$.departments[*]' columns ( d_ord for ordinality, department_id number, department_name ), nested path '$.employees[*]' columns ( e_ord for ordinality, employee_id number, first_name ) ABSENT ON NULL) j; 43

Nested paths: Siblings MANAGER_ID LAST_NAME D_ORD DEPT_ID DEPARTMENT_NAME E_ORD EMP_ID FIRST_NAME 103Hunold 1 10Administration 103Hunold 2 20Marketing 103Hunold 3 30Purchasing 103Hunold 4 60IT 103Hunold 1 104Bruce 103Hunold 2 105David 103Hunold 3 106Valli 103Hunold 4 107Diana 44

Advantages for OLTP • Out with ORM, in with database development – Provide application-specific APIs that add value – Use SQL*Net or REST – Data access layer and DB are less tightly coupled – Generate / interpret complex hierarchical data • Read consistency for all data – One SELECT per API 45

JSON in Database 18c/19c JSON as it was meant to be Stew Ashton OUGN 2019 Blog: stewashton.wordpress.com Twitter: @stewashton

If we had time… • ETL: multi-table insert with nested JSON • OLTP modifications – JSON_MERGEPATCH (19c) • String manipulation – Splitting strings stewashton.wordpress.com/splitting-strings-a-new-champion/ – Listagg() for CLOBs 47

Json in 18c and 19c

More Related Content

What's hot

Similar to Json in 18c and 19c

More from stewashton

Recently uploaded

Json in 18c and 19c