3 Replies Latest reply on Feb 23, 2018 3:22 PM by Kiran Pawar

    How to extract the member names of a JSON string

    Laury

      Hi,

       

      If I have a JSON sting like:

       

      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} 

              ]}';   

             

             

      1) How can extract the members: empno, ename, job,...

      I suspect I have to use the function JSON_APEX.GET_MEMBER, but how?

      I found the documentation not very explicit.

       

      2) How to get a count of all distinct members empno, ename, job,...

       

      Thanks by advance for any tip.

       

      Kind Regards

        • 1. Re: How to extract the member names of a JSON string
          Kiran Pawar

          Hi Laury,

          Laury wrote:

           

          If I have a JSON sting like:

          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}

          ]}';

          If you look keenly to my reply to your old thread every thing is given in code snippet i posted: Re: Extract field values from JSON string using PL/SQL

          Still again to repeat that:

          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 --> '|| 
                                      apex_json.get_number ( 
                                          p_values => l_values, 
                                          p_path  => 'items[%d].comm', 
                                          p0      => i ) 
                                      ||', '|| 
                                      'deptno --> '|| 
                                      apex_json.get_number ( 
                                          p_values => l_values, 
                                          p_path  => 'items[%d].deptno', 
                                          p0      => i )
                                    ); 
              end loop; 
          
          END; 
          

          Laury wrote:

          1) How can extract the members: empno, ename, job,...

          I suspect I have to use the function JSON_APEX.GET_MEMBER, but how?

          I found the documentation not very explicit.

          Yes APEX_JSON.GET_XXXX API has to be used. In the above code the snippet gives how to extract the members using APEX_JSON.GET_VARCHAR2, APEX_JSON.GET_NUMBER and APEX_JSON.GET_DATE.

              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 --> '|| 
                                      apex_json.get_number ( 
                                          p_values => l_values, 
                                          p_path  => 'items[%d].comm', 
                                          p0      => i ) 
                                      ||', '|| 
                                      'deptno --> '|| 
                                      apex_json.get_number ( 
                                          p_values => l_values, 
                                          p_path  => 'items[%d].deptno', 
                                          p0      => i )
                                    ); 
              end loop;
          

          Laury wrote:

           

          2) How to get a count of all distinct members empno, ename, job,...

          You cannot count the array elements (constituent components), but the array members (the number of rows of data) is given by APEX_JSON.GET_COUNT

            l_data_count := apex_json.get_count ( 
                                  p_values => l_values, 
                                  p_path  => 'items' ); 
          

           

          Hope this helps!

           

          Regards,

          Kiran

          1 person found this helpful
          • 2. Re: How to extract the member names of a JSON string
            Laury

            Hi,

             

            Thanks for the feedback.

            I did not formulate properly my question in this thread. My apologies for this...

            In fact I wanted to extract the field names like "ename" with the corresponding values like "ADAMS", but without knowing what the name is for this element/field.

            I mean without knowing that "ename" is "ename" and not "name"...

             

            In the meantime I have found this very nice article that I want to share and that answers pretty good my post:

            http://sql-plsql-de.blogspot.nl/2016/12/a-plsql-json-explorer-based-on-apexjson.html

            The article lacks somewhat comments/explanations on the various steps of the code... but it works!!

            My complements to Carsten Czarski!

             

            Hope this can also help someone.

             

            Kind Regards

            • 3. Re: How to extract the member names of a JSON string
              Kiran Pawar

              Hi Laury,

              Laury wrote:

               

              Hi,

               

              Thanks for the feedback.

              I did not formulate properly my question in this thread. My apologies for this...

              In fact I wanted to extract the field names like "ename" with the corresponding values like "ADAMS", but without knowing what the name is for this element/field.

              I mean without knowing that "ename" is "ename" and not "name"...

               

              In the meantime I have found this very nice article that I want to share and that answers pretty good my post:

              http://sql-plsql-de.blogspot.nl/2016/12/a-plsql-json-explorer-based-on-apexjson.html

              The article lacks somewhat comments/explanations on the various steps of the code... but it works!!

              My complements to Carsten Czarski!

               

              Hope this can also help someone.

               

              Kind Regards

              Apologies from my side as well, but thanks for posting this explanation. Now, I get what you exactly wanted and learnt something new about JSON parsing as well!

               

              Regards,

              Kiran