Скачать или смотреть Updating JSON Objects in Oracle 12c: A Step-by-Step Guide to Appending Keys

Learn how to update JSON objects in Oracle 12c, specifically how to append new keys and modify existing ones using the `PARSE()` and `PUT()` methods.
---
This video is based on the question https://stackoverflow.com/q/67619572/ asked by the user 'Vahram Danielyan' ( https://stackoverflow.com/u/12841558/ ) and on the answer https://stackoverflow.com/a/67644036/ provided by the user 'Barbaros Özhan' ( https://stackoverflow.com/u/5841306/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: Append Key into JSON_OBJ_COLUMN - Oracle

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
Updating JSON Objects in Oracle 12c: A Step-by-Step Guide to Appending Keys

In the world of databases, managing and updating JSON objects can sometimes prove challenging, especially when you are trying to append new keys or modify existing values. This is particularly true in Oracle 12c, where you may not be sure whether you need to overwrite an existing JSON object or if you can simply update it.

The Problem: Updating JSON Data

Let's set the scene. Imagine that you've created a table in Oracle 12c that contains JSON objects, and your table looks something like this:

iddatejson_obj101-MAY-2021{ key_1: val1, key_2: val2 }Now, suppose you want to make the following updates to the JSON object related to id 1:

Update the value of key_2 from val2 to val_22.

Append a new key key_3 with value val_3.

You may be wondering, do you need to overwrite the entire JSON object, or can you achieve this using more efficient methods?

The Solution: Using PARSE() and PUT()

If you're using Oracle database version 12c or later, specifically 12R2+ , you'll be relieved to know that you can easily append keys and update values in your JSON objects using a combination of the PARSE() and PUT() methods along with a standard UPDATE statement.

Step-by-Step Instructions

Here’s a breakdown of how to update the JSON objects:

Declare Variables:

Define a variable for the JSON object, as well as a variable to hold the content of the JSON object from the database.

Select the JSON Object:

Use a SELECT statement to retrieve the existing JSON object related to the specific id.

Parse the JSON Object:

Use JSON_OBJECT_T.PARSE() to convert the JSON string into a JSON object.

Update Existing Key and Append New Key:

Use the PUT() method to update the value of an existing key (key_2) and to append a new key (key_3).

Convert Back to CLOB:

Convert the modified JSON object back to a CLOB format for updating.

Perform the Update:

Execute an UPDATE SQL statement to save your changes back into the database.

Example Code

Here's a complete example of how this can be done in PL/SQL:

[[See Video to Reveal this Text or Code Snippet]]

Summary

By leveraging the powerful features found in Oracle 12c, you can efficiently update and manage JSON objects without the need to overwrite them entirely. This method not only saves time but also ensures that your database operations remain efficient.

In conclusion, if you are working with JSON data in Oracle 12c, remember to utilize the PARSE() and PUT() methods for easy updates and modifications. Happy coding!