Passing in a JSON_OBJECT_T into SODA_DOCUMENT_T

Question

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?

Answer 1

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"}