Background

I have created a REST endpoint in Oracle Integration Cloud that takes an id as a template parameter (ex: http://endpoint.com/api/orders/{id}), calls a stored procedure in MySQL (passing in the same id parameter) which returns data, does a transformation then returns that data as JSON in the response payload.

The tool is somewhat restrictive and it would make my life easier if the stored procedure could transform and output the query result as JSON (essentially using MySQL's new JSON_OBJECT, JSON_ARRAY, etc functions). It really just needs to be in proper JSON form (IE the actual content type of the response from MySQL doesn't matter).

I've done my best to be as thorough as possible to avoid confusion / a back and forth discussion in the comments, however if this example is two long for you skip to the last section which will likely suffice.

Outline of Current Process

The response payload has the following JSON Schema

{
  "$id": "http://example.com/example.json",
  "type": "object",
  "definitions": {},
  "$schema": "http://json-schema.org/draft-07/schema#",
  "properties": {
    "results": {
      "$id": "/properties/results",
      "type": "array",
      "items": {
        "$id": "/properties/results/items",
        "type": "object",
        "properties": {
          "requestId": {
            "$id": "/properties/results/items/properties/requestId",
            "type": "string",
            "title": "The Requestid Schema ",
            "default": "",
            "examples": [
              "id"
            ]
          },
          "invoiceNumber": {
            "$id": "/properties/results/items/properties/invoiceNumber",
            "type": "string",
            "title": "The Invoicenumber Schema ",
            "default": "",
            "examples": [
              "129"
            ]
          },
          "orderNumber": {
            "$id": "/properties/results/items/properties/orderNumber",
            "type": "string",
            "title": "The Ordernumber Schema ",
            "default": "",
            "examples": [
              "13"
            ]
          }
        }
      }
    }
  }
}

Here is an example response payload

{
  "results": [
    {
      "requestId": "2018-CRD-1818",
      "invoiceNumber": "120129",
      "orderNumber": "1343"
    },
    {
      "requestId": "2018-RMA-1891",
      "invoiceNumber": "1291234
      "orderNumber": "1323"
    }
  ]
}

Currently, I have a stored procedure that basically condenses multiple rows into a single, comma separated row and then returns the result to Oracle's Integration Cloud.

 DELIMITER //
 CREATE PROCEDURE GetOrderNumbers(IN id int, OUT ids varchar(4000))
   BEGIN

        SELECT 
                group_concat(concat(crd.requestId, ',', crd.originalOrderNumber))
            FROM 
                LathamCR_CreditRequest crd
            WHERE 
                crd.originalOrderNumber = id
            UNION ALL
            SELECT 
                rma.requestId
            FROM 
                LathamCR_RMARequest rma
            WHERE 
                rma.originalOrderNumber = id
            LIMIT 1
        INTO ids;
    END //
DELIMITER ;

Then in OIC I break transform that csv into the aforementioned JSON and return it.

New Process and Question

As stated before, instead of returning a single row CSV from the stored procedure I want to return a JSON Object (essentially doing the transformation in MySQL). So the stored procedure signature would be as follows

 DELIMITER //
 CREATE PROCEDURE GetOrderNumbers(IN id int, OUT ids JSON)
   BEGIN

         # The SQL Transformation

    END //
DELIMITER ;

The issue I'm having is that I cannot seem to condense multiple rows into a single JSON Object. I can condense every row into a JSON Object using the following:

SELECT
    JSON_OBJECT('requestId',crd.requestId, 'invoiceNumber', crd.originalOrderInvoice, 'orderNumber', crd.originalOrderNumber)
FROM 
    LathamCR_CreditRequest crd
UNION ALL
SELECT 
    JSON_OBJECT('requestId',rma.requestId, 'invoiceNumber', rma.originalOrderInvoice, 'orderNumber', rma.originalOrderNumber)
FROM
    LathamCR_RMARequest rma;

This returns the following result set (where each new line is a row).

# results
'{\"requestId\": \"2015-CRD-0000001\", \"orderNumber\": \"35454\", \"invoiceNumber\": \"3654654\"}'
'{\"requestId\": \"2015-CRD-0000002\", \"orderNumber\": \"4343434\", \"invoiceNumber\": \"3243434\"}'
'{\"requestId\": \"2015-CRD-0000003\", \"orderNumber\": \"3423423\", \"invoiceNumber\": \"2342342\"}'
'{\"requestId\": \"2015-CRD-0000004\", \"orderNumber\": \"3543543\", \"invoiceNumber\": \"9766483\"}'
'{\"requestId\": \"2015-CRD-0000005\", \"orderNumber\": \"8754632\", \"invoiceNumber\": \"**77732\"}'

This is exactly what I want, except I want to return all of that in one row as an array inside a parent object. For example:

{ 
   "results": [
      {"requestId": "2015-CRD-0000005", "orderNumber": "8754632", "invoiceNumber": "**77732"}, 
      {"requestId": "2016-CRD-0000005", "orderNumber": "866632", "invoiceNumber": "*6732"}
    ]
}

So to attempt to get it in one row I used GROUP_CONCAT and JSON_ARRAY as follows:

SELECT
    GROUP_CONCAT(
        JSON_ARRAY (
            JSON_OBJECT('requestId',crd.requestId, 'invoiceNumber', crd.originalOrderInvoice, 'orderNumber', crd.originalOrderNumber)
        )
    ) as result
FROM 
    LathamCR_CreditRequest crd
UNION ALL
SELECT 
    GROUP_CONCAT(
        JSON_ARRAY (
            JSON_OBJECT('requestId',rma.requestId, 'invoiceNumber', rma.createdDate, 'orderNumber', rma.originalOrderNumber)
        )
    ) as result
FROM
    LathamCR_RMARequest rma;

This is very close but it returns two rows (union all essentially stops working) and also I still need to wrap the array in a parent JSON Object. When I try to use JSON_OBJECT as the outer most block it then treats the array like a string (and I still can't get the union all to work again).