Forum Stats

  • 3,734,235 Users
  • 2,246,916 Discussions
  • 7,857,194 Comments

Discussions

Recreate JSON structure from a valid JSON CLOB

Martin J.
Martin J. Member Posts: 10 Green Ribbon

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.

cormacoEJ-EgyedthatJeffSmith-OracleMartin J.

Best Answer

Answers

  • EJ-Egyed
    EJ-Egyed Member Posts: 125 Blue Ribbon
    edited Apr 7, 2020 4:47PM

    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.

    Martin J.
  • Martin J.
    Martin J. Member Posts: 10 Green Ribbon
    edited Apr 8, 2020 9:09AM

    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.

  • EJ-Egyed
    EJ-Egyed Member Posts: 125 Blue Ribbon
    edited Apr 9, 2020 8:28AM

    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;
  • EJ-Egyed
    EJ-Egyed Member Posts: 125 Blue Ribbon
    edited Apr 13, 2020 8:54AM

    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!

  • Martin J.
    Martin J. Member Posts: 10 Green Ribbon
    edited Apr 15, 2020 9:56AM

    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...

  • Martin J.
    Martin J. Member Posts: 10 Green Ribbon
    edited Apr 15, 2020 10:06AM

    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!

  • User_CI7SY
    User_CI7SY Member Posts: 1 Green Ribbon

    Hey, I am running below simple code in Oracle 12C database but it throws me error ="PLS-00201: identifier 'JE' must be declared, PLS-00201: identifier 'JE.IS_OBJECT' must be declared ' must be declared" .Is it because of PL/JSON package not installed in the database or something else?


    DECLARE

      je JSON_ELEMENT_T;

      jo JSON_OBJECT_T;

    BEGIN

      je := JSON_ELEMENT_T.parse('{"name":"Radio controlled plane"}');

      IF (je.is_Object) THEN

        jo := treat(je AS JSON_OBJECT_T);

        jo.put('price', 149.99);

      END IF;

      DBMS_OUTPUT.put_line(je.to_string);

    END;

    /

  • thatJeffSmith-Oracle
    thatJeffSmith-Oracle Distinguished Product Manager Posts: 7,701 Employee

    probably, you don't say 12.1 or 12.2...either way for best JSON experience, try 19c or higher

Sign In or Register to comment.