26. Mai 2015

APEX_JSON ohne APEX: JSON parsen mit SQL und PL/SQL

Dies ist das zweite von zwei Blog-Postings zum neuen Package APEX_JSON, mit dem man JSON-Dokumente auch ohne APEX, nur mit PL/SQL, verarbeiten kann. Nachdem das erste Blog-Posting sich mit dem Erzeugen von JSON beschäftigt hat, geht es heute um das Parsen und Auslesen von JSON mit APEX_JSON. Vorher noch wichtiger Hinweis.
Ab Oracle12c, genauer: Ab der Version 12.1.0.2 stellt die Datenbank native SQL-Funktionen zum Parsen von JSON bereit. Diese sind im C-Code des Datenbankkerns implementiert und dürften daher wesentlich effizienter sein als eine PL/SQL-Lösung.
Wenn Ihr könnt, solltet Ihr JSON immer mit den nativen SQL/JSON-Funktionen parsen; in 12c also grundsätzlich diese verwenden. APEX_JSON sollte zum Parsen von JSON nur in 11g-Datenbanken verwendet werden. Weiter unten werden wir noch einen Vergleich durchführen.
Los geht's - als Beispiel nehme ich JSON-Dateien, wie sie von der Twitter-API angeliefert werden; hier ein Ausschnitt.
{
  "id": 578903819884585000,
  "text": "RT @iAdvise_live: #countdown naar #Apexworld @OGh_nl ...
  "geo": {...}
  "lang": "nl",
  "retweet_count": 2,
  "created_at": "Fri Mar 20 13:00:00 +0000 2015",
  "user": {
      "statuses_count": 266,
      "lang": "en",
      "id": 1237892781789,
      "favourites_count": 62,        
      "name": "Jonathan ...",
      "screen_name": "jvanvianen78",
  }
  :
}
Wenn man nun eine Tabelle mit diesen JSON-Dateien hat, kann man diese mit APEX_JSON.PARSE parsen und die Inhalte dann mit GET_VARCHAR2, GET_NUMBER und ähnlichen Calls auslesen. Ein Beispiel könnte dann so aussehen:
Hinweis: Wenn Ihr mit der allerersten APEX-Version 5.0.1 und einer deutschsprachigen Session-Language arbeitet, setzt vorher ein ALTER SESSION SET NLS_NUMERIC_CHARACTERS='.,' ab.
declare
  l_parsed_json apex_json.t_values;
  l_value       varchar2(4000);
begin
  for i in (select tweet from apextweets) loop
    apex_json.parse(
      p_values => l_parsed_json,
      p_source => i.tweet
    );
    dbms_output.put_line(
      'User "'||
      apex_json.get_varchar2(
        p_path => 'user.screen_name',
        p_values => l_parsed_json
      ) ||
      '" tweeted at ' ||
      apex_json.get_varchar2(
        p_path => 'created_at',
        p_values => l_parsed_json
      ) 
    );
  end loop;
end;
/
Das Ergebnis wird in diesem Fall mit DBMS_OUTPUT auf die Konsole geschrieben; natürlich ist auch eine andere Verarbeitung denkbar.
User "jvanvianen78" tweeted at Fri Mar 20 13:00:00 +0000 2015
User "Yvke1983" tweeted at Fri Mar 20 12:58:08 +0000 2015
User "iAdvise_live" tweeted at Fri Mar 20 12:57:35 +0000 2015
User "reynde75" tweeted at Fri Mar 20 11:54:56 +0000 2015
User "Smart4Apex" tweeted at Fri Mar 20 11:42:38 +0000 2015
User "brost" tweeted at Fri Mar 20 11:06:20 +0000 2015
User "johnnyq72" tweeted at Fri Mar 20 10:45:55 +0000 2015
User "crokitta" tweeted at Fri Mar 20 10:44:31 +0000 2015
User "johnnyq72" tweeted at Fri Mar 20 10:27:25 +0000 2015
User "PretiusSoftware" tweeted at Fri Mar 20 09:29:02 +0000 2015
:
Allerdings haben wir hier die gleiche Situation wie im letzten Blog-Posting: Man muss jede Abfragesituation in PL/SQL prozedural kodieren; bei Abfragen wäre es ja interessanter, direkt mit SQL auf die JSON-Dokumente zugreifen zu können. APEX_JSON bietet uns hier einen Trick an: Die Funktion TO_XMLTYPE wandelt das JSON in einen XMLTYPE um ...
select apex_json.to_xmltype(tweet) from tweets_json where rownum = 1
/

