从Oracle SQL生成深度嵌套的JSON palyload

b1payxdu 于 2023-10-16 发布在 Oracle

关注(0)|答案(2)|浏览(117)

我需要从Oracle表中生成一个三级嵌套的JSON有效负载。我已经描述了两种方法，我用来完成这一点，他们都没有工作。请告诉我哪里错了！

方法1

下面的select应该生成一个包含header、line和detail line表的json clob。你会看到，我试图得到标题，行和分发行的有效载荷，但它不工作。我得到标题行重复与每一行。因此，当这个有效负载进入一个WebService时，由于header行是重复的，WebService将抛出一个错误。如何使标题行不与每行重复？

select json_object
(
KEY 'InvoiceNumber'   VALUE  h.document_id,
KEY 'InvoiceCurrency' VALUE  'USD',
KEY 'InvoiceAmount'   VALUE  h.amount,
KEY 'InvoiceDate'     VALUE  h.trx_date,
KEY 'BusinessUnit'    VALUE  'ABCD Corp',
KEY 'Supplier'        VALUE  'NASA',
KEY 'SupplierSite'    VALUE  'PHYSICAL',
KEY 'InvoiceGroup'    VALUE  'MoonLander',
KEY 'Description'     VALUE  'Some Description',
KEY 'invoiceLines' VALUE  json_array
       (
        json_object 
           ( KEY 'LineNumber' VALUE t.line_id,
             KEY 'LineAmount' VALUE t.line_Value,
             KEY 'invoiceDistributions' VALUE json_array 
            (
              json_object 
              (
               KEY 'DistributionLineNumber' VALUE t.line_id,
               KEY 'DistributionLineType'   VALUE 'Item',
               KEY 'DistributionAmount'     VALUE  t.line_Value
              )
            )
            ))FORMAT JSON
    )JSON_VALUE
INTO aCLOB
from XXRR_HDR_STG h,
     XXRR_LINE_STG t
where t.document_id = h.document_id
and  h.document_id = 543210
order by t.line_id;

方法2

这种方法使用Json Object和数组。这是我更喜欢的选择，但这会在运行时给JSON带来错误。

