基于SQL将关系型查询结果格式化为指定JSON结构的技术请求

我来帮你搞定这个需求！要把关系型数据转换成你指定的嵌套JSON结构，不同的SQL数据库有不同的实现方式，我给你整理了几种常用数据库的解决方案：

先明确咱们要生成的目标JSON结构：

{
  "AFPInfo": [
    {
      "AgreementId": "100",
      "LoanAccounts": {
        "ProductGroup": "e",
        "Product": "CCOO3",
        "State": "karnataka"
      },
      "FeedbackInfo": {
        "DispositionCode": "PTP",
        "FeedbackDate": "24/7/2017"
      },
      "PaymentInfo": {
        "Receipt No": "12345",
        "ReceiptDate": "26/7/2017",
        "Amount": "2000"
      }
    },
    {
      "AgreementId": "11960600000203",
      "LoanAccounts": {
        "ProductGroup": "e",
        "Product": "CCOO3",
        "State": "karnataka"
      },
      "FeedbackInfo": {
        "DispositionCode": "..."
      }
    }
  ]
}

1. SQL Server 解决方案 #

SQL Server的FOR JSON PATH天生适合处理这种嵌套结构，它能通过列名的层级命名自动生成嵌套对象。假设我们有以下关联表：

• Agreements：存储核心的AgreementId
• LoanAccounts：关联AgreementId，存储产品相关字段
• FeedbackInfos：关联AgreementId，存储反馈信息
• PaymentInfos：关联AgreementId，存储支付凭证信息

对应的SQL查询写法：

SELECT
    a.AgreementId,
    -- 用点分隔命名，自动生成LoanAccounts嵌套对象
    la.ProductGroup AS [LoanAccounts.ProductGroup],
    la.Product AS [LoanAccounts.Product],
    la.State AS [LoanAccounts.State],
    -- 生成FeedbackInfo嵌套对象
    fi.DispositionCode AS [FeedbackInfo.DispositionCode],
    FORMAT(fi.FeedbackDate, 'dd/M/yyyy') AS [FeedbackInfo.FeedbackDate],
    -- 生成PaymentInfo嵌套对象
    pi.ReceiptNo AS [PaymentInfo.Receipt No],
    FORMAT(pi.ReceiptDate, 'dd/M/yyyy') AS [PaymentInfo.ReceiptDate],
    pi.Amount AS [PaymentInfo.Amount]
FROM Agreements a
JOIN LoanAccounts la ON a.AgreementId = la.AgreementId
JOIN FeedbackInfos fi ON a.AgreementId = fi.AgreementId
JOIN PaymentInfos pi ON a.AgreementId = pi.AgreementId
-- 指定根节点为AFPInfo，输出数组格式
FOR JSON PATH, ROOT('AFPInfo')

2. MySQL 8.0+ 解决方案 #

MySQL 8.0及以上版本支持JSON_OBJECT和JSON_ARRAYAGG组合构建嵌套JSON：

SELECT
    JSON_OBJECT(
        'AFPInfo',
        JSON_ARRAYAGG(
            JSON_OBJECT(
                'AgreementId', a.AgreementId,
                'LoanAccounts', JSON_OBJECT(
                    'ProductGroup', la.ProductGroup,
                    'Product', la.Product,
                    'State', la.State
                ),
                'FeedbackInfo', JSON_OBJECT(
                    'DispositionCode', fi.DispositionCode,
                    DATE_FORMAT(fi.FeedbackDate, '%d/%c/%Y') AS FeedbackDate
                ),
                'PaymentInfo', JSON_OBJECT(
                    'Receipt No', pi.ReceiptNo,
                    DATE_FORMAT(pi.ReceiptDate, '%d/%c/%Y') AS ReceiptDate,
                    'Amount', pi.Amount
                )
            )
        )
    ) AS result_json
FROM Agreements a
JOIN LoanAccounts la ON a.AgreementId = la.AgreementId
JOIN FeedbackInfos fi ON a.AgreementId = fi.AgreementId
JOIN PaymentInfos pi ON a.AgreementId = pi.AgreementId
GROUP BY a.AgreementId;

3. PostgreSQL 解决方案 #

PostgreSQL可以用json_build_object和json_agg实现相同效果：

SELECT
    json_build_object(
        'AFPInfo',
        json_agg(
            json_build_object(
                'AgreementId', a.agreementid,
                'LoanAccounts', json_build_object(
                    'ProductGroup', la.productgroup,
                    'Product', la.product,
                    'State', la.state
                ),
                'FeedbackInfo', json_build_object(
                    'DispositionCode', fi.dispositioncode,
                    TO_CHAR(fi.feedbackdate, 'DD/MM/YYYY') AS FeedbackDate
                ),
                'PaymentInfo', json_build_object(
                    'Receipt No', pi.receiptno,
                    TO_CHAR(pi.receiptdate, 'DD/MM/YYYY') AS ReceiptDate,
                    'Amount', pi.amount
                )
            )
        )
    ) AS result_json
FROM agreements a
JOIN loanaccounts la ON a.agreementid = la.agreementid
JOIN feedbackinfos fi ON a.agreementid = fi.agreementid
JOIN paymentinfos pi ON a.agreementid = pi.agreementid
GROUP BY a.agreementid;

小提示 #

• 如果你的数据是单宽表（所有字段在一张表），直接去掉JOIN语句，从单表取对应字段即可。
• 日期格式要注意匹配目标JSON的dd/M/yyyy格式，上面的例子已经加了对应数据库的日期格式化函数，你可以根据实际情况调整。

内容的提问来源于stack exchange，提问作者priyanka.sarkar