How to update a part of JSON string in Oracle 12c?

Question

I've a table in my oracle DB as below.

CREATE TABLE my_table(
  id    RAW(16) NOT NULL,
  data  CLOB,
  CONSTRAINT my_table_pk PRIMARY KEY (id),
  CONSTRAINT my_table_json_chk CHECK (data IS JSON)
);


INSERT INTO my_table (id, data)
VALUES (SYS_GUID(),
        '{
          "FirstName"      : "aa",
          "LastName"       : "bb",          
          "Address"        : {
                              "Street"   : "99 My Street",
                              "City"     : "My City",
                              "Country"  : "UK",
                              "Postcode" : "A12 34B"
                             }');

Now I know, I can fetch value of a specific property like address of the JSON string using $.

Similarly, can I update Street property of the JSON string without providing the entire JSON structure in my update query?

Please help me on this.

Why you need to store JSON in the db? You can create columns forceach of the property and query specific columns and filter on them too. Finding value in and getting only particular value out of it is basically a heavy string operation which will hit the performance of db very hard. — Chetan
– Chetan, Commented Jan 22, 2017 at 21:41
Chetan, because of some other requirements, I've to save the JSON in DB and I can't change that design now. So please suggest me if you can assist me on this doubt. — Raj
– Raj, Commented Jan 23, 2017 at 14:54

mark d drake · Accepted Answer · 2017-04-29 04:12:45Z

This is supported via PL/SQL in 12.2.0.1.0. New PL/SQL objects enable fine grained manipulation of JSON content

JSON_OBJECT_T: for working with JSON objects
JSON_ARRAY_T: for working with JSON Arrays

JSON_OBJECT_T and JSON_ARRAY_T are subtypes of JSON_ELEMENT_T

These objects provide a set of methods for manipulating JSON similar to GSON.

enter code here 
 WITH FUNCTION updateTax(JSON_DOC in VARCHAR2 ) RETURN VARCHAR2 
 IS
    jo JSON_OBJECT_T;  
    price NUMBER;  
    taxRate NUMBER; 
 BEGIN
    jo := JSON_OBJECT_T(JSON_DOC);     
    taxRate := jo.get_Number('taxRate');   
    price := jo.get_Number('total');   
    jo.put('totalIncludingTax', price * (1+taxRate));  
    RETURN jo.to_string(); 
 END; 
 ORDERS as (
    select '{"taxRate":0.175,"total":10.00}' JSON_DOCUMENT       
      from dual 
 ) 
 select JSON_DOCUMENT, updateTax(JSON_DOCUMENT)
  from ORDERS; 

JSON_DOCUMENT                   UPDATETAX(JSON_DOCUMENT)
------------------------------- --------------------------------------------------------
{"taxRate":0.175,"total":10.00} {"taxRate":0.175,"total":10.00,"totalIncludingTax":11.75}

https://docs.oracle.com/database/122/ADJSN/using-PLSQL-object-types-for-JSON.htm#ADJSN-GUID-F0561593-D0B9-44EA-9C8C-ACB6AA9474EE

Collectives™ on Stack Overflow

How to update a part of JSON string in Oracle 12c?

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related