1 Reply Latest reply on Oct 25, 2019 7:28 AM by MaxOrgiyan-Oracle

    Manipulate JSON in a blob

    Ria_B

      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