APEX_JSON.TO_XMLTYPE(TWEET)
--------------------------------------------------------------------------------
<json>
  <retweeted_status>
    <text>#countdown naar #Apexworld @OGh_nl #iadvise_live @Yvke1983 ...
    <retweeted>false</retweeted>
    <truncated>false</truncated>
    <lang>nl</lang>
    <entities>
      <symbols/>
      <urls/>
      <hashtags>
        <row>
          <text>countdown</text>
          <indices>
            <row>0</row>
            <row>10</row>
:
... und darauf lässt sich nun sehr elegant mit den SQL/XML-Funktionen der Datenbank arbeiten.
select
  screen_name,
  created_at,
  lang 
from tweets_json, xmltable(
  '/json'
  passing apex_json.to_xmltype(tweet)
  columns
    screen_name varchar2(30)  path 'user/screen_name',
    created_at  varchar2(100) path 'created_at',
    lang        varchar2(5)   path 'lang'
)
where rownum <= 20
/  

SCREEN_NAME                    CREATED_AT                          LANG
------------------------------ ----------------------------------- ----
jvanvianen78                   Fri Mar 20 13:00:00 +0000 2015      nl
Yvke1983                       Fri Mar 20 12:58:08 +0000 2015      nl
iAdvise_live                   Fri Mar 20 12:57:35 +0000 2015      nl
reynde75                       Fri Mar 20 11:54:56 +0000 2015      und
Smart4Apex                     Fri Mar 20 11:42:38 +0000 2015      nl
brost                          Fri Mar 20 11:06:20 +0000 2015      en
johnnyq72                      Fri Mar 20 10:45:55 +0000 2015      nl
crokitta                       Fri Mar 20 10:44:31 +0000 2015      nl
johnnyq72                      Fri Mar 20 10:27:25 +0000 2015      en
PretiusSoftware                Fri Mar 20 09:29:02 +0000 2015      en
josepcoves                     Mon Mar 23 18:57:22 +0000 2015      en
Fr4ncis                        Mon Mar 23 18:48:29 +0000 2015      en
BIntsifuL                      Mon Mar 23 17:58:38 +0000 2015      en
pauljacobs123                  Mon Mar 23 17:45:28 +0000 2015      en
S3v3n11                        Mon Mar 23 17:20:52 +0000 2015      en
Das ist doch schon sehr bequem - und man kann zusätzliche Attribute einfach durch Anpassen der SQL-Abfrage dazunehmen. Auch in die Hierarchie kann man einsteigen, genau wie bei XML. Nun wollen wir eine etwas anspruchsvollere Aufgabe lösen: "Wieviele Tweets (ohne "Retweets") hat jeder User abgesetzt?"
select
  screen_name,
  count(*) anzahl_tweets
from tweets_json, xmltable(
  '/json'
  passing apex_json.to_xmltype(tweet)
  columns
    screen_name varchar2(30) path 'user/screen_name',
    created_at  varchar2(100) path 'created_at'
)
where not xmlexists(
  '/json/retweeted_status'
  passing apex_json.to_xmltype(tweet)
)
group by screen_name
order by 2 desc
/  

SCREEN_NAME                    ANZAHL_TWEETS
------------------------------ -------------
orclapexblogs                            186
joelkallman                               62
swesley_perth                             45
flederbine                                39
andre_blu                                 39
:

