JSON - Doag

APEX_JSON ist nicht alles.
JSON-Funktionen der Oracle DB
Dr. Beda Hammerschmidt, Oracle Corporation
Carsten Czarski, ORACLE Deutschland B.V. Co KG
Follow me on twitter: @bch_t, @cczarski
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | Oracle Confidential – Internal/Restricted/Highly Restricted
JSON UND APEX 5.0: APEX_JSON
APEX_JSON ist nicht alles. JSON-Funktionen der Oracle DB
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
JSON und PL/SQL: APEX_JSON
• Mit APEX 5.0 eingeführt
– Teil der APEX-Installation
– Aber auch außerhalb von APEX oder ganz ohne APEX nutzbar
• Einfaches Erzeugen oder Parsen von JSON-Dateien
– Basiert auf Open Source
– Direktes Schreiben in den HTP Buffer oder in einen CLOB
– SQL-Unterstützung für JSON ist Teil der Datenbank ab 12.1.0.2
https://docs.oracle.com/cd/E59726_01/doc.50/e39149/apex_json.htm#AEAPI29635
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
JSON erzeugen mit PL/SQL
• PL/SQL-Package APEX_JSON
– Basiert auf Open Source PL/JSON
– Mit APEX 5.0 installiert
– Auch ohne APEX nutzbar
• JSON "programmatisch" erstellen
– Objekte mit OPEN|CLOSE_OBJECT
– Arrays mit OPEN|CLOSE_ARRAY
– Attribute mit WRITE
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
JSON erzeugen mit APEX_JSON
• Ausgabe in einen CLOB
– Generisch nutzbar
– Ausgabe erfolgt in temp. LOB
• Cache:
• Dur:
TRUE fast immer richtig
SESSION oder CALL
– Wichtig: LOB ggfs. freigeben
• Ausgabe in den HTP Buffer
PROCEDURE INITIALIZE_CLOB_OUTPUT
Argument Name
Typ
--------------------------- --------------P_DUR
BINARY_INTEGER
P_CACHE
BOOLEAN
P_INDENT
BINARY_INTEGER
PROCEDURE INITIALIZE_OUTPUT
Argument Name
--------------------------P_HTTP_HEADER
P_HTTP_CACHE
P_HTTP_CACHE_ETAG
P_INDENT
Typ
--------------BOOLEAN
BOOLEAN
VARCHAR2
BINARY_INTEGER
– Für Web-Anwendungen (APEX)
– Header und Browser-Caching setzen
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | Oracle Confidential – Internal/Restricted/Highly Restricted
5
JSON dynamisch erzeugen – per Cursor
SQL> select make_json(cursor(select * from emp)) as json_clob from dual;
JSON_CLOB
-------------------------------------------------------------------------------{"query":
[
{ "EMPNO":7369 ,
"ENAME":"SMITH" ,
"JOB":"CLERK" ,
"MGR":7902 ,
"HIREDATE":"1980-12-17T00:00:00Z" ,
"SAL":800 ,
"DEPTNO":20
} ,
{ : }
]
}
http://sql-plsql-de.blogspot.com/2015/05/apexjson-ohne-apex-json-erzeugen-mit.html
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
APEX_JSON: JSON parsen
• APEX_JSON.PARSE
– JSON-Parsing und Ablage in Memory
– Zugriff mit GET_XXX Aufrufen
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
APEX_JSON: JSON parsen
• APEX_JSON.PARSE
– JSON-Parsing und Ablage in Memory
– Zugriff mit GET_XXX Aufrufen
• Alternativ: Umweg über XMLTYPE
– Danach XML-Parsing
– Mehr Möglichkeiten
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
ENABLE FAST APPLICATION DEVELOPMENT
Native JSON Unterstützung in der Oracle Datenbank
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
9
Anforderungen agiler Anwendungsentwicklung
• Schema-Flexibilität
–
–
–
–
–
Endgültiges Schema zu Beginn unbekannt
Hinzufügen oder Wegfallen von Feldern
Kardinalitäten ändern sich
Daten mit altem Schema beibehalten,
"Data First - Schema Later or Never"
• Zugriff von verschiedenen Clients und Programmiersprachen
• Skalierbarkeit, Cloud Deployment, ….
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | Oracle Confidential – Internal/Restricted/Highly Restricted
10
Anforderungen agiler Anwendungsentwicklung
• Schema-Flexibilität
–
–
–
–
–
Endgültiges Schema zu Beginn unbekannt
Hinzufügen oder Wegfallen von Feldern
Kardinalitäten ändern sich
Daten mit altem Schema beibehalten,
"Data First - Schema Later or Never"
• Zugriff von verschiedenen Clients, Programmiersprachen
• Skalierbarkeit, Cloud Deployment, ….
"Wir brauchen eine NoSQL Datenbank"
Aber was ist mit …
Abfragesprache, Transaktionen, OLTP oder OLAP, Enterprise Features,
Product Maturity, Security, Betriebskonzept, etc …?
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | Oracle Confidential – Internal/Restricted/Highly Restricted
11
Schemaless Development mit Oracle12c und JSON
JSON in der Datenbank speichern
JSON
• Kein festes Schema mehr nötig
• JSON-Dokumente beschreiben sich selbst
Zugriffe per SQL oder per API
• SQL/JSON-Funktionen: JSON-Zugriffe mit SQL
• REST API: Zugriffe per API via HTTP – ohne SQL
HTTP(S) client
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
12
Wie genau geht das?
REST/Java Apis
Indexing
SQL/JSON Queries
Storage
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | Oracle Confidential – Internal/Restricted/Highly Restricted
13
Wie genau geht das?
• JSON als VARCHAR2, CLOB oder BLOB
– Kein eigener Datentyp – mit Absicht!
– Check Constraint IS JSON stellt JSON
Syntax sicher
REST/Java Apis
Indexing
CREATE TABLE jtab(
id
NUMBER,
jcol CLOB,
CONSTRAINT c1 CHECK(jcol IS JSON));
– Alle Clients können JSON nutzen
JDBC, OCI, PHP, node.js, APEX, …
SQL/JSON Queries
– Alle DB-Features mit JSON nutzbar
Import/Export, Replication, Golden Gate, …
Storage
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | Oracle Confidential – Internal/Restricted/Highly Restricted
14
Wie genau geht das?
SELECT
jtab.jsol.text,
jtab.jcol.user.screenName
FROM jsontab;
REST/Java Apis
• SQL/JSON Standard für Abfragen
– JSON_VALUE
Indexing
– JSON_QUERY
– JSON_EXISTS
SQL/JSON Queries
Storage
– JSON_TABLE
• Navigation in JSON ähnlich JavaScript
– $.address[0].city
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | Oracle Confidential – Internal/Restricted/Highly Restricted
15
SQL/JSON: Beispiel 1
{
"id": 578903819884585000,
"text": "RT @iAdvise_live: #countdown naar #Apexworld
@OGh_nl #iadvise_live @Yvke1983 geeft met #Robeco een
client case over #twitterbootstrap",
"geo": {
"type": "Point",
"coordinates": [
37.78217,
-122.40062
]
},
"lang": "nl",
"retweet_count": 2,
"created_at": "Fri Mar 20 13:00:00 +0000 2015",
"user": {
"statuses_count": 266,
"lang": "en",
"id": 1568150293,
"favourites_count": 62,
"name": "Jonathan van Vianen",
"screen_name": "jvanvianen78",
"followers_count": 187,
"friends_count": 493
}
}
SELECT JSON_VALUE(jcol, '$.id')
FROM jtab;
--------------------------------578903819884585000
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | Oracle Confidential – Internal/Restricted/Highly Restricted
16
SQL/JSON Beispiel 2
{
"id": 578903819884585000,
"text": "RT @iAdvise_live: #countdown naar #Apexworld
@OGh_nl #iadvise_live @Yvke1983 geeft met #Robeco een
client case over #twitterbootstrap",
"geo": {
"type": "Point",
"coordinates": [
37.78217,
-122.40062
]
},
"lang": "nl",
"retweet_count": 2,
"created_at": "Fri Mar 20 13:00:00 +0000 2015",
"user": {
"statuses_count": 266,
"lang": "en",
"id": 1568150293,
"favourites_count": 62,
"name": "Jonathan van Vianen",
"screen_name": "jvanvianen78",
"followers_count": 187,
"friends_count": 493
}
}
SELECT JSON_QUERY(jcol, '$.user'
FROM jtab;
---------------------------------------{
"statuses_count": 266,
"lang": "en",
"id": 1568150293,
"favourites_count": 62,
"name": "Jonathan van Vianen",
"screen_name": "jvanvianen78",
"followers_count": 187,
"friends_count": 493
}
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | Oracle Confidential – Internal/Restricted/Highly Restricted
18
SQL/JSON Beispiel 3
{
"id": 578903819884585000,
"text": "RT @iAdvise_live: #countdown naar #Apexworld
@OGh_nl #iadvise_live @Yvke1983 geeft met #Robeco een
client case over #twitterbootstrap",
"geo": {
"type": "Point",
"coordinates": [
37.78217,
-122.40062
]
},
"lang": "nl",
"retweet_count": 2,
"created_at": "Fri Mar 20 13:00:00 +0000 2015",
"user": {
"statuses_count": 266,
"lang": "en",
"id": 1568150293,
"favourites_count": 62,
"name": "Jonathan van Vianen",
"screen_name": "jvanvianen78",
"followers_count": 187,
"friends_count": 493
}
}
SELECT jt.*
FROM jtab,
JSON_TABLE(
jcol,
'$'
COLUMNS (
"ID"
"TEXT"
"LANG"
"USR "
"FOLLOWER"
"FRIENDS"
)
) jt;
NUMBER PATH '$.id'
PATH '$.text',
PATH '$.lang' DEFAULT ‘???' ON ERROR,
VARCHAR2(40) PATH '$.user.name',
NUMBER PATH '$.followers_count',
NUMBER PATH '$.friends_count'
ID
TEXT
LANG USR
FOLLOWER
FRIENDS
--------------------------------------------------578903
RT @i nl
Jonathan
187
493
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | Oracle Confidential – Internal/Restricted/Highly Restricted
19
SQL/JSON Beispiel 4
{
"id": 578903819884585000,
"text": "RT @iAdvise_live: #countdown naar #Apexworld
@OGh_nl #iadvise_live @Yvke1983 geeft met #Robeco een
client case over #twitterbootstrap",
"geo": {
"type": "Point",
"coordinates": [
37.78217,
-122.40062
]
},
"lang": "nl",
"retweet_count": 2,
"created_at": "Fri Mar 20 13:00:00 +0000 2015",
"user": {
"statuses_count": 266,
"lang": "en",
"id": 1568150293,
"favourites_count": 62,
"name": "Jonathan van Vianen",
"screen_name": "jvanvianen78",
"followers_count": 187,
"friends_count": 493
}
}
CREATE OR REPLACE VIEW TwitterV AS
SELECT jt.*
FROM jtab,
JSON_TABLE(
jcol,
'$'
COLUMNS (
"ID"
NUMBER PATH '$.id'
"TEXT"
PATH '$.text',
"LANG"
PATH '$.lang' DEFAULT ‘???' ON ERROR,
"USR“
VARCHAR2(40) PATH '$.user.name',
"FOLLOWER" NUMBER PATH '$.followers_count',
"FRIENDS" NUMBER PATH '$.friends_count'
)
) jt;
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | Oracle Confidential – Internal/Restricted/Highly Restricted
20
SQL/JSON Beispiel 4
{
"id": 578903819884585000,
"text": "RT @iAdvise_live: #countdown naar #Apexworld
@OGh_nl #iadvise_live @Yvke1983 geeft met #Robeco een
client case over #twitterbootstrap",
"geo": {
"type": "Point",
"coordinates": [
37.78217,
-122.40062
]
},
"lang": "nl",
"retweet_count": 2,
"created_at": "Fri Mar 20 13:00:00 +0000 2015",
"user": {
"statuses_count": 266,
"lang": "en",
"id": 1568150293,
"favourites_count": 62,
"name": "Jonathan van Vianen",
"screen_name": "jvanvianen78",
"followers_count": 187,
"friends_count": 493
}
}
CREATE OR REPLACE VIEW TwitterV AS
SELECT jt.*
FROM jtab,
JSON_TABLE(
jcol,
'$'
COLUMNS (
"ID"
NUMBER PATH '$.id'
:
"FRIENDS" NUMBER PATH '$.friends_count'
)
) jt;
-- Find most active German Tweeter
SELECT usr, count(1) "COUNT" FROM tweetv
WHERE lang = 'de'
GROUP BY usr ORDER BY "COUNT" DESC
FETCH FIRST 10 ROWS ONLY;
USR
COUNT
-----------------------------flederbine
28
cczarski
18
:
:
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | Oracle Confidential – Internal/Restricted/Highly Restricted
21
Wie genau geht das?
• B-Tree/Bitmap Index
REST/Java Apis
Indexing
SQL/JSON Queries
Storage
– CREATE INDEX id_idx ON jtab
(jtab.jcol.id);
– CREATE BITMAP INDEX lang_idx ON
jtab (jtab.jcol.lang);
– Felder müssen bekannt sein
• JSON Search Index
– Indiziert alle Felder als "Volltextindex"
– Ideal für unbekannte Daten
– CREATE INDEX po_search_idx
ON j_purchaseorder (po_document)
INDEXTYPE IS CTXSYS.CONTEXT PARAMETERS (
'section group CTXSYS.JSON_SECTION_GROUP
SYNC (ON COMMIT)'
);
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | Oracle Confidential – Internal/Restricted/Highly Restricted
22
Wie genau geht das?
• REST Abstraktion für Tabellen und SQL
• Collection  JSON-Sammlung
• REST Endpoints (GET, PUT, DELET, POST)
REST/Java Apis
• JSON-Query Language
{"lang": "nl"}
Indexing
SQL/JSON Queries
{"and":[
{"lang": "nl"},
{"retweet_count":{"$gt":3}}
]}
Storage
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | Oracle Confidential – Internal/Restricted/Highly Restricted
23
REST-Sicht auf eine JSON-Collection ( Tabelle)
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | Oracle Confidential – Internal/Restricted/Highly Restricted
24
Oracle REST Data Services (ORDS)
Oracle REST Data Services
URI
JSON
Transform
JSON Collection API
Auto Generated SQL
Rückgabe
JSON
JSON
Oracle12c
REST Client
Standard-REST-Aufrufe
GET, PUT, POST
HTTP, HTTPS
JSON in der Datenbank
SQL Queries
APEX Anwendungen
REST Data Services
- No need for developers to write SQL
- REST is stateless – no need to manage sessions or transactions
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
25
JSON in der Oracle-Datenbank: Zusammenfassung
Access JSON documents using RESTful API
PUT /my_database/my_schema/customers HTTP/1.0
Content-Type: application/json
Body:
{
"firstName": "John",
“lastName”: "Smith",
"age": 25,
"address": {
"streetAddress": "21 2nd Street",
"city": "New York",
"state": "NY",
"postalCode": "10021“,
"isBusiness" : false },
"phoneNumbers": [
{"type": "home", "number": "212 555-1234"},
{"type": "fax", "number": "646 555-4567"}
]
}
Query JSON using SQL
Oracle Database 12c
select
c.json_document.firstName,
c.json_document.lastName,
c.json_document.address.city
from customers c;
FIRSTNAME
----------John
:
LASTNAME
----------Smith
:
CITY
-------------New York
:
JSON
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
26
JSON UND ORACLE12C IN APEX NUTZEN
Das Zusammenspiel
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
27
Twitter Analyse mit SQL/JSON und APEX
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | Oracle Confidential – Internal/Restricted/Highly Restricted
28
Twitter Analyse mit SQL/JSON und APEX
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | Oracle Confidential – Internal/Restricted/Highly Restricted
29
Twitter Analyse mit SQL/JSON und APEX
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | Oracle Confidential – Internal/Restricted/Highly Restricted
30
Twitter Analyse mit SQL/JSON und APEX
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | Oracle Confidential – Internal/Restricted/Highly Restricted
31
#apexsummer15
• APEX 5.0 Summer School 2015
– 8 Webinare – Juli 2015 – Mittwochs und Freitags
• Alles über APEX 5.0
– Page Designer, Universal Theme, SQL und PL/SQL, Migration,
Interactive Reports, Plug-Ins, Sample Applications und mehr …
• Von den deutschsprachigen Community-Experten
– Peter Raganitsch, Niels de Bruijn, Oliver Lemm, Denes Kubicek,
Tobias Arnhold, Dietmar Aust, Christian Rokitta, Carsten Czarski,
Jürgen Schuster
tinyurl.com/orclapexsummerschool
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | Oracle Confidential – Internal/Restricted/Highly Restricted
32
http://blogs.oracle.com/apexcommunity_deutsch
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
[email protected]
http://blogs.oracle.com/jsondb
[email protected]
http://tinyurl.com/apexcommunity
http://sql-plsql-de.blogspot.com
Twitter: @bch_t
Twitter: @cczarski
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |