mysql - 将 MySQL 结果集转换为单个 JSON OBJECT(MySQL 数据类型)

Question

背景

我在 Oracle Integration Cloud 中创建了一个 REST 端点，它将 id 作为模板参数(例如:http://endpoint.com/api/orders/{id})，调用 MySQL 中的存储过程(传入相同的 id 参数)，该过程返回数据，进行转换，然后在响应负载中将该数据作为 JSON 返回。

该工具有些限制，如果存储过程可以将查询结果转换并输出为 JSON(主要使用 MySQL 的新 JSON_OBJECT、JSON_ARRAY等函数)。它实际上只需要采用正确的 JSON 格式(即 MySQL 响应的实际内容类型并不重要)。

我已尽力做到尽可能详尽，以避免混淆/在评论中来回讨论，但是如果这个示例对您来说有两个长的部分，请跳至最后一节，这可能就足够了。

当前流程概要

响应负载具有以下 JSON 模式

{
  "$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"
            ]
          }
        }
      }
    }
  }
}

这是一个示例响应负载

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

目前，我有一个存储过程，它基本上将多行压缩成一个逗号分隔的行，然后将结果返回到 Oracle 的 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 ;

然后在 OIC 中，我将该 csv 转换为上述 JSON 并将其返回。

新流程和问题

如前所述，我不想从存储过程返回单行 CSV，而是返回一个 JSON 对象(本质上是在 MySQL 中进行转换)。所以存储过程签名如下

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

         # The SQL Transformation

    END //
DELIMITER ;

我遇到的问题是我似乎无法将多行压缩到一个 JSON 对象中。我可以使用以下方法将每一行压缩成一个 JSON 对象:

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;

这将返回以下结果集(其中每个新行都是一行)。

# 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\"}'

这正是我想要的，除了我想在一行中将所有这些作为父对象内的数组返回。例如:

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

因此，为了尝试将其排成一行，我使用了 GROUP_CONCAT 和 JSON_ARRAY，如下所示:

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;

这非常接近，但它返回两行(union all 基本上停止工作)而且我仍然需要将数组包装在父 JSON 对象中。当我尝试使用 JSON_OBJECT 作为最外层的 block 时，它会将数组视为字符串(而且我仍然无法让 union all 再次工作)。

Answer 1

一种选择是使用 JSON_MERGE() (已弃用 5.7.22) --> JSON_MERGE_PATCH()/JSON_MERGE_PRESERVE() , 请参阅 12.16.1 JSON Function Reference .

SET @`json_LathamCR_CreditRequest` :=
  (SELECT
     CONCAT(
       '[',
       GROUP_CONCAT(
         JSON_OBJECT('requestId', `requestId`)
       ),
       ']')
  FROM `LathamCR_CreditRequest`);

SET @`json_LathamCR_RMARequest` :=
  (SELECT
     CONCAT(
       '[',
       GROUP_CONCAT(
         JSON_OBJECT('requestId', `requestId`)
       ),
       ']')
  FROM `LathamCR_RMARequest`);

SELECT
  `der`.`result`,
  JSON_VALID(`der`.`result`) `JSON_VALID?`
FROM (
  SELECT
    JSON_OBJECT(
      'results',
      JSON_MERGE(@`json_LathamCR_CreditRequest`,
                 @`json_LathamCR_RMARequest`
      )
  ) `result`
) `der`;

参见 db-fiddle .