3 Replies Latest reply on Nov 5, 2019 7:11 PM by Vadim Tropashko-Oracle

    Formatting JSON_OBJECT Calls

    Mikhail Velikikh

      I would like to format JSON_OBJECT calls similar to XMLATTRIBUTES or any other function calls. Here is an example to demonstrate the desired formatting:

       

      select json_object (
               key 'attr1' value 'val1',
               key 'attr2' value 'val2',
               key 'attr3' value 'val3'
             ) Json_Obj1,
             json_object (
               'attr1' value 'val1',
               'attr2' value 'val2'
             ) Json_Obj2,
             xmlelement(
               "e",
               xmlattributes(
                 'val1' as "ATTR1",
                 'val2' as "ATTR2",
                 'val3' as "ATTR3"
               ),
               'val1'
             ) Xml_El
        from Dual;
      

       

      In a nutshell:

      1. each attribute is defined on a separate line
      2. the attribute definition is indented with two spaces with relation to the start of JSON_OBJECT:
      select json_object (
               key 'attr1' value 'val1'
      

       

      I have amended the custom format for that and made the following changes:

      1. simpleIndentConditions:
      2. | [node) content & [node^) JSON_OBJECT
        
      3. _extraBrkAfter:
      4. | :breaksAfterComma &  JSON_OBJECT < node & [node) ','
      5. incrementalAlignments:
      6. | [node) JSON_OBJECT

       

      It seems to be working fine in my version of SQL Developer: 19.2.1.247

      It might be a good idea to include other JSON functions as well or adapt/rewrite the formatting rules above to handle them.

      Could you please consider including JSON_OBJECT formatting in the subsequent releases of SQL Developer?

        • 1. Re: Formatting JSON_OBJECT Calls
          Vadim Tropashko-Oracle

          simpleIndentConditions:

          ...

          | [node) regular_entry

          ;

           

          _extraBrkAfter:

          ...

          |  [node+1) aux_xml_value_expr          & [node) ','

          ;

           

          commasInProc: [node) ','

                & procedureCall < node

                & ([procedureCall) function_call | [procedureCall) function & ![procedureCall) XML_function  | [procedureCall) procedure_call)

          ;

           

          P.S. This amendment is checked in for 19.4 together with 2 boolean variables that turn it on by default. Please don't hesitate to report any issues with the  in the Arbori formatting logic in the code snippet above.

          • 2. Re: Formatting JSON_OBJECT Calls
            Mikhail Velikikh

            Thanks for that. I reset to the custom format and made the proposed changes. Here is what code looks like after that:

            select json_object (
              key 'attr1' value 'val1',
              key 'attr2' value 'val2',
              key 'attr3' value 'val3'
            ) Json_Obj1,
                  json_object (
                    'attr1' value 'val1',
                    'attr2' value 'val2'
                  ) Json_Obj2,
                  xmlelement(
                    "e",
                    xmlattributes(
                      'val1' as "ATTR1",
                      'val2' as "ATTR2",
                      'val3' as "ATTR3"
                    ),
                    'val1'
                  ) Xml_El
              from Dual;
            

            Please notice that the first term in the select list is not indented the same as the subsequent terms.

            Here is my format.arbori with all those changes made: https://tinyurl.com/yxm5goyh

            One way to achieve the desired formatting it is the following:

             

            incrementalAlignments:

            ...

            | [node) JSON_OBJECT

             

            Although this rule does the job, I understand that this rule is not generic and there might be better options to solve the same task.

            Could you please advise as to what rules I should add to those three you proposed to format the code as required? The code looks as follows after adding the incrementalAlignments line above on top of those three rules you wrote:

            select json_object (
                     'attr1' value 'val1',
                     'attr2' value 'val2'
                   ) Json_Obj2,
                   json_object (
                     key 'attr1' value 'val1',
                     key 'attr2' value 'val2',
                     key 'attr3' value 'val3'
                   ) Json_Obj1,
                   xmlelement(
                     "e",
                     xmlattributes(
                       'val1' as "ATTR1",
                       'val2' as "ATTR2",
                       'val3' as "ATTR3"
                     ),
                     'val1'
                   ) Xml_El
              from Dual;
            
            • 3. Re: Formatting JSON_OBJECT Calls
              Vadim Tropashko-Oracle

              I didn't notice you have set the "no breaks after select/from/where" option. Thank you for correction!

               

              You seem to have good grasp onto Arbori programming:-) The condition JSON_OBJECT < node in your original post solution:

               

              _extraBrkAfter:

              ...

               

               

              :breaksAfterComma &  JSON_OBJECT < node & [node) ',' 

               

              introduces one additional attribute named  JSON_OBJECT, but there is no grammar payload constraint onto it. You wanted it to be JSON_OBJECT, of course, but in Arbori you have to be explicit about it. Otherwise, the variable named JSON_OBJECT just iterates over all the ancestors of the variable node! Consequently, line breaks would be inserted everywhere after the comma -- the side effect not immediately obvious from your example.

               

              The common way to debug Arbori program is adding

               

              ->{ print(tuple) }

               

              before (or instead of) rule's trailing semicolon, and watching query output at java console. So one may guess that you would immediately see that the _extraBrkAfte rule becomes binary after implementing your initial proposal. Unfortunately, it is more subtle than that: the disjunction with other unary conditions would project away the JSON_OBJECT attribute, so that you wont see it. The justification behind this design of disjunction operation is little sophisticated, but theoretically sound.