How to format JSON

ronald_2017

Hello All,

I want to change the following query result.

SELECT 
    JSON_SERIALIZE(
        JSON_OBJECT(
                KEY 'companyid' VALUE COMPANY_ID,
                key 'invoice' VALUE
                            JSON_OBJECTAGG(
                            'I dont want this tag' value
                                        json_object(
                                            KEY 'refNo' VALUE INVOICE_NO,
                                            key 'cheques' VALUE JSON_ARRAY(
                                                                           JSON_OBJECT(KEY 'cheque_no' VALUE CHEQUE_NO)                        
                                                                          )
                                            )           
                            RETURNING CLOB)
                RETURNING CLOB)
    RETURNING CLOB PRETTY)
FROM (
    SELECT 'A1' INVOICE_NO, '10' CHEQUE_NO, '101010' COMPANY_ID FROM DUAL
    UNION ALL
    SELECT 'A1' INVOICE_NO, '20' CHEQUE_NO, '101010' COMPANY_ID FROM DUAL
    UNION ALL
    SELECT 'A2' INVOICE_NO, '20' CHEQUE_NO, '101010' COMPANY_ID FROM DUAL
) GROUP BY COMPANY_ID;

{
  "companyid" : "101010",
  "invoice" :
  {
    "I dont want this tag" :
    {
      "refNo" : "A1",
      "cheques" :
      [
        {
          "cheque_no" : "10"
        }
      ]
    },
    "I dont want this tag" :
    {
      "refNo" : "A2",
      "cheques" :
      [
        {
          "cheque_no" : "20"
        }
      ]
    },
    "I dont want this tag" :
    {
      "refNo" : "A1",
      "cheques" :
      [
        {
          "cheque_no" : "20"
        }
      ]
    }
  }
}

I want to display below output and order by refno. I use Oracle 19c.

{
  "companyid" : "101010",
  "invoice" :
  {
      "refNo" : "A1",
      "cheques" :
      [
        {
          "cheque_no" : "10"
        }
      ],
      "refNo" : "A2",
      "cheques" :
      [
        {
          "cheque_no" : "20"
        }
      ],
      "refNo" : "A1",
      "cheques" :
      [
        {
          "cheque_no" : "20"
        }
      ]
  }
}

Thanks

Paulzip

I think you'd have to roll your own solution to the union of the repeated keys

with 
  data(invoice_no, cheque_no, company_id) as (
    select 'A1', '10', '101010' from dual union all
    select 'A1', '20', '101010' from dual union all
    select 'A2', '20', '101010' from dual
  )
, inv_items(company_id, item) as (
    select company_id
         , json_object(
             key 'refNo' value invoice_no,
             key 'cheques' value json_array(
               json_object(key 'cheque_no' value cheque_no)                        
             )
           ) 
    from data
  )
, inv_items_union(company_id, items) as (
    select
      company_id 
    , substr(
        XMLCast(
          XMLAgg(    -- XMLAgg is used in lieu of ListAgg to circumvent ListAgg's 4000 byte limit
            XMLElement(e , ','||substr(item, 2, length(item) - 1)) -- Remove outer curly braces
          )
          as clob
        ), 2
       ) -- length of ',' + 1
    from inv_items
    group by company_id    
  )
select json_object(
         key 'companyid' value company_id
       , key 'invoice' value '{'||items||'}' format json
         returning clob
       ) res
from  inv_items_union
;

RES
----------------------------------------
{"companyid":"101010","invoice":{"refNo":"A1","cheques":[{"cheque_no":"10"}]},"refNo":"A2","cheques":[{"cheque_no":"20"}]},"refNo":"A1","cheques":[{"cheque_no":"20"}]}}}
 

Notes : I'm not sure how you expect to handle multiple companies, whether as an array, or the same key duplication, so I'll leave you to tweak the solution.

Alex Nuijten

I don't think the output that you want is valid JSON (or at least that what Visual Code Studio is telling me "duplicate object key").

Wouldn't this structure be "better"?

{
   "companyid" : "101010",
   "invoices" :
   [ {
       "refNo" : "A1",
       "cheques" :
       ["10","20"]
     },
     {
       "refNo" : "A2",
       "cheques" :
       ["20"]
     }
   ]
 }

ronald_2017

https://community.oracle.com/tech/developers/discussion/comment/16821955#Comment_16821955

This is just an example. Keys can be dublicated. I just want to get desired output format. What do you suggest?

Thanks for your help.

Paulzip

Sibling keys can't be duplicates in JSON, so your source JSON is invalid ("I dont want this tag") and your result JSON is invalid too ("refNo") so wouldn't be able to be generated.

If you want a solution, you'll need to provide an example that is valid.

For example, this is valid :

{
  "companyid": "101010",
  "invoice": [
    {
      "refNo": "A1",
      "cheques": [
        {
          "cheque_no": "10"
        }
      ]
    },
    {
      "refNo": "A2",
      "cheques": [
        {
          "cheque_no": "20"
        }
      ]
    },
    {
      "refNo": "A1",
      "cheques": [
        {
          "cheque_no": "20"
        }
      ]
    }
  ]
}

User_H3J7U

