oracle-databaseplsqloracle-autonomous-dboracle-soda

Passing in a JSON_OBJECT_T into SODA_DOCUMENT_T


I have a PL/SQL handler using the SODA package to manipulate a JSON database. I want to:

  1. Read the value for key id in the payload
  2. Write the payload JSON into a new document in the database.

To do step 1, The handler takes :body to be parsed as a JSON_OBJECT_T type, so that I can access the value for key id in the payload.

But for step 2, if I read body again when constructing with SODA_DOCUMENT_T(b_content=> :body), it will simply give me a blank document.

I also can't pass in the JSON_OBJECT_T variable in directly, like SODA_DOCUMENT_T(j_content=> jso), because that function expects a JSON type and not a JSON_OBJECT_T. I can't find the JSON type documentation, but saw in code examples the function JSON('{}') to generate one.

Reading :body_text however gives me other problems - because JSON() function cannot handle line breaks in the payload and gives an error instead.

Currently to work around this I'm using the following:

SODA_DOCUMENT_T(
  j_content => JSON(jso.to_string())
)

Which seems very silly because I'm serialising it to a string again before converting it back into JSON type. Is there a proper way to read the value of a key of the payload, and pass it into the SODA_DOCUMENT_T?


Solution

  • Yes, you can't pass in JSON_Object_T instance to SODA_Document_T constructor. However, you may use JSON_QUERY() PL/SQL function that drills into the JSON document given a path expression and it returns a JSON type instance.

    Example:

    jval := JSON_QUERY(body, '$.id' RETURNING JSON);
    

    In the above example, $.id is the path expression and json_query() returns json value corresponding to the field id starting from the root $

    Documentation: https://docs.oracle.com/en/database/oracle/oracle-database/21/adjsn/function-JSON_QUERY.html

    You should now be able to pass this jval instance of type JSON to SODA_Document_T constructor.

    Yes, I think you should definitely avoid back and forth conversions.

    Usage:

    SQL> 
    CREATE OR REPLACE FUNCTION process_request(body  IN VARCHAR2)
    RETURN JSON
    IS
      j  JSON;
      d  SODA_Document_T;
    BEGIN
      
      j := JSON_QUERY(body, '$.id' RETURNING JSON);
      d := SODA_Document_T(j_Content => j);
      -- n := coll.insert_one(d);
      RETURN j;
    END;
     13  /
    
    Function created.
    
    SQL> SELECT process_request('{"id":{"type": "string", "val": "DEADBEEF"}}') FROM dual;
    
    PROCESS_REQUEST('{"ID":{"TYPE":"STRING","VAL":"DEADBEEF"}}')
    --------------------------------------------------------------------------------
    {"type":"string","val":"DEADBEEF"}
    

    I recommend this approach. Let me know if that helps!

    Alternate solution:

    BTW, given a DOM, you can directly go to JSON type instance as well, using the following method in JSON_Object_T type:

    MEMBER FUNCTION GET_JSON RETURNS JSON
     Argument Name                  Type                    In/Out Default?
     ------------------------------ ----------------------- ------ --------
     KEY                            VARCHAR2                IN
    

    This method pretty much does the same work as JSON_Query() shown above:

    SQL>
    CREATE OR REPLACE FUNCTION process_body(data IN VARCHAR2)
    RETURN JSON
    AS
      body JSON_Object_T;
      j    JSON;
    BEGIN
      body := JSON_Object_T.parse(data);
      j := body.get_Json('id');
      return j;
    END;
     11  /
    
    SQL> SELECT process_body('{"id":{"type": "string", "val": "DEADBEEF"}}') FROM dual;
    
    PROCESS_BODY('{"ID":{"TYPE":"STRING","VAL":"DEADBEEF"}}')
    --------------------------------------------------------------------------------
    {"type":"string","val":"DEADBEEF"}