FOR i IN gethdrrec LOOP
        -- Create JSON payload
            l_json.put('InvoiceNumber',i.document_id);
            l_json.put('InvoiceCurrency','USD');
            l_json.put('InvoiceAmount',i.amount);
            l_json.put('InvoiceDate', i.trx_date);
            l_json.put('BusinessUnit', 'ABCD Corp');
            l_json.put('Supplier', 'NASA');
            l_json.put('SupplierSite','PHYSICAL');
            l_json.put('InvoiceGroup','RR');
            l_json.put('Description', 'Some Descr');
            
            FOR j IN getlnrec(i.document_id) LOOP

              l_children.append(json_object_t('
             {  
                "LineNumber": "'|| j.line_id || '",
                "LineAmount": "'|| j.line_value|| '",
             }'));
              l_grandchild.append(json_object_t('
                { "DistributionLineNumber": "'|| j.line_id || '",
                  "DistributionLineType": "Item",
                  "DistributionAmount":"'|| j.line_value|| '",
                  "DistributionCombination": "254.000.000.2111010.000.0.0"
                 }'
                ));              
            END LOOP;
         
          END LOOP;

            l_json.put('invoiceLines', l_children);
            l_json.put('invoiceDistributions',l_grandchild);
            

        envelope := l_json.to_clob;

最后，这里是我尝试使用上述方法生成的示例负载结构

{
    "InvoiceNumber": "MA_APInvoicex1",
    "InvoiceCurrency": "USD",
    "InvoiceAmount": 2212.75,
    "InvoiceDate": "2023-07-07",
    "BusinessUnit": "ABC Corp",
    "Supplier": "NASA",
    "SupplierSite": "Saint Paul",
    "InvoiceGroup": "July2023",
    "Description": "Office Supplies", 
    "invoiceLines": [{
        "LineNumber": 1,
        "LineAmount": 2112.75,
        "invoiceDistributions": [{
            "DistributionLineNumber": 1,
            "DistributionLineType": "Item",
            "DistributionAmount": 2112.75
        }]
        },
        {
            "LineNumber": 2,
            "LineType": "Freight",
            "LineAmount": 100,
            "ProrateAcrossAllItemsFlag": true,
            "invoiceDistributions": [{
                "DistributionLineNumber": 2,
                "DistributionLineType": "Item",
                "DistributionAmount": 2112.75
                 }]
        },{
            "LineNumber": 3,
            "LineType": "Freight",
            "LineAmount": 100,
            "ProrateAcrossAllItemsFlag": true,
            "invoiceDistributions": [{
                "DistributionLineNumber": 3,
                "DistributionLineType": "Item",
                "DistributionAmount": 2112.75
              }]
        }]
}

以下是根据@MT0的要求生成上述查询的表格

CREATE TABLE xxrr_hdr_stg(document_id, amount, trx_date) AS
SELECT 543210, 100, SYSDATE FROM DUAL

CREATE TABLE xxrr_line_stg(document_id, line_id, line_value) AS
SELECT 543210, 1, 2112.75 FROM DUAL;

INSERT INTO  xxrr_line_stg(document_id, line_id, line_value) 
SELECT 543210, 2, 2112.75 FROM DUAL;

INSERT INTO  xxrr_line_stg(document_id, line_id, line_value) 
SELECT 543210, 3, 2112.75 FROM DUAL;

commit;

请告知。
谢谢你，达什

oracle

来源：https://stackoverflow.com/questions/77040975/generating-deep-nested-json-palyload-out-of-oracle-sql

2条答案

按热度按时间

rbpvctlc1#

第一个选项工作正常，但嵌套对象需要自己的子查询。下面是emp/dept样本数据集的一个示例。它从dual中进行选择，因为外层是一个键，它拥有数组中的所有行，但也可以像您正在做的那样来自表。

SELECT JSON_OBJECT (
         KEY 'departments' VALUE (
           SELECT JSON_ARRAYAGG(
                    JSON_OBJECT (
                      KEY 'department_name' VALUE d.dname,
                      KEY 'department_no' VALUE d.deptno,
                      KEY 'employees' VALUE (
                        SELECT JSON_ARRAYAGG (
                                 JSON_OBJECT(
                                   KEY 'employee_number' VALUE e.empno,
                                   KEY 'employee_name' VALUE e.ename
                                 )
                               )
                        FROM   emp e
                        WHERE  e.deptno = d.deptno
                      )
                    )
                  )
           FROM   dept d
         )
       ) AS departments
FROM   dual;

{
  "departments": [
    {
      "department_name": "ACCOUNTING",
      "department_no": 10,
      "employees": [
        {
          "employee_number": 7839,
          "employee_name": "KING"
        },
        {
          "employee_number": 7782,
          "employee_name": "CLARK"
        },
        {
          "employee_number": 7934,
          "employee_name": "MILLER"
        }
      ]
    },
    {
      "department_name": "RESEARCH",
      "department_no": 20,
      "employees": [
        {
          "employee_number": 7566,
          "employee_name": "JONES"
        },
        {
          "employee_number": 7788,
          "employee_name": "SCOTT"
        },
        {
          "employee_number": 7902,
          "employee_name": "FORD"
        },
        {
          "employee_number": 7369,
          "employee_name": "SMITH"
        },
        {
          "employee_number": 7876,
          "employee_name": "ADAMS"
        }
      ]
    },
    {
      "department_name": "SALES",
      "department_no": 30,
      "employees": [
        {
          "employee_number": 7698,
          "employee_name": "BLAKE"
        },
        {
          "employee_number": 7499,
          "employee_name": "ALLEN"
        },
        {
          "employee_number": 7521,
          "employee_name": "WARD"
        },
        {
          "employee_number": 7654,
          "employee_name": "MARTIN"
        },
        {
          "employee_number": 7844,
          "employee_name": "TURNER"
        },
        {
          "employee_number": 7900,
          "employee_name": "JAMES"
        }
      ]
    },
    {
      "department_name": "OPERATIONS",
      "department_no": 40,
      "employees": null
    }
  ]
}

赞(0）回复(0）举报 2023-10-16

0sgqnhkj2#

当有聚合列和非聚合列时，需要使用GROUP BY子句。考虑到你的结构，你可能想使用相关的子查询和JSON_ARRAY来处理最内部的对象（而不是JSON_ARRAYAGG，因为你只有一个对象，没有聚合任何东西）：

SELECT JSON_OBJECT(
         KEY 'InvoiceNumber'   VALUE  h.document_id,
         KEY 'InvoiceCurrency' VALUE  'USD',
         KEY 'InvoiceAmount'   VALUE  h.amount,
         KEY 'InvoiceDate'     VALUE  h.trx_date,
         KEY 'BusinessUnit'    VALUE  'ABCD Corp',
         KEY 'Supplier'        VALUE  'NASA',
         KEY 'SupplierSite'    VALUE  'PHYSICAL',
         KEY 'InvoiceGroup'    VALUE  'MoonLander',
         KEY 'Description'     VALUE  'Some Description',
         KEY 'invoiceLines'    VALUE  (
           SELECT JSON_ARRAYAGG(
                    JSON_OBJECT(
                      KEY 'LineNumber' VALUE t.line_id,
                      KEY 'LineAmount' VALUE t.line_Value,
                      KEY 'invoiceDistributions' VALUE JSON_ARRAY(
                        JSON_OBJECT(
                          KEY 'DistributionLineNumber' VALUE t.line_id,
                          KEY 'DistributionLineType'   VALUE 'Item',
                          KEY 'DistributionAmount'     VALUE  t.line_Value
                        )
                      )
                    )
                  )
           FROM   XXRR_LINE_STG t
           WHERE  t.document_id = h.document_id
         )
       ) AS json_value
--INTO   aCLOB
FROM   XXRR_HDR_STG h
WHERE  h.document_id = 543210;

其中，对于样本数据：

CREATE TABLE xxrr_hdr_stg(document_id, amount, trx_date) AS
SELECT 543210, 100, SYSDATE FROM DUAL

CREATE TABLE xxrr_line_stg(document_id, line_id, line_value) AS
SELECT 543210, 1, 2112.75 FROM DUAL;

输出：
| JSON_VALUE|
| --|
| {“发票编号”：543210，“发票币种”：“美元”，“发票金额”：100，“发票日期”：“2023-09- 05 T09：07：51”，“业务单元”：“ABCD Corp”，“供应商”：“NASA”，“供应商站点”：“物理”，“发票组”：“MoonLander”，“说明”：“一些说明”，“发票行”：[{“LineNumber”：1，“LineAmount”：2112.75，“发票分发”：[{“DistributionLineNumber”：1，“DistributionLineType”：“Item”，“DistributionAmount”：2112.75}]}]}|
fiddle

赞(0）回复(0）举报 2023-10-16

我来回答

从Oracle SQL生成深度嵌套的JSON palyload

2条答案

相关问题

热门标签

最新问答