Search for a command to run...
No comments yet. Be the first to comment.
Oracle APEX already includes Font APEX as the default icon library for Universal Theme. In most cases, that is enough. But in one of my APEX apps, I needed a few icons that were not available there, s
Looking to give your Oracle APEX mobile app a modern, native feel? Here’s a quick hack: add a floating action button that stays anchored at the bottom corner just like in popular mobile apps. This sma
APEX 26.1 has been updated to the Universal Theme. While much of the attention in this release is on AI features, I think the UT improvements are just as important for developers like me who care abou
The apex_barcode package offers a PL/SQL API to generate various types of barcodes. You can output barcodes as SVG (Scalable Vector Graphics) values or as PNG file blobs, making it easy to embed them
Oracle APEX makes it easy to get data on screen fast, but often, the default tabular output isn’t enough. What if you want to highlight statuses, add icons, or display badges all without writing compl
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.
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;
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.