ORDS, SODA & JSON in the Database

Hi

I have a table with json-data stored in a blob-column. Mostly the data is larger than 4000 char.

Is there a way of updating values in the json structure in a blob-column? All examples I have seen are doing conversions to clob etc.

When looking at JSON_OBJECT_T and JSON_ELEMENT_T there is a function put but the examples never seem to use

data from fields in the database.

I tried this:

set serveroutput on;

DECLARE

jo JSON_OBJECT_T;

json_data blob;

BEGIN

select load_data into json_data from load_pos where pos_id like '%TestUlrika22%' and load_dt='2019-09-16 11:29:36,609521000';

    jo := new JSON_OBJECT_T(json_data);

    jo.put('storeCode', '11111');

    DBMS_OUTPUT.put_line(jo.TO_STRING);

--    update load_pos

--    set load_data=to_blob(jo)

--    where load_dt='2019-09-16 11:29:36,609521000';

And I got an extra storeCode at the end instead of updating the present one.

{"records":[{"value":{"receiptHeader":{"version":"1.0.0","countryCode":"46","storeCode":"99924","storeCustomerNumber":"34865","pickingStoreCode":"99924","pickingCustomerNumber":"34865","salesChannel":0,"transactionNumber":"19832705","transactionNumberPos":"157273","receiptDateTime":"2019-05-09T22:02:22","cashierNumber":"2555","receiptNumber":"0","cashRegisterNumber":"3","receiptType":2,"salesLocation":0,"cashRegisterType":0,"customerType":0},"receiptRows":[]},"key":"se::xx::messaging::standard"}],"storeCode":"11111"}

END;

Also how do I convert the JSON_OBJECT_T back to blob to be able to update the table?

Thanks and Best Regards

Ria