9 Replies Latest reply on Aug 5, 2020 12:59 AM by Emu

    using apex_json.write with column of json data

    Emu

      I've been using using apex_json.write to write json for some time which has been working out great.

       

      However today the cursor to write as json had a column containing json.

       

      the result for the json column wasn't what I was after.

       

      The result was like :

       

      "INGREDIENTS":"[{\"Bread Improver\":\"1g\"},{\"Flour\":\"480g\"}]"

       

      When was hoping for something more like:

       

      "INGREDIENTS":"[{"Bread Improver":"1g"},{"Flour":"480g"}]"

       

      Is there anyway around this?  Its a solution still under construction I could save something else in the column other then json but am not sure if that woulf help...I'm hoping to avoid trying a nested table.

        • 1. Re: using apex_json.write with column of json data
          thatJeffSmith-Oracle

          If ORDS is generating the JSON, there is a way to tell ORDS that your column is already JSON...

          https://www.thatjeffsmith.com/archive/2017/09/ords-returning-raw-json/

           

          • 2. Re: using apex_json.write with column of json data
            Emu

            Hi Jeff,

            Thanks for your reply.  This is interesting to know.

             

            Unfortunately I've not been using ORDS to make the json I am using apex_json.write.  I'm needing a proc called by a GET request which doesn't seem an option via ords

             

            If I write the clob can I then htp.p out the clob?  is that likely to be bad for performance?  That maybe what I look at tomorrow.

            • 3. Re: using apex_json.write with column of json data
              Emu

              a few more hours wasted and no I cannot seem to use htp.p through ords to send out the correct json doc from a clob.

              • 4. Re: using apex_json.write with column of json data
                thatJeffSmith-Oracle

                If you want help with ORDS, you're in the right place

                 

                If you want help with apex features, you need to jump over to the apex community

                • 5. Re: using apex_json.write with column of json data
                  Emu

                  The solution that works for me is to use JSON_OBJECT to create the whole document including the data that was in the json column and serve it up with hpt.p.

                   

                  I was hoping not to have to get so manual with the json creation - and to be able to follow more closely with what I've done elsewhere but it wasn't too bad and I really have spent way to long on this already.

                   

                  Maybe Oracle will make this easier for us some year...

                  • 6. Re: using apex_json.write with column of json data
                    thatJeffSmith-Oracle

                    ORDS generates the JSON for you, you never described what you were trying to achieve with APEX_JSON

                     

                    What is it that you want to be easier?

                    • 7. Re: using apex_json.write with column of json data
                      Emu

                      Hi Jeff, Thanks for your interest.

                       

                      I couldn't see how to get ORDS to make the json I wanted it to when using a procedure to pass a cursor to ORDS.  I really needed to use a procedure.  I tried:

                       

                      1. selecting a column containing JSON.  - It didn't seem possible to get this to format correctly.
                      2. Using a cursor within a cursor to create the nested json.  - This isn't possible.
                      3. I did try using ords based on SQL - I did get the json I wanted (plus stuff I didn't need) but for other reasons couldn't use this approach.

                       

                      At this point I gave up on getting ORDS to create the json and tried:

                      1. Using the apex_json package to make the json for me and writing our via htp.p on ords.  (which is very odd maybe I'm missing something here?  I could if I saved the json to a clob , inserted into a table and then selected the json it again!  (I don't know why I had to write the clob to a table.)  This solution was too convoluted/unmaintainable for me)   BTW Using apex_json to convert a cursor to json is working well for me and is my go to approach where there isn't a cursor within a cursor requirement, partly because then I can control the output better - our clients don't expect some of the stuff ORDS adds to the output.  If we put a CDN in front of the API then the links from ORDs seem problematic?  Using apex_json works well for writing json to a table for me even in the cursor within a cursor scenario - its a shame that this only works when writing out a clob not when writing out to http.

                       

                      Finally I made the json more manually - using JSON_OBJECT, JSON_ARRAYAGG etc and nesting as needed which does seem a little primitive and possibly error prone but it worked - and then I wrote this out using htp.p (breaking the clob down into varchar2 sized chunks)

                      This got the desired result and seems reasonably performant.  (preparing the json in advance would've had performance advantages but there are be other methods I can use to get the performance desired.)uess

                       

                      I guess I'm lucky I could fall back to more primitive approaches till I could find one I could make work for me.

                       

                      Hopefully that's clear, accurate and reasonably logical!

                      • 8. Re: using apex_json.write with column of json data
                        thatJeffSmith-Oracle

                        not really

                         

                        you never actually described your issues with the procedure/cursor and the json formatting

                         

                        what did you try?

                         

                        what did you get?

                         

                        what did you need to have changed for it to be sufficient?

                        • 9. Re: using apex_json.write with column of json data
                          Emu

                          Hi Jeff,

                          Json formatting issues are the same as the formatting issues in the original post above.  Your method for selecting a json column only worked for me if the source was SQL not for a stored proc.  This wasn't a good option for me.

                           

                          Having a cursor within a cursor simply didn't work.  I posted a question about it and you answered suggesting it worked in with ORDS based on 'plain ole sql' which I took as confirmation it couldn't be done in proc.  :  cannot execute cursor with nested cursor through ords