How to update the nested array in json using oracle?

@darrion.kuhn 

In Oracle, you can update a nested array in a JSON document using the JSON_MERGEPATCH function. This function allows you to merge the contents of one JSON document into another, including updating arrays nested within the JSON structure.

Here's an example of how you can update a nested array in a JSON document using JSON_MERGEPATCH:

1. Create a JSON document with a nested array:

1
2
3
4
5
6

CREATE TABLE json_table (
    id NUMBER PRIMARY KEY,
    json_data CLOB
);

INSERT INTO json_table (id, json_data) VALUES (1, '{"id": 1, "nested_array": [1, 2, 3]}');

1. Update the nested array in the JSON document using JSON_MERGEPATCH:

1
2
3
4
5
6
7
8

UPDATE json_table
SET json_data = JSON_MERGEPATCH(
    json_data,
    '{
        "nested_array": [4, 5, 6, 7]
    }'
)
WHERE id = 1;

In this example, the nested array in the JSON document with id 1 is updated to [4, 5, 6, 7] by merging the new array into the existing JSON structure using JSON_MERGEPATCH.

Note that the JSON_MERGEPATCH function requires Oracle Database 21c or later. If you are using an earlier version of the database, you may need to use a different approach to update nested arrays in JSON documents.