Oracle's approach to parsing complex JSON

Currently in the Oracle database (version before 11G) parsing JSON there is no direct use of the system method, the online Plsql script can only parse the structure of a single JSON string, for the complex structure of JSON string can not be resolved. As a result, even if the remote interface can be called in PL/SQL, the returned results will still need to be passed to the foreground JS or Java and other code for processing, not very convenient.

Analysis Ideas:

1. Write JSON strings in PL/SQL without having to declare JSON objects, simply stitching them into a properly formatted JSON string, so the transfer of JSON strings between database objects can be replaced by VARCHAR2 or CLOB.

2, the structure of complex JSON string node element values can basically be divided into two categories: ① is still a JSON string ②json array, so we only need to deal with these two types of JSON objects.

3. When working with JSON arrays in PL/SQL, because the JSON elements are unordered and repeatable, we need to index the array members and be able to get the length of the arrays before they can be recycled.

4. There are many open source jar packages in Java that can easily parse JSON strings, and the database supports importing Java class files, so can I import the Java code generated class into the data processing JSON?

Solution:

Method 1:loadjava import Java class for parsing

1. View the Java class file that the current database has imported

650) this.width=650; "Src=" http://images2015.cnblogs.com/blog/752160/201705/752160-20170504214047257-914676018. PNG "style=" margin:0px;padding:0px;border:0px; "/>

2. Execute Loadjava command to import the jar file required to process JSON, select Org.json instead of Fastjson or Jackson because the jar package is not externally dependent and satisfies functionality while requiring fewer imported class files.

--Import the JSON-related jar package loadjava-r-f-u scott/[email PROTECTED]:1521/ORCL json.jar--Delete the specified jar#dropjava-u Scott/[email prote from the database CTED]:1521/ORCL Json.jar

Here, we perform the import as follows:

650) this.width=650; "Src=" http://images2015.cnblogs.com/blog/752160/201705/752160-20170504220213882-1708350568. PNG "style=" margin:0px;padding:0px;border:0px; "/>

Details : If there is an external dependency on the imported jar package, the following exception will be reported, continue to import external dependencies will only make the jar package to be imported more and more, and ultimately not necessarily import success, not worth the loss, as follows:

650) this.width=650; "Src=" http://images2015.cnblogs.com/blog/752160/201705/752160-20170504220333320-1777059659. PNG "style=" margin:0px;padding:0px;border:0px; "/>

3. After importing Json.jar successfully, see the currently imported class file as follows

650) this.width=650; "Src=" http://images2015.cnblogs.com/blog/752160/201705/752160-20170504214203086-812400083. PNG "style=" margin:0px;padding:0px;border:0px; "/>

4. Execute the following script in the Database SQL window to create the Java source object

650) this.width=650; "src="/img/fz.gif "alt=" Copy Code "style=" Margin:0px;padding:0px;border:none; "/>

 1 create or replace and compile java source named  "Jsonutil " as 2  3 import org.json.JSONArray; 4 import  Org.json.jsonexception; 5 import org.json.jsonobject; 6 import java.lang.integer ;  7  8 public class jsonutil { 9   10  // Fetch JSON string single node value 11   public static string getvalue (string jsonstr,string  NodeName) {12     string nodevalue= ""; 13     try { 14       if (jsonstr==null| |! Jsonstr.startswith ("{") | |! Jsonstr.endswith ("}")) {15         nodevalue= "";16        }else{17         JSONObject  Obj =new jsonobject (JSONSTR); 18   &nbsP;     nodevalue = obj.getstring (NodeName);19        }20     } catch  (jsonexception e)  {21        nodevalue= "";22     }23      return nodevalue;24   }25   //JSON array lengths for Easy loop processing 26   public  static integer getarraylength (STRING JSONARRAYSTR) {27      Integer length=0;28     try {29       if (jsonarraystr==null| |! Jsonarraystr.startswith ("[") | |! Jsonarraystr.endswith ("]")) {30         length=0;31        }else{32         JSONArray  Jsonarr = new jsonarray (JSONARRAYSTR); 33     &nbsP;   length=jsonarr.length ();34       }35      } catch  (jsonexception e)  {36       length= 0;37     }38     return length;39   }40  41   //takes the index element of the JSON array 42   public static string  Getarrayvalue (string jsonstr,integer index) {43     string nodevalue= "" ; 44     try {45       if (jsonStr==null| |! Jsonstr.startswith ("[") | |! Jsonstr.endswith ("]")) {46         nodevalue= "";47        }else{48         JSONArray  Jsonarr = new jsonarray (JSONSTR);49          Nodevalue=jsoNarr.getstring (Index);50       }51     }  catch  (jsonexception e)  {52       nodevalue= "";53      }54     return nodevalue;55   }56 }

650) this.width=650; "src="/img/fz.gif "alt=" Copy Code "style=" Margin:0px;padding:0px;border:none; "/>

