Forum Stats

  • 3,783,678 Users
  • 2,254,819 Discussions
  • 7,880,515 Comments

Discussions

How to format JSON

ronald_2017
ronald_2017 Member Posts: 547 Blue Ribbon

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

Best Answer

  • Paulzip
    Paulzip Member Posts: 8,544 Blue Diamond
    edited Jan 14, 2022 12:46PM Accepted Answer

    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

Answers

  • Alex Nuijten
    Alex Nuijten Member Posts: 183 Silver Badge

    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
    ronald_2017 Member Posts: 547 Blue Ribbon


    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
    Paulzip Member Posts: 8,544 Blue Diamond
    edited Jan 14, 2022 12:46AM

    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
    User_H3J7U Member Posts: 833 Gold Trophy

    @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
    Paulzip Member Posts: 8,544 Blue Diamond
    edited Jan 14, 2022 2:46AM

    @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
    ronald_2017 Member Posts: 547 Blue Ribbon

    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
    Paulzip Member Posts: 8,544 Blue Diamond

    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
    ronald_2017 Member Posts: 547 Blue Ribbon

    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
    Paulzip Member Posts: 8,544 Blue Diamond
    edited Jan 14, 2022 12:46PM Accepted Answer

    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