@Paulzip Sibling keys can't be duplicates in JSON, so your source JSON is invalid ("I dont want this tag") and your result JSON is invalid too ("refNo") so wouldn't be able to be generated.

The oddity is not only in uniqueness, but also in the order of keys refNo+cheques.

Formally, the json is valid. ECMA-404:

6 Objects

An object structure is represented as a pair of curly bracket tokens surrounding zero or more name/value pairs. A name is a string. A single colon token follows each name, separating the name from the value. A single comma token separates a value from a following name. The JSON syntax does not impose any restrictions on the strings used as names, does not require that name strings be unique, and does not assign any significance to the ordering of name/value pairs. These are all semantic considerations that may be defined by JSON processors or in specifications defining specific uses of JSON for data interchange.

Paulzip

@User_H3J7U

As understand things there are two competing JSON standards documents.

RFC 8259 - https://www.rfc-editor.org/rfc/rfc8259

ECMA-404 - http://www.ecma-international.org/publications/files/ECMA-ST/ECMA-404.pdf

There's practically no difference between the two standards other than ECMA-404 says they are not required to be unique, but RFC 8259 says the keys should be unique. As JSON is a Key / Value data interchange format, in my eyes "Key" (in a particular object context) should infer uniqueness. But perhaps that's just me.

Most of the JSON validators I've used, raise an error on dupe keys and most JSON parsers use hash table / dictionary, so end up overwriting previous values on dupes. Hence it's more prudent to follow the RFC 8259's recommendation to ensure data integrity.

E.g.

declare
  vJO JSON_Object_T;
begin
  vJO := JSON_Object_T.parse(
  '{
    "companyid" : "101010",
    "invoice" :
    {
        "refNo" : "A1",
        "cheques" :
        [
          {
            "cheque_no" : "10"
          }
        ],
        "refNo" : "A1",
        "cheques" :
        [
          {
            "cheque_no" : "20"
          }
        ],
        "refNo" : "A1",
        "cheques" :
        [
          {
            "cheque_no" : "30"
          }
        ]
    }
  }');
  dbms_output.put_line(vJO.to_string);
end;

{"companyid":"101010","invoice":{"refNo":"A1","cheques":[{"cheque_no":"30"}]}}

You can see the overwriting occurs and the data content has changed, it doesn't matter if you move all of the refNos together followed by Cheques either.

ronald_2017

https://community.oracle.com/tech/developers/discussion/comment/16822027#Comment_16822027

I know, I just want to learn how to make the following format? How to make the output as below?

{
  "companyid" : "101010",
  "invoice" :
  {
      "refNo" : "A1",
      "cheques" :
      [
        {
          "cheque_no" : "10"
        }
      ],
      "refNo" : "A2",
      "cheques" :
      [
        {
          "cheque_no" : "20"
        }
      ],
      "refNo" : "A1",
      "cheques" :
      [
        {
          "cheque_no" : "20"
        }
      ]
  }
}

Paulzip

Are you happy to create it from scratch using JSON DB functions or did you particularly want to transform it from your original JSON source?

ronald_2017

https://community.oracle.com/tech/developers/discussion/comment/16822066#Comment_16822066

I am trying to generate a JSON format like above. The content is not important, I need to generate the format.

{
  "companyid" : "101010",
  "invoice" :
  {
      "refNo" : "A1",
      "cheques" :
      [
        {
          "cheque_no" : "10"
        }
      ],
      "refNo" : "A2",
      "cheques" :
      [
        {
          "cheque_no" : "20"
        }
      ],
      "refNo" : "A1",
      "cheques" :
      [
        {
          "cheque_no" : "20"
        }
      ]
  }
}

Paulzip

I think you'd have to roll your own solution to the union of the repeated keys

with 
  data(invoice_no, cheque_no, company_id) as (
    select 'A1', '10', '101010' from dual union all
    select 'A1', '20', '101010' from dual union all
    select 'A2', '20', '101010' from dual
  )
, inv_items(company_id, item) as (
    select company_id
         , json_object(
             key 'refNo' value invoice_no,
             key 'cheques' value json_array(
               json_object(key 'cheque_no' value cheque_no)                        
             )
           ) 
    from data
  )
, inv_items_union(company_id, items) as (
    select
      company_id 
    , substr(
        XMLCast(
          XMLAgg(    -- XMLAgg is used in lieu of ListAgg to circumvent ListAgg's 4000 byte limit
            XMLElement(e , ','||substr(item, 2, length(item) - 1)) -- Remove outer curly braces
          )
          as clob
        ), 2
       ) -- length of ',' + 1
    from inv_items
    group by company_id    
  )
select json_object(
         key 'companyid' value company_id
       , key 'invoice' value '{'||items||'}' format json
         returning clob
       ) res
from  inv_items_union
;

RES
----------------------------------------
{"companyid":"101010","invoice":{"refNo":"A1","cheques":[{"cheque_no":"10"}]},"refNo":"A2","cheques":[{"cheque_no":"20"}]},"refNo":"A1","cheques":[{"cheque_no":"20"}]}}}
 

Notes : I'm not sure how you expect to handle multiple companies, whether as an array, or the same key duplication, so I'll leave you to tweak the solution.

ronald_2017

https://community.oracle.com/tech/developers/discussion/comment/16822099#Comment_16822099

Thank you so much.