7 Replies Latest reply on Apr 15, 2020 2:06 PM by Martin J.

    Recreate JSON structure from a valid JSON CLOB

    Martin J.

      Hi,

       

      I am in need of a query that can deteremine the JSON structure of a certain column. That column contains a valid JSON structure but each row can be different. I'm looking for a way to find the structure so I can write code to handle that structure. But I have millions of rows in my source table and each row can reveal a different structure (ie. 15 in total, but suppose there is a 16th).

       

      Is there a tool or query that can provide this information?

       

      Regards,

      Martin J.

        • 1. Re: Recreate JSON structure from a valid JSON CLOB
          EJ-Egyed

          I'm not sure I understand what you are trying to achieve.  Can you explain it with some examples?  JSON_OBJECT_T can be used to parse the JSON clob, then you can loop through the arrays and they keys of the objects using various methods.  A more descriptive example of what your goal is would be helpful.

          1 person found this helpful
          • 2. Re: Recreate JSON structure from a valid JSON CLOB
            Martin J.

            I will try to explain what I'm looking for. In general I would like to find out all possible structures of my JSON data in a certain CLOB field. Two examples of different items. I recieve millions of rows and don't know what different JSON structures to receive.

             

            [{  "id": "extra_verkeerslicht",

                "label": "Is de situatie gevaarlijk?",

                "answer": {

                  "id": "niet_gevaarlijk",

                  "label": "Niet gevaarlijk"

            }]

             

            [{ "id": "extra_personen_overig_vaker",

                "label": "Gebeurt het vaker?",

                "answer": {

                  "label": null,

                  "value": true

            }]

             

            What I would like is a SQL query that shows me the structure of the JSON as in above examples without the data.

            • 3. Re: Recreate JSON structure from a valid JSON CLOB
              EJ-Egyed

              I don't think this is possible using just SQL but it is possible using PL/SQL.  Below is something I quickly built that may be do some of what you are looking for that you could potentially turn into a function of some sort.  I'm not sure what kind of output you are expecting but the procedure below gives a pretty descriptive layout of the JSON.  It may not be 100% accurate but worked pretty well in my testing.  Just set the l_input variable to whatever JSON you want to "describe".

               

              /* Formatted on 4/9/2020 8:27:16 AM (QP5 v5.354) */
              DECLARE
                  l_input                  CLOB := '[{  "id": "extra_verkeerslicht",
                  "label": "Is de situatie gevaarlijk?",
                  "test": 1,
                  "arr": [1,2,3,"text",[6,7,8]],
                  "answer": {
                    "id": "niet_gevaarlijk",
                    "label": "Niet gevaarlijk"
              }}]';
              
              
                  invalid_syntax           EXCEPTION;
                  invalid_object           EXCEPTION;
                  PRAGMA EXCEPTION_INIT (invalid_syntax, -40441);
                  PRAGMA EXCEPTION_INIT (invalid_object, -40587);
                  c_indent_size   CONSTANT INTEGER := 4;
                  c_indent_char   CONSTANT VARCHAR2 (1) := '-';
              
              
                  PROCEDURE print_obj_info (p_object json_object_t, p_indent INTEGER DEFAULT 0);
              
              
                  PROCEDURE print_array_info (p_array json_array_t, p_indent INTEGER DEFAULT 0);
              
              
                  FUNCTION get_type (p_element json_element_t)
                      RETURN VARCHAR2
                  IS
                      l_type   VARCHAR2 (20);
                  BEGIN
                      l_type :=
                          CASE
                              WHEN p_element.is_array THEN 'ARRAY'
                              WHEN p_element.is_object THEN 'OBJECT'
                              WHEN p_element.is_number THEN 'NUMBER'
                              WHEN p_element.is_boolean THEN 'BOOLEAN'
                              WHEN p_element.is_string THEN 'STRING'
                              WHEN p_element.is_null THEN 'NULL'
                              WHEN p_element.is_date THEN 'DATE'
                              WHEN p_element.is_timestamp THEN 'TIMESTAMP'
                              ELSE 'unknown'
                          END;
              
              
                      RETURN l_type;
                  END;
              
              
                  PROCEDURE print_obj_info (p_object json_object_t, p_indent INTEGER DEFAULT 0)
                  IS
                      l_keys   json_key_list;
                      l_type   VARCHAR2 (20);
                  BEGIN
                      l_keys := p_object.get_keys;
              
              
                      FOR i IN 1 .. l_keys.COUNT
                      LOOP
                          l_type := get_type (p_object.get (l_keys (i)));
              
              
                          DBMS_OUTPUT.put_line (
                              LPAD (c_indent_char, p_indent, c_indent_char) || l_keys (i) || ': ' || l_type);
              
              
                          CASE l_type
                              WHEN 'OBJECT'
                              THEN
                                  print_obj_info (p_object.get_object (l_keys (i)), p_indent + c_indent_size);
                              WHEN 'ARRAY'
                              THEN
                                  print_array_info (TREAT (p_object.get (l_keys (i)) AS json_array_t),
                                                    p_indent + c_indent_size);
                              ELSE
                                  NULL;
                          END CASE;
                      END LOOP;
                  END;
              
              
                  PROCEDURE print_array_info (p_array json_array_t, p_indent INTEGER DEFAULT 0)
                  IS
                      l_type   VARCHAR2 (20);
                  BEGIN
                      FOR i IN 0 .. p_array.get_size - 1
                      LOOP
                          l_type := get_type (p_array.get (i));
                          DBMS_OUTPUT.put_line (LPAD (c_indent_char, p_indent, c_indent_char) || l_type);
              
              
                          CASE l_type
                              WHEN 'OBJECT'
                              THEN
                                  print_obj_info (TREAT (p_array.get (i) AS json_object_t), p_indent + c_indent_size);
                              WHEN 'ARRAY'
                              THEN
                                  print_array_info (TREAT (p_array.get (i) AS json_array_t), p_indent + c_indent_size);
                              ELSE
                                  NULL;
                          END CASE;
                      END LOOP;
                  END;
              BEGIN
                  print_obj_info (json_object_t (l_input));
              EXCEPTION
                  WHEN invalid_object
                  THEN
                      print_array_info (json_array_t (l_input));
                  WHEN invalid_syntax
                  THEN
                      DBMS_OUTPUT.put_line ('Invalid JSON syntax');
              END;
              
              • 4. Re: Recreate JSON structure from a valid JSON CLOB
                MaxOrgiyan-Oracle

                Have you seen the JSON dataguide feature? https://docs.oracle.com/en/database/oracle/oracle-database/19/adjsn/json-dataguide.html#GUID-219FC30E-89A7-4189-BC36-7B9…

                 

                It basically infers the schema of all the JSON documents in your column. There are two kinds - persistent dataguide, that requires a JSON search index to be used. And another dataguide, which computes it on the fly (see https://docs.oracle.com/en/database/oracle/oracle-database/18/sqlrf/JSON_DATAGUIDE.html#GUID-4CF32887-0F46-4925-8381-AE2… . The dataguide, i.e inferred schema of the JSON documents in your column, can be easily fetched by calling a PLSQL function. You could try it out and see if it returns the info you need).

                1 person found this helpful
                • 5. Re: Recreate JSON structure from a valid JSON CLOB
                  EJ-Egyed

                  Wow! JSON_DATAGUIDE seems to be a much better option then what I had thrown together.  Thanks for the info MaxOrgiyan-Oracle! I didn't know this feature existed, but I definitely plan on using it in the future now!

                  • 6. Re: Recreate JSON structure from a valid JSON CLOB
                    Martin J.

                    This guide is the answer to my prayers. Thanks!

                     

                    ** Edited ** Untill I found out it was introduced in 12.2 and we're on 12.1...

                    • 7. Re: Recreate JSON structure from a valid JSON CLOB
                      Martin J.

                      I tried that procedure but got stuck on errors on line 21 and 24.

                      PLS-00201: identifier 'JSON_OBJECT_T' must be declared.

                      PLS-00201: identifier 'JSON_ARRAY_T' must be declared.

                      PLS-00201: identifier 'JSON_ELEMENT_T' must be declared.

                      and more...

                       

                      I found out that these objects are introduced in R12.2 and further. I'm stuck with R12.1. But thanks for the effort!