Elapsed: 00:01:17:55
Man kann mit den XML-Funktionen also eine ganze Menge erreichen. Würde man die gleiche Aufgabe mit PL/SQL Logik lösen, wäre dieser Code hier nötig (die absteigende Sortierung fehlt sogar noch).
declare
  type t_tabtype is table of number index by varchar2(200);

  l_parsed_json apex_json.t_values;
  l_user        varchar2(4000);
  l_tweetsbyusr t_tabtype;
  l_retweeted   boolean; 
begin
  for i in (select tweet from tweets_json) loop
    apex_json.parse(
      p_values => l_parsed_json,
      p_source => i.tweet
    );
    l_retweeted := apex_json.does_exist(
      p_path   => 'retweeted_status',
      p_values => l_parsed_json
    );
    if not l_retweeted then 
      l_user := apex_json.get_varchar2(
        p_path => 'user.screen_name',
        p_values => l_parsed_json
      );
      if l_tweetsbyusr.exists(l_user)  then
        l_tweetsbyusr(l_user) := l_tweetsbyusr(l_user) + 1;
      else 
        l_tweetsbyusr(l_user) := 1;
      end if;
    end if;
  end loop;
  l_user := l_tweetsbyusr.first;
  while l_user is not null loop
    dbms_output.put_line(l_user||':'||l_tweetsbyusr(l_user));
    l_user := l_tweetsbyusr.next(l_user);
  end loop;
end;
/

APEXORADEV:13
AliUK12:1
AljazMali:1
AntonScheffer:2
BIASCareers:2
BIntsifuL:7
BigBen212:1
BlueberryCoder:1
CPiasecki23:1
:

Elapsed: 00:01:06.36
Interessant ist nun aber die Ausführungszeit - für diese Auswertung auf 4.526 JSON-Dateien wurden mit dem XMLTYPE-Ansatz etwa 1 Minute und 17 Sekunden verbraucht, der prozedurale PL/SQL-Ansatz verbrauchte 1 Minute und 6 Sekunden. Halten wir da mal die nativen SQL/JSON-Funktionen, die ab 12.1.0.2 bereitstehen, dagegen.
select
  screen_name,
  count(*) anzahl_tweets
from tweets_json, json_table(
  tweet,
  '$'
  columns (
    screen_name varchar2(30)  path '$.user.screen_name',
    created_at  varchar2(100) path '$.created_at'
  )
)
where not json_exists(tweet, '$.retweeted_status')
group by screen_name
order by 2 desc
/  

SCREEN_NAME                    ANZAHL_TWEETS
------------------------------ -------------
orclapexblogs                            186
joelkallman                               62
swesley_perth                             45
flederbine                                39
andre_blu                                 39
:

