Location>code7788 >text

Oracle deletes and modifys Json data

Popularity：532 ℃/2025-03-17 11:02:12

1. Background:

Due to project requirements, large Json data needs to be databaseed into DB (clob type). Due to the long content or the limitation of oracle version, some well-known processing methods cannot be used.

To accurately solve the problem, you can directly look at the fourth step: [4. Corresponding Lizi SQL:], the others are nonsense.

 

2. Structure:

Raw data:

'{"name": "Li Si", "skillLevel":{"Project Manager": "Senior", "Technical Manager": "Senior", "Product Manager": "Senior"}}'

 

3. Goal:

3.1. Modify "Li Si" to "Li Si Big Boss"【Modify Level 1 value】

3.2. Change the "Advanced" of "Project Manager" to "Super Advanced"【Modify the second level value】

3.3. Delete attributes "Technical Manager"【Delete key】

3.4. "Product Manager" does not make any modifications【Don't move】

3.5. Added attributes "Technical Director", level is "Super Advanced"【Added key-value】

Just compare the SQL in 4, so I won’t disassemble a single SQL.

 

4. Corresponding chestnut SQL:

SELECT 
    JSON_MERGEPATCH(
        '{"name": "Li Si", "skillLevel":{"Project Manager": "Senior", "Technical Manager": "Senior", "Product Manager": "Senior"}}'
        , '{"name": "Boss Li Si", "skillLevel":{"Project Manager": "Super-Senior", "Technical Manager": null, "Technical Director": "Super-Senior"}}'
         RETURNING CLOB PRETTY
    ) AS text 
FROM dual t

 

5. JSON_MERGEPATCH function explanation

Add toRETURNING CLOB PRETTY is because my Json_data is too large (more than varchar2). If I don't specify the return clob type, it will not be processed and will return NULL.

Note: There are only two input parameters, and the second input parameter is followed by the specified type

JSON_MERGEPATCH(json_data, update_json_date [RETURNING CLOB PRETTY])

 

6. Practical application

Modify Li Si's information

UPDATE T.JSON_DATA =  
    JSON_MERGEPATCH(
        T.JSON_DATA
        , '{"name": "Boss Li Si", "skillLevel":{"Project Manager": "Super-Senior", "Technical Manager": null, "Technical Director": "Super-Senior"}}'
         RETURNING CLOB PRETTY
    ) 
FROM JSON_DATA_TABLE t 
WHERE tJSON_EXISTS(t.JSON_DATA, '$.name ? (@ == "Li Si")');

 

7. Other functions explanation

--JSON_QUERY query object, if the query's attribute is not an object, it returns NULL
SELECT JSON_QUERY('{"common": {"sex": { "M": "Male", "F": "Female", "U": "Not detailed" }}}', '$.') as sexLabel FROM DUAL;

--JSON_VALUE query field, if the query's attribute is an object, return NULL
SELECT JSON_VALUE('{"common": {"sex": { "M": "Male", "F": "Female", "U": "Not detailed" }}}', '$.') as sexLabel FROM DUAL;

--JSON_EXISTS determines whether the attribute or value exists
SELECT 1 FROM DUAL WHERE JSON_EXISTS('{"common": {"sex": { "M": "Male", "F": "Female" }}}', '$.');
SELECT 1 FROM DUAL WHERE JSON_EXISTS('{"common": {"sex": { "M": "Male", "F": "Female" }}}', '$. ? (@ == "Male")');

--JSON_MERGEPATCH merges two objects and replaces the properties of the first JSON object with the existing properties of the second parameter;
SELECT JSON_MERGEPATCH('{"common": {"sex": { "M": "Male", "F": "Female" }}}', '{"common": {"sex": { "M": "Male 1", "F": "Female" }}}') as sex FROM DUAL;

--If the return value of JSON_MERGEPATCH is too large, you need to specify the return type:
SELECT JSON_MERGEPATCH(TO_CLOB('{"common": {"sex": { "M": "Male", "F": "Female" }}}'), '{"common": {"sex": { "M": "Male 1", "F": "Female" }}}' RETURNING CLOB PRETTY ) as sex FROM DUAL;

--JSON_TABLE Converts JSON data to table structure display JSON_OBJECT Generates a JSON object JSON_ARRAY Generates a JSON array

--The method cannot be found
--JSON_MERGEPRESERVE Modify data usage
--JSON_MODIFY Modify data usage
--JSON_KEYS Get all keys
--JSON_REMOVE Delete a key

 



 
						


												

							<Previous