Shulou(Shulou.com)06/01 Report--

Background of the question:

At present, there is no system method that can be directly used to parse json in Oracle database (version before 11G). Most of the PLSQL scripts spread on the Internet can only parse json strings with single structure, but cannot parse json strings with complex structure. As a result, even though the remote interface can be called in PL/SQL, the returned result still needs to be passed to other codes such as js or java for processing, which is not very convenient.

Analysis ideas:

1. When writing json strings in PL/SQL, you don't need to declare json objects, you just need to splice them directly into a properly formatted json string, so the transfer of json strings between database objects can be completely replaced by varchar2 or clob.

2. The node element values of json strings with complex structure 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 dealing with the json array in PL/SQL, because the json element is unordered and repeatable, we need to index the array members and get the length of the array before we can loop it.

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 the class generated by the compilation of java code be imported into data processing json?

Solution:

Method 1:loadjava is imported into java class for parsing

1. View the java class files imported from the current database

2. Execute the loadjava command to import the jar file needed to deal with json. The reason for choosing org.json instead of fastjson or jackson here is that the jar package has no external dependency and meets the function, while fewer class files are needed to import.

-- Import json-related jar package loadjava-r-f-u scott/tiger@xxx.xxx.xxx.xxx:1521/orcl json.jar-- into the database to delete the specified jar#dropjava-u scott/tiger@xxx.xxx.xxx.xxx:1521/orcl json.jar

Here, we perform the import, as follows:

Details: if the imported jar package has an external dependency, the following exception will be reported. Continuing to import external dependencies will only make more and more jar packages to be imported. In the end, the import may not be successful, and the loss outweighs the gain, as shown below:

3. After successfully importing json.jar, check the class files that have been imported again as follows

4. Execute the following script in the database SQL window to create the java source object

Create or replace and compile java source named "JsonUtil" asimport org.json.JSONArray;import org.json.JSONException;import org.json.JSONObject;import java.lang.Integer;public class JsonUtil {/ / fetch single node value public static String getValue (String jsonStr,String nodeName) {String nodeValue= "; try {if (jsonStr==null | |! jsonStr.startsWith (" {") | |! jsonStr.endsWith ("} ")) {nodeValue=";} else {JSONObject obj = new JSONObject (jsonStr); nodeValue= obj.getString (nodeName) }} catch (JSONException e) {nodeValue= ";} return nodeValue;} / take the json array length to facilitate cyclic processing of public static Integer getArrayLength (String jsonArrayStr) {Integer length=0; try {if (jsonArrayStr==null | |! jsonArrayStr.startsWith (" [") | |! jsonArrayStr.endsWith ("] ")) {length=0;} else {JSONArray jsonArr = new JSONArray (jsonArrayStr); length=jsonArr.length ();} catch (JSONException e) {length=0;} return length } / / take public static String getArrayValue (String jsonStr,Integer index) {String nodeValue= "; try {if (jsonStr==null | |! jsonStr.startsWith (" [") | |! jsonStr.endsWith ("] ")) {nodeValue=";} else {JSONArray jsonArr = new JSONArray (jsonStr); nodeValue=jsonArr.getString (index);}} catch (JSONException e) {nodeValue= ";} return nodeValue;}}

After the creation is successful, you can see the corresponding class file by querying again:

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

Create or replace package jsonpkgasfunction getval (jsonstr varchar2,nodename varchar2) return varchar2;function getarrval (jsonArrayStr varchar2,seqNo number) return varchar2;function getarrlen (jsonArrayStr varchar2) return number;end jsonpkg;/create or replace package body jsonpkgasfunction getval (jsonstr varchar2,nodename varchar2) return varchar2as language java name 'JsonUtil.getValue (java.lang.String,java.lang.String) return java.lang.String';function getarrval (jsonArrayStr varchar2,seqNo number) return varchar2as language java name' JsonUtil.getArrayValue (java.lang.String, java.lang.Integer) return java.lang.String' Function getarrlen (jsonArrayStr varchar2) return numberas language java name 'JsonUtil.getArrayLength (java.lang.String) return java.lang.Integer';end jsonpkg;/

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

6. Testing

① simple json Test

② parsing complex json

At this point, we can easily get the value of any node in the json string (if the node value is an array, we can calculate the length of the array before loop loop processing, or directly use the getarrval method to get the value of the specified array elements).

Method 2: install the open source component PL/JSON

Download address: https://github.com/pljson/pljson

Advantages: convenient installation, professional analysis method; disadvantages: more new database objects, high cost of short-term learning. The documentation is so detailed that I will not repeat it here. If you have any questions, please leave me a message and the editor will reply you in time. Thank you very much for your support to the website!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.