Elapsed: 00:00:00:79
Es kommt das gleiche Ergebnis heraus - aber Sub-Second! 0.79 Sekunden, um genau zu sein. Wenn Ihr also auf einer 12c-Datenbank seid, verwendet auf jeden Fall die SQL/JSON-Funktionen zum JSON-Parsong - diese sind wesentlich effizienter. Mit APEX_JSON solltet Ihr nur dann parsen, wenn die nativen SQL/JSON-Funktionen nicht in Frage kommen - das wäre bspw. in einer Oracle11g-Datenbank der Fall.
Das mit APEX 5.0 eingeführte PL/SQL-Paket APEX_JSON eignet sich auch außerhalb von APEX sehr gut, um in PL/SQL mit JSON zu arbeiten. Neben dem Generieren von JSON bietet es auch Möglichkeiten an, JSON zu parsen. Besonders interessant ist die Möglichkeit, ein JSON mit TO_XMLTYPE in einen XMLTYPE zu wandeln, so dass man danach mit den SQL/XML-Funktionen darauf auch komplexere Abfragen durchführen kann.
Die mit Oracle12c eingeführten, nativen SQL/JSON-Funktionen bieten hierfür allerdings eine um Längen bessere Performance, so dass SQL-Funktionen wie JSON_VALUE, JSON_QUERY oder JSON_TABLE zum Parsen von JSON sicherlich zu bevorzugen wären.
This is the second of two blog postings about the new PL/SQL package APEX_JSON, which allows to work with JSON documents in a PL/SQL environment, even outside of APEX. While the first blog posting was about generating JSON with APEX_JSON, this posting will concentrate on the parsing side. We already have a bunch of JSON documents and want to work with their data. Before we begin, here is a very important note.
Beginning with Oracle12c, more specific: 12.1.0.2, the Oracle database provides native SQL/JSON fucntions zu parse and access JSON data. These are implemented within the database kernel and therefore much more efficient than a PL/SQL-based approach.
So, in an Oracle12c environment, always try to work with the nativer SQL/JSON functions instead of programming PL/SQL with APEX_JSON. JSON parsing with APEX_JSON is suited for pre-12c databases, where the native functions are not available. In this bloh posting, you'll also see a comparison between the two approaches.
Here we go: As an example, I'll take some JSON files which I obtained from the Twitter API. Here's a snippet.
{
  "id": 578903819884585000,
  "text": "RT @iAdvise_live: #countdown naar #Apexworld @OGh_nl ...
  "geo": {...}
  "lang": "nl",
  "retweet_count": 2,
  "created_at": "Fri Mar 20 13:00:00 +0000 2015",
  "user": {
      "statuses_count": 266,
      "lang": "en",
      "id": 1237892781789,
      "favourites_count": 62,        
      "name": "Jonathan ...",
      "screen_name": "jvanvianen78",
  }
  :
}
Assumed, we have a table containing instances of these JSON documents, we can start parsing these with APEX_JSON.PARSE. APEX_JSON will generate an in-Memory representation of the JSON data. We can then access the JSON data by calling methods like GET_VARCHAR2 or GET_NUMBER. A first procedure looks like this:
Important: If you are working with the very first APEX 5.0.1 release, and you are using a german or french NLS environment, execute the following call at the beginning of your session: ALTER SESSION SET NLS_NUMERIC_CHARACTERS='.,'
declare
  l_parsed_json apex_json.t_values;
  l_value       varchar2(4000);
begin
  for i in (select tweet from apextweets) loop
    apex_json.parse(
      p_values => l_parsed_json,
      p_source => i.tweet
    );
    dbms_output.put_line(
      'User "'||
      apex_json.get_varchar2(
        p_path => 'user.screen_name',
        p_values => l_parsed_json
      ) ||
      '" tweeted at ' ||
      apex_json.get_varchar2(
        p_path => 'created_at',
        p_values => l_parsed_json
      ) 
    );
  end loop;
end;
/
In this example, the processing results are being written onto the console with DBMS_OUTPUT. Of course, we could also process it otherwise or create a table function which returns structured output. So far, so good.
User "jvanvianen78" tweeted at Fri Mar 20 13:00:00 +0000 2015
User "Yvke1983" tweeted at Fri Mar 20 12:58:08 +0000 2015
User "iAdvise_live" tweeted at Fri Mar 20 12:57:35 +0000 2015
User "reynde75" tweeted at Fri Mar 20 11:54:56 +0000 2015
User "Smart4Apex" tweeted at Fri Mar 20 11:42:38 +0000 2015
User "brost" tweeted at Fri Mar 20 11:06:20 +0000 2015
User "johnnyq72" tweeted at Fri Mar 20 10:45:55 +0000 2015
User "crokitta" tweeted at Fri Mar 20 10:44:31 +0000 2015
User "johnnyq72" tweeted at Fri Mar 20 10:27:25 +0000 2015
User "PretiusSoftware" tweeted at Fri Mar 20 09:29:02 +0000 2015
:
But this is the same situation as described in the first blog posting. We will have to create procedural code for each individual query requirement. It would be much more convenient, to execute a SQL-like query direcly on the JSON data. And APEX_JSON offers a trick: The function TO_XMLTYPE converts JSON to an XMLTYPE instance ...
select apex_json.to_xmltype(tweet) from tweets_json where rownum = 1
/

