5 Replies Latest reply on Feb 20, 2018 1:58 PM by Laury

    Extract field values from JSON string using PL/SQL

    Laury

      Hi,

       

      I am trying to extract the fields and the values of these fields from a JSON string with a PL/SQL piece of code:

       

      set serveroutput on

      declare

          l_json_text varchar2(32767);

          l_count     pls_integer;

          l_members   wwv_flow_t_varchar2;

          l_paths     apex_t_varchar2;

          l_exists    boolean;

      begin

          ---

          l_json_text := '{

              "items":[

                  {"empno":7876,"ename":"ADAMS","job":"CLERK","mgr":7788,"hiredate":"1983-01-11T23:00:00Z","sal":1100,"comm":null,"deptno":20},

                  {"empno":7782,"ename":"CLARK","job":"MANAGER","mgr":7839,"hiredate":"1981-06-08T22:00:00Z","sal":2450,"comm":null,"deptno":10},

                  {"empno":7934,"ename":"MILLER","job":"CLERK","mgr":7782,"hiredate":"1982-01-22T23:00:00Z","sal":1300,"comm":null,"deptno":10}

              ]}';

          ---

          apex_json.parse(l_json_text);

          ---

          l_count := APEX_JSON.get_count(p_path => 'items');

          dbms_output.put_line('Members count: ' || l_count);

          ---

          for i in 1 .. l_count

          loop

              dbms_output.put_line('Employee Number: ' ||

              -- apex_json.get_number(p_path => 'items.empno[%d].empno', p0 => i));

              -- apex_json.get_number(p_path => 'items.empno[%d]', p0 => i));

              apex_json.get_number(p_path => 'items.empno.empno[%d]', p0 => i));

          end loop;

      end;

      /

       

      I can get the number of elements of the type "empno", but I am unable to get the values for "empno", "ename",...

       

      Does someone know how to extract these values?

       

      Kind Regards

        • 1. Re: Extract field values from JSON string using PL/SQL
          Adrian D

          Hi Laury

           

          You were pretty close. Here is what you need in your loop.

           

             for i in 1 .. l_count

             loop

                  dbms_output.put_line('Employee Number: ' ||

                 apex_json.get_number(p_path => 'items[%d].empno', p0 => i));

             end loop;

           

          Regards,

          Adrian

          1 person found this helpful
          • 2. Re: Extract field values from JSON string using PL/SQL
            Kiran Pawar

            Hi Laury,

            Laury wrote:

             

            I am trying to extract the fields and the values of these fields from a JSON string with a PL/SQL piece of code:

            ..

            I can get the number of elements of the type "empno", but I am unable to get the values for "empno", "ename",...

            Does someone know how to extract these values?

            You have to play with the APEX_JSON API to get what you want. Adrian has given a correct way to achieve what you want, following is the code demonstrating the types of APEX_JSON.GET_XXXX to extract different type of values viz. NUMBER, VARCHAR2, DATE:

            DECLARE  
              
                l_values apex_json.t_values;  
                l_source clob;  
                l_data_count integer;  
              
            BEGIN  
              
                l_source := '{
                    "items":[
                        {"empno":7876,"ename":"ADAMS","job":"CLERK","mgr":7788,"hiredate":"1983-01-11T23:00:00Z","sal":1100,"comm":null,"deptno":20},
                        {"empno":7782,"ename":"CLARK","job":"MANAGER","mgr":7839,"hiredate":"1981-06-08T22:00:00Z","sal":2450,"comm":null,"deptno":10},
                        {"empno":7934,"ename":"MILLER","job":"CLERK","mgr":7782,"hiredate":"1982-01-22T23:00:00Z","sal":1300,"comm":null,"deptno":10}
                    ]}';  
              
                apex_json.parse (  
                    p_values => l_values,  
                    p_source => l_source );
              
                dbms_output.put_line ( 'Items Members Count --> '||  
                                        apex_json.get_count (  
                                            p_values => l_values,  
                                            p_path  => 'items' )  
                                      );  
              
                l_data_count := apex_json.get_count (  
                                    p_values => l_values,  
                                    p_path  => 'items' );  
              
                for i in 1 .. l_data_count loop  
              
                dbms_output.put_line ( 'empno --> '||  
                                        apex_json.get_number (  
                                            p_values => l_values,  
                                            p_path  => 'items[%d].empno',  
                                            p0      => i )  
                                        ||', '||  
                                        'ename --> '||  
                                        apex_json.get_varchar2 (  
                                            p_values => l_values,  
                                            p_path  => 'items[%d].ename',  
                                            p0      => i )  
                                        ||', '||  
                                        'job --> '||  
                                        apex_json.get_varchar2 (  
                                            p_values => l_values,  
                                            p_path  => 'items[%d].job',  
                                            p0      => i )  
                                        ||', '||  
                                        'mgr --> '||  
                                        apex_json.get_number (  
                                            p_values => l_values,  
                                            p_path  => 'items[%d].mgr',  
                                            p0      => i )   
                                        ||', '||  
                                        'hiredate --> '||  
                                        to_char(apex_json.get_date (  
                                            p_values => l_values,  
                                            p_path  => 'items[%d].hiredate',  
                                            p0      => i ), 'DD-Mon-YYYY')  
                                        ||', '||  
                                        'sal --> '||  
                                        apex_json.get_number (  
                                            p_values => l_values,  
                                            p_path  => 'items[%d].sal',  
                                            p0      => i )  
                                        ||', '||  
                                        'comm --> '||  
                                        nvl(apex_json.get_number (  
                                            p_values => l_values,  
                                            p_path  => 'items[%d].comm',  
                                            p0      => i ),0)
                                        ||', '||  
                                        'deptno --> '||  
                                        apex_json.get_number (  
                                            p_values => l_values,  
                                            p_path  => 'items[%d].deptno',  
                                            p0      => i )
                                      );  
                end loop;
              
            END;
            

            The above code produces following output:

            Items Members Count --> 3
            empno --> 7876, ename --> ADAMS, job --> CLERK, mgr --> 7788, hiredate --> 11-Jan-1983, sal --> 1100, comm --> 0, deptno --> 20
            empno --> 7782, ename --> CLARK, job --> MANAGER, mgr --> 7839, hiredate --> 08-Jun-1981, sal --> 2450, comm --> 0, deptno --> 10
            empno --> 7934, ename --> MILLER, job --> CLERK, mgr --> 7782, hiredate --> 22-Jan-1982, sal --> 1300, comm --> 0, deptno --> 10
            

            Following are the forum threads that will help how you can use APEX_JSON API:

             

            Hope this helps!

             

            Regards,

            Kiran

            1 person found this helpful
            • 3. Re: Extract field values from JSON string using PL/SQL
              Laury

              Hi,

               

              Thanks for the indications.


              In fact in my code I defined l_source with a "hard-coded" value for the JSON string.
              In practice, I want to get it through an URL, so for instance:

               

              l_response := apex_web_service.make_rest_request
              (  
              p_url          => 'http://192.168.1.123:8090/apex500/ZEUS/7782/7934',
              p_http_method  => 'GET'
              ); 

               

              where 7782 and 7934 give the range to extract. So, in that situation: 7782,7782,7934,a and l_response is the JSON string.

               

              Is it the right way to do to extract the JSON string?
              How to "hide"  the IP address or server name in the PL/SQL code?
              How to hide the port number of the Tomcat server in the PL/SQL code?

               

              I am working with APEX 5.0.0.
              Does the link "How to Parse a JSON Object in Oracle APEX v5.1 that is Stored within an Array" also applies to APEX 5.0.0?

               

              Kind Regards

              • 4. Re: Extract field values from JSON string using PL/SQL
                Laury

                Additionally, what means exactly the pi parameter?
                I have get a look at the Oracle documentation, but I do not understand what is meant by "Each %N in p_path is replaced by pN and every i-th %s or %d is replaced by the p[i-1].".
                Does someone understands what it means?

                Kind Regards

                • 5. Re: Extract field values from JSON string using PL/SQL
                  Laury

                  Hi,

                   

                  Some more remarks.

                   

                  Also, what should be exactly the syntax when manipuating p_path.
                  I have get a look at the Oracle documentation, but I do not understand what is meant by "Index into p_values.".

                   

                  @Kirian:

                  On this post:
                  https://community.oracle.com/message/13189960#13189960
                  you have the following example:

                  l_source := '{ "Success":true 
                  ,"Message":null 
                  ,"Data":[ { "Id":2,"Name":"Dotcoin","Symbol":"DOT","Algorithm":"Scrypt" } 
                  , { "Id":64,"Name":"OrbitCoin","Symbol":"ORB","Algorithm":"Yescrypt" } 
                  , { "Id":7,"Name":"Feathercoin","Symbol":"FTC","Algorithm":"NeoScrypt" } 

                  }';

                  dbms_output.put_line ( 'Id --> '|| 
                  apex_json.get_number ( 
                  p_values => l_values, 
                  p_path  => 'Data[%d].Id', 
                  p0      => 1 )
                  ...

                  On that other post:
                  https://community.oracle.com/thread/4056846
                  you have the following example:

                  l_source := '[ 

                  "type": "Campaign", 
                  "currentStatus": "Active", 
                  "id": "206", 
                  "depth": "complete", 
                  "folderId": "1428", 
                  "name": "Car Loan", 
                  "isReadOnly": "false" 
                  }, 

                  "type": "Debate", 
                  "currentStatus": "Active", 
                  "id": "207", 
                  "depth": "pending", 
                  "folderId": "1429", 
                  "name": "House Loan", 
                  "isReadOnly": "true" 

                  ]';  

                  dbms_output.put_line ( 'type --> '||  
                  apex_json.get_varchar2 (  
                  p_values => l_values,  
                  p_path  => '[%d].type',  
                  p0      => i )  
                  ...

                  What is the difference with that positional %d?
                  How does it impact the JSON string? In one example it starts with "{" (like my example in this post), and in your other example it starts with "[".

                  What is then a correct JSON format?

                   

                  Kind Regards