skip to Main Content

I have a need to generate a three level nested JSON payload out of Oracle tables. I have described two approaches I have used to accomplish this and neither of them work. Please advise where am I going wrong!

Approach 1

Following select is supposed to generate a single json clob containing header, line and detail line tables. You will see that I am trying to get header, lines and distribution lines payload but its not working. I am getting header rows repeating with every line rows. So, as a result when this payload goes into a webservice, because header row is repeating, webservice will throw an error. How can I make header row not repeat with every line row?

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;

Approach 2

This approach uses Json Object and arrays. This is my preferable option but this gives Json Syntax error during runtime.

  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;

Finally here is the example payload structure I am trying to generate using above methods

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

Here are tables to produce above query as requested by @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;

Please advise.

Thank you ,
Darsh

2

Answers


  1. That first option works fine but a nested object needs its own subquery. Here is an example on the emp/dept sample dataset. It selects from dual since the outer level is a single key that has the all rows in an array, but that can also be from a table like you’re doing.

    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
        }
      ]
    }
    
    Login or Signup to reply.
  2. You need to use a GROUP BY clause when you have aggregated and non-aggregated columns. Given your structure you would want to use correlated sub-queries and JSON_ARRAY for the inner-most object (rather than JSON_ARRAYAGG, since you only have a single object and are not aggregating anything) :

    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;
    

    Which, for the sample data:

    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;
    

    Outputs:

    JSON_VALUE
    {"InvoiceNumber":543210,"InvoiceCurrency":"USD","InvoiceAmount":100,"InvoiceDate":"2023-09-05T09:07:51","BusinessUnit":"ABCD Corp","Supplier":"NASA","SupplierSite":"PHYSICAL","InvoiceGroup":"MoonLander","Description":"Some Description","invoiceLines":[{"LineNumber":1,"LineAmount":2112.75,"invoiceDistributions":[{"DistributionLineNumber":1,"DistributionLineType":"Item","DistributionAmount":2112.75}]}]}

    fiddle

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search