APEX_JSON.TO_XMLTYPE(TWEET)
--------------------------------------------------------------------------------
<json>
  <retweeted_status>
    <text>#countdown naar #Apexworld @OGh_nl #iadvise_live @Yvke1983 ...
    <retweeted>false</retweeted>
    <truncated>false</truncated>
    <lang>nl</lang>
    <entities>
      <symbols/>
      <urls/>
      <hashtags>
        <row>
          <text>countdown</text>
          <indices>
            <row>0</row>
            <row>10</row>
:
... and as soon as we have XMLTYPE, we can start using the native SQL/XML functions which allow us to author SQL queries directly on XML data.
select
  screen_name,
  created_at,
  lang 
from tweets_json, xmltable(
  '/json'
  passing apex_json.to_xmltype(tweet)
  columns
    screen_name varchar2(30)  path 'user/screen_name',
    created_at  varchar2(100) path 'created_at',
    lang        varchar2(5)   path 'lang'
)
where rownum <= 20
/  

SCREEN_NAME                    CREATED_AT                          LANG
------------------------------ ----------------------------------- ----
jvanvianen78                   Fri Mar 20 13:00:00 +0000 2015      nl
Yvke1983                       Fri Mar 20 12:58:08 +0000 2015      nl
iAdvise_live                   Fri Mar 20 12:57:35 +0000 2015      nl
reynde75                       Fri Mar 20 11:54:56 +0000 2015      und
Smart4Apex                     Fri Mar 20 11:42:38 +0000 2015      nl
brost                          Fri Mar 20 11:06:20 +0000 2015      en
johnnyq72                      Fri Mar 20 10:45:55 +0000 2015      nl
crokitta                       Fri Mar 20 10:44:31 +0000 2015      nl
johnnyq72                      Fri Mar 20 10:27:25 +0000 2015      en
PretiusSoftware                Fri Mar 20 09:29:02 +0000 2015      en
josepcoves                     Mon Mar 23 18:57:22 +0000 2015      en
Fr4ncis                        Mon Mar 23 18:48:29 +0000 2015      en
BIntsifuL                      Mon Mar 23 17:58:38 +0000 2015      en
pauljacobs123                  Mon Mar 23 17:45:28 +0000 2015      en
S3v3n11                        Mon Mar 23 17:20:52 +0000 2015      en
This is much better - by changing the SQL query we can access any JSON attribute we want. By nesting XMLTABLE expressions, we can also access hierarchical structures; this has been an XML requirement for years. Using this powerful tool, we can take a more sophisticated challenge: "We want to know, how many tweets each user posted, but without retweets". The query is straightforward.
select
  screen_name,
  count(*) anzahl_tweets
from tweets_json, xmltable(
  '/json'
  passing apex_json.to_xmltype(tweet)
  columns
    screen_name varchar2(30) path 'user/screen_name',
    created_at  varchar2(100) path 'created_at'
)
where not xmlexists(
  '/json/retweeted_status'
  passing apex_json.to_xmltype(tweet)
)
group by screen_name
order by 2 desc
/  

SCREEN_NAME                    ANZAHL_TWEETS
------------------------------ -------------
orclapexblogs                            186
joelkallman                               62
swesley_perth                             45
flederbine                                39
andre_blu                                 39
:

Elapsed: 00:01:17:55
So, the SQL/XML functions take us a long way in parsing and working with JSON. Without those functions, we would have to author an awful lot of procedural code. In the following example, the descending sort is even missing.
declare
  type t_tabtype is table of number index by varchar2(200);

  l_parsed_json apex_json.t_values;
  l_user        varchar2(4000);
  l_tweetsbyusr t_tabtype;
  l_retweeted   boolean; 