After the successful creation, you can see the corresponding class file:

650) this.width=650; "Src=" http://images2015.cnblogs.com/blog/752160/201705/752160-20170504214526132-953348688. PNG "style=" margin:0px;padding:0px;border:0px; "/>

5. Create a function (or procedure) using the class created in step 4, in order to distinguish it from the JSON processing method currently existing in the database, we create a package as follows:

650) this.width=650; "src="/img/fz.gif "alt=" Copy Code "style=" Margin:0px;padding:0px;border:none; "/>

 1 create or replace package jsonpkg 2 as 3 function  Getval (JSONSTR VARCHAR2,NODENAME VARCHAR2)  return varchar2; 4 function  Getarrval (Jsonarraystr varchar2,seqno number)  return varchar2; 5 function  Getarrlen (JSONARRAYSTR VARCHAR2)  return number; 6 end jsonpkg; 7 /  8 create or replace package body jsonpkg 9 as10 function   getval (JSONSTR VARCHAR2,NODENAME VARCHAR2)  return varchar211 as  language java name  ' Jsonutil.getvalue (java.lang.string,java.lang.string)  return  Java.lang.String '; 12    13 function getarrval (jsonarraystr varchar2,seqno  number)  return varchar214 as language java name  ' Jsonutil.getarrayvalue (Java.lang.string, java.lang. Integer)  return java.lang.string '; 15 16 function getarrlen (jsonarraystr varchar2 )  return number17 as language java name  ' Jsonutil.getarraylength ( java.lang.String)  return java.lang.integer '; 18 19 end jsonpkg;20 /

650) this.width=650; "src="/img/fz.gif "alt=" Copy Code "style=" Margin:0px;padding:0px;border:none; "/>

After a successful creation, you can view the package description and principals:

650) this.width=650; "Src=" http://images2015.cnblogs.com/blog/752160/201705/752160-20170504214922664-864046555. PNG "style=" margin:0px;padding:0px;border:0px; "/>

650) this.width=650; "Src=" http://images2015.cnblogs.com/blog/752160/201705/752160-20170504214932523-1804702594. PNG "style=" margin:0px;padding:0px;border:0px; "/>

6. Testing

① Simple JSON test

650) this.width=650; "Src=" http://images2015.cnblogs.com/blog/752160/201705/752160-20170504221401914-448808445. PNG "style=" margin:0px;padding:0px;border:0px; "/>

650) this.width=650; "Src=" http://images2015.cnblogs.com/blog/752160/201705/752160-20170504221440648-1359482983. PNG "style=" margin:0px;padding:0px;border:0px; "/>

650) this.width=650; "Src=" http://images2015.cnblogs.com/blog/752160/201705/752160-20170504221951679-2082169547. PNG "style=" margin:0px;padding:0px;border:0px; "/>

② Parsing Complex JSON

650) this.width=650; "Src=" http://images2015.cnblogs.com/blog/752160/201705/752160-20170504221053867-526077869. PNG "style=" margin:0px;padding:0px;border:0px; "/>

At this point, we can easily take the value of any node in the JSON string (if the node value is an array, you can calculate the length of the arrays before looping, or use the Getarrval method to take the values of the specified array elements).

Method 2: Install the Open source component Pl/json

: Https://github.com/pljson/pljson

650) this.width=650; "Src=" http://images2015.cnblogs.com/blog/752160/201705/752160-20170504221214289-2011843698. PNG "style=" margin:0px;padding:0px;border:0px; "/>

Advantages: Easy to install, the analytic method is more professional; disadvantage: The new Database object is more, the short-term learning cost is higher. The documentation is detailed and will not be mentioned here.

Oracle's approach to parsing complex JSON