# 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

```sql
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

```sql
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.

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

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1748008306927/1c0cba07-36c9-4c9a-aa29-3aa32e2fc5fb.png align="center")

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)

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

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1748008441233/bd60e97d-96ea-4ba6-b711-a02baac0c358.png align="center")

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

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

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1748011301475/42a91bd4-0751-43dc-b03f-89cfe0a7122c.png align="center")

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

```sql
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.

```sql
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.