begin
  for i in (select tweet from tweets_json) loop
    apex_json.parse(
      p_values => l_parsed_json,
      p_source => i.tweet
    );
    l_retweeted := apex_json.does_exist(
      p_path   => 'retweeted_status',
      p_values => l_parsed_json
    );
    if not l_retweeted then 
      l_user := apex_json.get_varchar2(
        p_path => 'user.screen_name',
        p_values => l_parsed_json
      );
      if l_tweetsbyusr.exists(l_user)  then
        l_tweetsbyusr(l_user) := l_tweetsbyusr(l_user) + 1;
      else 
        l_tweetsbyusr(l_user) := 1;
      end if;
    end if;
  end loop;
  l_user := l_tweetsbyusr.first;
  while l_user is not null loop
    dbms_output.put_line(l_user||':'||l_tweetsbyusr(l_user));
    l_user := l_tweetsbyusr.next(l_user);
  end loop;
end;
/

APEXORADEV:13
AliUK12:1
AljazMali:1
AntonScheffer:2
BIASCareers:2
BIntsifuL:7
BigBen212:1
BlueberryCoder:1
CPiasecki23:1
:

Elapsed: 00:01:06.36
Let's have a look on the exeution time: The table contains 4.526 JSON documents. For these documents, the SQL/XML approach took about 1 minute and 17 seconds, whereas the procedural approach took 1 minute and 6 seconds. We see, the XML overhead is rather small compared to the JSON parsing time. Let's compare this to the native SQL/JSON functions in Oracle12c.
select
  screen_name,
  count(*) anzahl_tweets
from tweets_json, json_table(
  tweet,
  '$'
  columns (
    screen_name varchar2(30)  path '$.user.screen_name',
    created_at  varchar2(100) path '$.created_at'
  )
)
where not json_exists(tweet, '$.retweeted_status')
group by screen_name
order by 2 desc
/  

SCREEN_NAME                    ANZAHL_TWEETS
------------------------------ -------------
orclapexblogs                            186
joelkallman                               62
swesley_perth                             45
flederbine                                39
andre_blu                                 39
:

Elapsed: 00:00:00:79
We get the same result, but in less than a second! 0.79 Seconds, to be precise. So, once more: If you are working in an Oracle12c environment, make sure to use the native SQL/JSON functions when parsing and accessing JSON data. APEX_JSON is suitable when the SQL/JSON functions are not available (like in Oracle 11.2) or not appropriate (you might want to use pure PL/SQL and avoid SQL in specific situations).
Summarized, the new APEX_JSON package introduced with APEX 5.0, is very usable outside of APEX as well. Beyond generating JSON it also allows o parse JSON documents and to access JSON data. After converting JSON to XMLTYPE, we can perform even flexible queries and access any JSON attribute we want - without authoring additional procedural code.
But the native SQL/JSON functions, which are available in Oracle12c and higher, offer much better query performance since these have been implemented directly into the database kernel. So, if possible, JSON parsing is done best with the JSON_VALUE, JSON_QUERY or JSON_TABLE SQL functions.

1 Kommentar:

chrisonoracle hat gesagt…

Eine kleine Optimierung ist bei dem Beispiel mit XMLEXISTS möglich. Hier werden die Dokumente 2 Mal von APEX_JSON geparst. Die Variante unten verwendet XQuery um diese Einschränkung zu implementieren:

select screen_name,
count(*) anzahl_tweets
from tweets_json, xmltable(
'for $json in /json
where fn:not(fn:exists($json/retweeted_status))
return $json'
passing apex_json.to_xmltype(tweet)
columns
screen_name varchar2(30) path 'user/screen_name',
created_at varchar2(100) path 'created_at'
)
group by screen_name
order by 2 desc

Wie Carsten bei einem Test festgestellt hat, verringert sich so die Ausführung geringfügig von 1:17 auf 1:10.

Beliebte Postings