How to get JSON response from a teiid REST service

Question

I am issuing a SQL SELECT query against a MySQL data source from teiid and trying to get the SQL response in the JSON format. I was able to get the response in the XML format but having trouble when I'am trying to get the response in JSON format. it seems that there are not equivalent JSON functions to XMLELEMENT, XMLAGG, XMLFOREST XML functions. I have posted the XML version of my query bellow. What are the equivalent JSON version of this query.

CREATE VIRTUAL PROCEDURE GetFlightRecordsByAirDelay1(IN p1 integer, IN p2 integer) RETURNS (xml_out xml) OPTIONS (UPDATECOUNT 0, "REST:METHOD" 'GET', "REST:URI" 'GetFlightRecordsByAirDelay1') AS /*+ cache(pref_mem ttl:14400000) */ BEGIN SELECT XMLELEMENT("FlightDelayRecords",
XMLAGG(XMLELEMENT("FlightDelayRecord", XMLFOREST(UniqueCarrier,FlightNum, CRSDepTime, DepTime, CRSArrTime, ArrDelay) )) ) as xml_out FROM (SELECT UniqueCarrier,FlightNum, CRSDepTime, DepTime, CRSArrTime,
ArrDelay FROM flight_records_mod1 WHERE flight_records_mod1.ArrDelay < p1 AND flight_records_mod1.YEAR = p2 LIMIT 10) A;
END

score 0 · Accepted Answer · edited Nov 22 '17 at 00:33

It can be simpler to return json. To produce a result like:

{"FlightDelayRecords":[{"UniqueCarrier":...},...]}

Use

CREATE VIRTUAL PROCEDURE GetFlightRecordsByAirDelay1(IN p1 integer, IN p2 integer) 
RETURNS (clob json) OPTIONS (UPDATECOUNT 0, "REST:METHOD" 'GET', "REST:URI" 'GetFlightRecordsByAirDelay1') AS /*+ cache(pref_mem ttl:14400000) */ 
BEGIN 
SELECT JSONOBJECT(JSONARRAY_AGG(JSONOBJECT(UniqueCarrier,FlightNum, CRSDepTime, DepTime, CRSArrTime, ArrDelay)) as "FlightDelayRecords") as json FROM (SELECT UniqueCarrier,FlightNum, CRSDepTime, DepTime, CRSArrTime,
ArrDelay FROM flight_records_mod1 WHERE flight_records_mod1.ArrDelay < p1 AND flight_records_mod1.YEAR = p2 LIMIT 10) A;
END

How to get JSON response from a teiid REST service

1 Answers1