如何在Oracle 12c中更新JSON字符串的一部分? [英] How to update a part of JSON string in Oracle 12c?

问题描述

我的oracle数据库中有一个表，如下所示.

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"
                             }');

现在我知道了，我可以使用$来获取JSON字符串的特定属性(如address)的值.

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

类似地，我可以在不提供更新查询中整个JSON结构的情况下更新JSON字符串的Street属性吗?

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

请帮助我.

推荐答案

在12.2.0.1.0中通过PL/SQL支持此功能.新的PL/SQL对象支持对JSON内容进行细粒度的操作

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:用于处理JSON对象
• JSON_ARRAY_T:用于处理JSON数组

JSON_OBJECT_T和JSON_ARRAY_T是JSON_ELEMENT_T的子类型

JSON_OBJECT_T and JSON_ARRAY_T are subtypes of JSON_ELEMENT_T

这些对象提供了一组类似于GSON的JSON处理方法.

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} 

查看全文