REST clients often want related data in one response: a hub with its long-haul routes, a member with their preferences. ORDS query handlers can nest JSON without any PL/SQL: a CURSOR expression in the select list becomes a nested array, and a column of the JSON data type is embedded as a JSON object. This guide shows both, and the limits found while testing them.
Before You Start
You need ORDS installed and running against your database, and a schema to work in. The examples use NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder of the Oracle Database 26ai code repository on GitHub. The schema comes from Oracle Database 26ai SQL and PL/SQL Book.
ORDS in the examples answers at https://localhost:8443/ords/, and NIMBUS is REST-enabled with the URL alias nimbus. Replace the host and port with your own ORDS address. The curl commands use -k because the test server has a self-signed certificate; leave it out when your certificate is trusted. JSON responses are formatted for reading; ORDS returns them on one line.
The handlers join the network module from How to Create a REST Module, Template, and Handler with ORDS.DEFINE_MODULE.
Syntax
select parent_columns,
cursor(select child_columns from child where child.fk = parent.pk) as children
from parent
select ..., json_column from ... -- a JSON value is embedded as JSONA Nested Array from a CURSOR Expression
The hubs handler lists each hub with its routes longer than 10,000 km. Paging is turned off for this handler: ORDS pages a collection by wrapping the query, and a CURSOR expression is not allowed inside that wrapper. With paging on, the request failed with status 555 and ORA-22902 in the ORDS log.
Example:
begin
ords.define_template(p_module_name => 'network', p_pattern => 'hubs');
ords.define_handler(p_module_name => 'network',
p_pattern => 'hubs',
p_method => 'GET',
p_source_type => ords.source_type_collection_feed,
p_items_per_page => 0, -- no paging: CURSOR is not allowed in a paged query
p_source => q'[
select a.airport_code, a.city,
cursor(select r.destination, r.distance_km
from routes r
where r.origin = a.airport_code
and r.distance_km > 10000
order by r.distance_km desc) as long_haul
from airports a
where a.is_hub]');
commit;
end;
/Example:
curl -k https://localhost:8443/ords/nimbus/network/hubs
Output:
{
"items": [
{
"airport_code": "DXB",
"city": "Dubai",
"long_haul": [
{
"destination": "AKL",
"distance_km": 14200
},
{
"destination": "LAX",
"distance_km": 13400
},
{
"destination": "GRU",
"distance_km": 12217
},
{
"destination": "SYD",
"distance_km": 12043
},
{
"destination": "ORD",
"distance_km": 11641
},
{
"destination": "YYZ",
"distance_km": 11082
},
{
"destination": "JFK",
"distance_km": 11001
}
]
}
],
"hasMore": false,
"limit": 0,
"offset": 0,
"count": 1,
"links": [
{
"rel": "self",
"href": "https://localhost:8443/ords/nimbus/network/hubs"
},
{
"rel": "describedby",
"href": "https://localhost:8443/ords/nimbus/metadata-catalog/network/item"
}
]
}Dubai, the only hub, carries a long_haul array of its seven routes over 10,000 km.
A JSON Column
CUSTOMERS.LOYALTY is a JSON column. Selected as it is, ORDS embeds it as a JSON object, not as a string.
Example:
begin
ords.define_template(p_module_name => 'network', p_pattern => 'members/:id');
ords.define_handler(p_module_name => 'network',
p_pattern => 'members/:id',
p_method => 'GET',
p_source_type => ords.source_type_collection_item,
p_source => q'[
select customer_id, first_name,
loyalty -- a JSON column comes out as JSON
from customers
where customer_id = :id]');
commit;
end;
/Example:
curl -k https://localhost:8443/ords/nimbus/network/members/1
Output:
{
"customer_id": 1,
"first_name": "Diego",
"loyalty": {
"memberId": "NM100037",
"tier": "Blue",
"points": 3296,
"memberSince": "2021-08-17",
"preferences": {
"seat": "aisle",
"meal": "standard",
"newsletter": true
},
"favoriteAirports": [
"CDG",
"JNB",
"NBO"
]
},
"links": [
{
"rel": "collection",
"href": "https://localhost:8443/ords/nimbus/network/members/"
}
]
}JSON Built in the Query
JSON_OBJECT returns text by default, which ORDS sends as a string. With RETURNING JSON the value has the JSON type and is embedded:
Example:
begin
ords.define_template(p_module_name => 'network', p_pattern => 'routes/:id/card');
ords.define_handler(p_module_name => 'network',
p_pattern => 'routes/:id/card',
p_method => 'GET',
p_source_type => ords.source_type_collection_item,
p_source => q'[
select route_id,
json_object('from' value origin, 'to' value destination returning json) as leg,
json_object('from' value origin, 'to' value destination) as leg_as_text
from routes
where route_id = :id]');
commit;
end;
/Example:
curl -k https://localhost:8443/ords/nimbus/network/routes/7/card
Output:
{
"route_id": 7,
"leg": {
"from": "DXB",
"to": "AMS"
},
"leg_as_text": "{\"from\":\"DXB\",\"to\":\"AMS\"}",
"links": [
{
"rel": "collection",
"href": "https://localhost:8443/ords/nimbus/network/routes/7/"
}
]
}leg is a nested object, while leg_as_text is a string that contains JSON. In this test, the RETURNING JSON column was embedded with the collection item source type, but left out of the response entirely with query one row, so use collection item or collection feed for JSON columns.
Things to Know
- CURSOR expressions need paging off for the handler (p_items_per_page set to 0); keep such lists short, or page them yourself.
- Prefer JSON-typed values, from JSON columns or RETURNING JSON, over JSON text, which arrives as a quoted string.
- For deeply nested documents, a PL/SQL handler or a JSON relational duality view gives full control over the shape.
Related Guides
Conclusion
ORDS query handlers return nested JSON from CURSOR expressions and from JSON-typed columns. Turn paging off when you use CURSOR, return JSON values rather than JSON text, and choose a collection source type for JSON columns.
