Managing and Updating JSON Data with DML Operations

In the world of Oracle development today, working with JSON data has become an essential skill.

As modern applications increasingly depend on the flexibility of semi-structured data and the seamless integration offered by RESTful services, understanding JSON is more important than ever.

With Oracle Database 21c and its subsequent versions, you have the advantage of managing and updating JSON documents directly within your tables. Thanks to powerful native SQL functions, there's no need for complex PL/SQL or manual parsing anymore.

Why use native JSON DML operations?

• Performance: Native functions like json_transform and json_mergepatch are optimized for speed.

• Simplicity: Update, insert, or remove fields in json documents with a single sql statement.

• Flexibility: Works with columns of type json, clob, varchar2, or blob

To start lets create a simple table with only two columns

create table orders (
    order_id number 
        generated by default on null as identity
        constraint orders_pk primary key
        not null,
    order_data clob
);

and insert a JSON into order_data

insert into orders(order_data) values ('{
  "order_id": 1001,
  "customer": {
    "customer_id": 501,
    "name": "jane smith",
    "email": "jane.smith@example.com"
  },
  "items": [
    {
      "item_id": 2001,
      "product": "laptop",
      "quantity": 1,
      "price": 1200.00
    },
    {
      "item_id": 2002,
      "product": "mouse",
      "quantity": 2,
      "price": 25.50
    }
  ],
  "status": "processing",
  "order_date": "2025-05-23t14:30:00z",
  "shipping": {
    "address": "123 main street, leeds, uk",
    "method": "express",
    "cost": 15.00
  },
  "last_updated": "2025-05-23t14:30:00z"
}
');

If we run a simple query (select order_data from orders), we can see the full JSON. However, we can easily filter it to show only the important data using the code below. In the example, I only want to see the order status.

select order_id,
       json_value(order_data, '$.status') as status
from orders
where order_id = 1;

Using json_transform, we can easily update this field.This updates the status field to "shipped" for order 1. if the field doesn't exist, it is created (upsert behavior)

update orders
set order_data = json_transform(order_data,
    set '$.status' = 'shipped')
where order_id = 1;

You can also update several fields in a single statement. Only the changed fragments of the json are rewritten for efficiency.

update orders
set order_data = json_transform(order_data,
    set '$.status' = 'delivered',
    set '$.last_updated' = systimestamp)
where order_id = 1;

We can remove a field from json. The code below removes the last_updated field if it exists.

update orders
set order_data = json_transform(order_data,
    remove '$.last_updated')
where order_id = 1;

It's also possible to update the JSON using json_mergepatch with a single set statement by passing the part of the JSON that needs to be updated.

update orders
set order_data = json_mergepatch(order_data, '{"status":"delivered"}')
where order_id = 1;

Best Practices

• Prefer native json functions (json_transform, json_mergepatch) over manual pl/sql parsing for better performance and maintainability.

• Use the json data type (oracle 21c+) for optimal storage and partial updates.