Updated 19 May 2022
ORDS tries to be nice, any chance we have to save you time, we will try to do things for you.
Example: ORDS auto-formats your SQL or PL/SQL results and response to JSON before returning to your client or application.
But what if you’re response is ALREADY in JSON?
Like this:
Not so good…escaped JSON response
So if you create a REST API that simply does a SELECT on the TABLE, you’ll get something like this back from ORDS when you call it:
ORDS trying to convert JSON to JSON
Better – give ORDS a heads-up that JSON is already in the Response
Yes, you might need to tell ORDS that one of the columns in the result set is already a JSON document. What ORDS knows from the JDBC driver is that it’s got an incoming CLOB document.
But there’s nothing to tell us that CLOB also has JSON in it.
Yes, there COULD be a check-constraint there ‘IS JSON’ – but for us to know that, we’d have to look for it on all the calls involving that table.column.
THAT would be….expensive. So we don’t do it.
In this case, the ENTIRE response is a JSON document. So I’m going to use the Media Resource Source Type, and include the Mime type in the SELECT for the GET handler.
select 'application/json', json_column
from the_table
Go here to learn more about the MEDIA Resource Type…
The Media resource in this case is application/json – and we’re going to let the browser handle that, just like it would a BLOB and a PDF. We just need to include the heads-up notice saying what it actually is, so adjust your GET query text to include ‘application/json’ before you include the JSON itself.
Better?
What if you like all the nice things ORDS does for your query results? Like the paging. And what if only one or more of your columns is {json} and the remaining still need to be converted? We have a solution for that as well.
Give your JSON column this alias
"{}jsons"The query for my GET Handler is –
SELECT id, jsons "{}jsons" FROM table_with_json
The ID column is a number. But the second column is JSON data I’m storing in the database. As soon as I alias it using the write magic word, then ORDS will let it pass through unmolested. You’ll see it also in as a nested JSON document.
But Jeff, I’m using PL/SQL, not SQL.
Then just PRINT the JSON, with HTP.P. A better example here, perhaps.
Can’t this be any better?
Yes! With 21c, we have a NATIVE JSON data type. That means you can create a table with a table of data type ‘JSON’ and the JDBC driver supports that fully.
So when I build my REST APIs that return JSON docs from columns stored as JSON data type in a 21c table, ORDS handles it correctly.
34 Comments
Hi,
when I output Json in this way the Open API Documentation is wrong. It doese not show the props from the nested Json Object. Is there a way to fix this?
You’ll have to manually edit the JSON for the openapi spec – there’s no way for ords to know what your JSON looks like.
Hi,
the {} is a great little hidden feature that we use all the time.
But I was wondering if I have raw json, that contains an array of strings, is it possible to query the elements therein using the builtin in URL Query features of ORDS?
SELECT id,
jsons “{}jsons”
FROM table_with_json
{“id”: 1 “jsons”: [“a”, “b”, “c”]}
Something like ?q={“jsons”:{“$in”:”a”} }, to only get the items that contain “a”.
Currently we are building are own query for such use cases, but it looks a bit ugly when some elements of the query are separate from the normal “q=…” JSON structure.
Mikkel
In 23c when you have a native JSON SQL type, the {} trick is no longer required 🙂
Onto your question…have you looked at the SODA for REST side of the house? It looks like you’re storing JSON docs, and you want to use filters to only get specific docs? There’s a bigger/more direct way of filtering here, I think.
Hi Jeff, what would be the best way when having a function returning JSON_OBJECT_T?
You get today’s Paul, let’s continue the discussion over here.
I am using ORDS 3.0.3.351.13.24
{}jsons is not working for me, is this only after some specific version ?
ords.define_template(p_module_name => ‘paybc’
,p_pattern => ‘search/orders/:prefnumber/’
,p_comments => ”);
ords.define_handler(p_module_name => ‘paybc’
,p_pattern => ‘search/orders/:prefnumber/’
,p_method => ‘GET’
,p_source_type => ORDS.source_type_collection_feed
,p_source => ‘SELECT DTL.PBCRN_REF_NBR “paybc_reference_number”,
DTL.TXN_MASTER_IDX “order_id”,
PRN.PBSRN_CODE “form_type”,
TO_CHAR (MAS.TXN_TIMESTAMP, ”DD-MON-YYYY HH24:MI:SS”) “date”,
GEN.TXN_JSON “{}sections”,
utl_url.escape(DTL.TXN_MASTER_IDX ||”/”) “$sections_json”,
null “payment_method”,
DTL.DTL_INVOICE_NBR “invoice_number”,
PAY.RCPT_NO “receipt_number”,
PAY.PAYMENT_AMT_PAID “amount”,
”CAD” “currency”,
PAY.PAYMENT_STATUS “status”
FROM PRTL_DB.CAS_PBCP_TXN_DETAIL DTL,
PRTL_DB.CAS_PBCP_REF_NBR PRN,
PRTL_DB.CAS_PBCP_TXN_MASTER MAS,
PRTL_DB.CAS_PBCP_TXN_GENERIC GEN,
PRTL_DB.CAS_PBCP_TXN_PAYMENT PAY
WHERE PRN.PBCRN_REF_NBR = DTL.PBCRN_REF_NBR
AND MAS.TXN_MASTER_ID = DTL.TXN_MASTER_IDX
AND GEN.TXN_DETAIL_ID = DTL.TXN_DETAIL_ID
AND PAY.TXN_MASTER_ID = DTL.TXN_MASTER_IDX
AND PRN.PBCRN_REF_NBR= :prefnumber
AND DTL.TXN_MASTER_IDX = NVL ( :order_id, DTL.TXN_MASTER_IDX)
AND PAY.RCPT_NO = NVL ( :receipt_number, PAY.RCPT_NO)
AND To_DATE( TO_CHAR ( MAS.TXN_TIMESTAMP, ”DD-MON-YYYY HH24:MI:SS”) , ”DD-MON-YYYY HH24:MI:SS”)
between NVL(TO_DATE(:from_date, ”DD-MON-YYYY HH24:MI:SS”), MAS.TXN_TIMESTAMP ) AND NVL(TO_DATE(:to_date, ”DD-MON-YYYY HH24:MI:SS”), MAS.TXN_TIMESTAMP )’
,p_items_per_page => 25);
{
“items”: [
{
“paybc_reference_number”: “10011”,
“order_id”: 1002374,
“form_type”: “LsbSFD”,
“date”: “29-SEP-2021 10:19:07”,
“{}sections”: “{\”instructions\”:{\”readInstructions\”:true},\”applicationContactInformation\”:{\”firstName\”:\”Sujan\”,\”lastName\”:\”Pirani\”,\”phone\”:\”2508847103\”,\”organisation\”:\”Test Company\”,\”email\”:\”[email protected]\”},\”informationReview\”:{\”confirmReview\”:true},\”paymentOptions\”:{\”orderNumber\”:\”\”,\”paymentMethod\”:\”online\”,\”receiptNumber\”:\”\”,\”txnAmount\”:100},\”returnMailingAddress\”:{\”firstName\”:\”Sujan1\”,\”lastName\”:\”Pirani1\”}}”,
“payment_method”: null,
“invoice_number”: “MC35932690”,
“receipt_number”: “PYBCCCTEST”,
“amount”: 100,
“currency”: “CAD”,
“status”: “PAID”,
“links”: [
{
“rel”: “sections_json”,
“href”: “https://cfs-dev.cas.gov.bc.ca:7025/ords/cas/pbc/search/orders/10011/1002374/”
}
]
},
Version 3.0.3 is too old me for me to speculate/think about – you most definitely need to upgrade, not matter what. It’s more than 5 years old.
i Jeff
i was following your article as i have same problem of exposing json in GET
but for some reason my code is not working any idea
ORDS: How to Return RAW JSON Content for Your REST APIs
https://www.thatjeffsmith.com/archive/2017/09/ords-returning-raw-json/
Oracle REST Data Services happily converts your Oracle results to JSON for your REST applications. But what if your results are already JSON? Media resource!
BEGIN
ords.define_template(p_module_name => ‘paybc’
,p_pattern => ‘search/orders/:prefnumber’
,p_comments => ”);
ords.define_handler(p_module_name => ‘paybc’
,p_pattern => ‘search/orders/:prefnumber’
,p_method => ‘GET’
,p_source_type => ORDS.source_type_collection_feed
,p_source => ‘SELECT
1 id,
GEN.TXN_JSON “{}json”
FROM
PRTL_DB.CAS_PBCP_TXN_GENERIC GEN
WHERE 1 =1 ‘
,p_items_per_page => 25);
COMMIT;
END;
/
OUTPU
{
“items”: [
{
“id”: 1,
“{}json”: “{\”instructions\”:{\”readInstructions\”:true},\”applicationContactInformation\”:{\”firstName\”:\”Satbir\”,\”lastName\”:\”Singh\”,\”phone\”:\”4313883833\”,\”organisation\”:\”\”,\”email\”:\”[email protected]\”},\”informationReview\”:{\”confirmReview\”:true},\”paymentOptions\”:{\”orderNumber\”:\”\”,\”paymentMethod\”:\”online\”,\”receiptNumber\”:\”\”,\”txnAmount\”:100},\”returnMailingAddress\”:{\”firstName\”:\”Satbir\”,\”lastName\”:\”Singh\”}}”
},
{
“id”: 1,
“{}json”: “{\”instructions\”:{\”readInstructions\”:true},\”applicationContactInformation\”:{\”firstName\”:\”Sujan\”,\”lastName\”:\”Pirani\”,\”phone\”:\”2508847103\”,\”organisation\”:\”Test Company\”,\”email\”:\”[email protected]\”},\”informationReview\”:{\”confirmReview\”:true},\”paymentOptions\”:{\”orderNumber\”:\”\”,\”paymentMethod\”:\”online\”,\”receiptNumber\”:\”\”,\”txnAmount\”:100},\”returnMailingAddress\”:{\”firstName\”:\”Sujan1\”,\”lastName\”:\”Pirani1\”}}”
},
{
“id”: 1,
“{}json”: “{\”instructions\”:{\”readInstructions\”:true},\”applicationContactInformation\”:{\”firstName\”:\”Sujan\”,\”lastName\”:\”Pirani\”,\”phone\”:\”2508847103\”,\”organisation\”:\”Test Company\”,\”email\”:\”[email protected]\”},\”informationReview\”:{\”confirmReview\”:true},\”paymentOptions\”:{\”orderNumber\”:\”\”,\”paymentMethod\”:\”online\”,\”receiptNumber\”:\”\”,\”txnAmount\”:100},\”returnMailingAddress\”:{\”firstName\”:\”Sujan1\”,\”lastName\”:\”Pirani1\”}}”
}
],
“hasMore”: false,
“limit”: 25,
“offset”: 0,
“count”: 3,
“links”: [
{
“rel”: “self”,
“href”: “https://cfs-dev.cas.gov.bc.ca:7025/ords/cas/paybc/search/orders/10011”
},
{
“rel”: “describedby”,
“href”: “https://cfs-dev.cas.gov.bc.ca:7025/ords/cas/metadata-catalog/paybc/search/orders/item”
},
{
“rel”: “first”,
“href”: “https://cfs-dev.cas.gov.bc.ca:7025/ords/cas/paybc/search/orders/10011”
}
]
}
Hi Jeff,
Is there a way to disable the conversion to lowercase of columns.
eg.
select camel_case_col as “camelCase” …. from some_table
is always converted to { “camelcase” : “value”, ….
Within a json column created with JSON_OBJECT(‘otherCamelCaseColumn’ value .. returning clob) as ‘{}json’ camelCase is preserved.
Thanks, Herman
Yes, generate the JSON yourself. Our JSON standard requires lowercase attribute names.
Jeff, this is a huge help. I’ve been trying to generate nested JSON using an Apex Collection Query for days. I wasn’t aware of the “{}blah” alias; is it documented anywhere (at Oracle)?
Hi Jeff,
Thanks for the article
Which ORDS version is required to use the below statement. My current ORDS version is 19.2.0
SELECT id,
jsons “{}jsons”
FROM table_with_json
Thanks,
Arun
Well, I wrote that post in 2017, but it’s been that way for even longer probably.
What’s happening when you try this?
19.2 is old, recommend you give 21.2 a chance next month – it’ll be much faster 🙂
Thanks for this article Jeff. The issue of returning json and non-json data in an API has been driving me crazy all week.
Hi,
So if I have raw JSON and I want to return it in a procedure OUT parameter, I will do the following:
-Define the parameter: CUR OUT SYS_REFCURSOR
-Open the cursor: OPEN CUR FOR SELECT RAW_JSON “{}RESULT” FROM DUAL;
The resulting JSON returned by ORDS will look like the following:
“cur”: {
“result”:{
raw_json
}
}
Is this correct? Anyway I can omit CUR or RESULT key since it is redundant?
i wouldn’t use a cursor I don’t think…if we see a refcursor, we’re going to take that content and format it for JSON.
I would use HTP.P and have the content sent to your client as a file or stream…see what I did in the 3rd scenario here.
how to send OUT parameter with JSON_OBJECT_T object type in a POST ORDS service?
i try to convert it to STRING using .stringify, but the ORDS always give an output with \”, meanwhile if i try to hit my script at the Stored Procedure, the json to string format is correct.
Very helpful[l, thanks Jeff.
So what happens if you have a “JSON” column, and other non-JSON columns being returned in the same query? It would be better if there was a specific JSON column type so that ORDS would know how to format the results. Is there any way of doing that?
In your query..
Select json_col “{}col”…
This tells ords that this column is already json and not to transform it.
Hello,
What changes in the case of a post handler? I mean in case I want to return from a stored procedure a json output via a clob, how should my post handler be defined ?
thanks in advance,
Anisa
Hi Jeff,
What if your are using PL/SQL for your JSON response, the media resource is not available in that type. I am facing the same problem. and I am getting the JSON response escaped, converting the JSON to a JSON.
Regards,
Omar
What is your handler doing? The only fix I know of is to find a way to do it with SQL.
Hello Jeff, isn’t it possible to return the contents of a table/query as a xml, instead of json/csv?
Thank you.
You’d have to build the XML output yourself and return it as RAW data
Thanks for the help Jeff. It worked like a charm 🙂
Same problem here, we moved on from “standard” GET and we’re using the SYS_REFCURSOR as response with POST handler.
It looks pretty when you’re using a direct query inside your PL/SQL but… when you’ve a web-hook and you get a huge JSON response from the second one, you’re not able to “forward” it.
I got back to the GET handler using “Media resource” and “application/json”, changing the PL/SQL from a stored procedure to a stored function.
Would be better having a CLOB in the response type in ORDS (at the moment is missing).
Hi,
The problem with this method is the inability to use ORDS to generate URI’s. REST API conventions call for URI’s to identify objects that can be retrieved, and this only appears to be an ORDS facility if it generates the JSON itself.
I would really like to see ORDS support a hybrid mechanism that allowed JSON elements to be returned as columns from the query, so that URI generation and other ORDS features could be mixed with both 12c calls, and PL/SQL functions that returned complex objects.
For instance we have a system that has an entity CONTRACTS, however, it is heavily sub typed (more than 20, with two levels of sub types). I can generate all the optional components from PL/SQL easily enough, but there is no way to incorporate into the query, so it has to do a total replacement, and that causes an immediate issue for URI generation, forcing me to do them statically, not truly dynamically, which gives problems with testing.
How about an example of what you’re after?
If I use a SELECT I can have paths modified by ORDS. As a simple case, start with my Contract header:
SELECT id “$uri”, ‘../customers/’||cust_id “$customer, contract_type, name, start_date, …
FROM contracts
WHERE id = :id;
Gives my links and the common contract details.
However, there are variable details depending on contract_type. But there is no way to provide a variable number of columns in the query. Being able to return a string with the appropriate JSON pairs for those conditional columns would be a real advantage.
But its not just conditional columns, its also variations for embedded objects.
All contracts have lines, so I can add those in with CURSOR(SELECT product, unit_price, percentage FROM contract lines WHERE contract_id = :id), but variance contracts have volume breaks below contract lines, and there is no way to include those conditionally. I would prefer to call a PL/SQL function instead of the CURSOR that returned the JSON object for the lines, so I could handle all the various contract types.
At the moment what I have is just a PL/SQL procedure that produces the JSON, but building the URI’s was a problem.
If that doesn’t describe it adequately I can put all the details into a document and email it to you.
If you have a stored procedure, why can’t you code the query specific to the value of CONTRACT_TYPE?
Hi Jeff,
Clearly I am not explaining myself well.
I like ORDS. I find the ease of constructing queries to retrieve data a major feature.
When we started our REST API we focused on the GET handlers to flesh out our thoughts and had about 30 built very quickly.
That’s how we discovered the difficulties with deep structures, and sub types.
For ones like contracts we have had to write them entirely in PL/SQL, when I would have preferred to just create individual pieces in PL/SQL to populate the complex parts.
Once we went to PL/SQL for the entire output, we had the problem of correctly generating URI’s to embed, something that is done for us by ORDS in a query. That was somewhat difficult, and required understanding that the components can be retrieved with OWA_UTIL.
That’s really why I say it would be an advantage to be able to retrieve JSON components as column data, both as simple pairs to optionally include columns, and as an embedded JSON object.
You already use $ as the first character in a column name to identify columns that need path processing, you could continue that with say # to identify a column that already contains JSON.
My goal would be to minimize the use of PL/SQL, but if that is not a direction you want to go, then providing support to PL/SQL to access the same facilities would be second best.
Ok, thanks for sticking with me, I understand now 🙂
I think you have an excellent idea for an ER, where we could pull out the $ from an OUT param and know to apply the URI magic to it just like we do for SQL.