1 Reply Latest reply on Apr 1, 2020 12:49 PM by EJ-Egyed

    How to make ORDS not to escape quotes in JSON objects/tables?

    Igor Kortchnoï

      Hi all!

       

      Apparently, ORDS handles JSON arrays as simple strings , so it escapes quotes inside them.

       

      I have the following view:

       

      create or replace view v_persons

      select    prt.name

               ,json_arrayagg(chd.name order by chd.name)   dependency

      from      persons prt

      join      persons chd on chd.parent_id = prt.parent_id

      where     prt.id = prt.parent_id

      group by  prt.name

      order by  prt.name

      ;   

       

      When I do select * from v_persons, I get the correct representation:

       

      Name     Dependency

      -------- -----------------------------

      Parent1  ["Parent1"]                 

      Parent2  ["Parent2"]                 

      Parent3  ["Child1","Child2","Parent3"]

       

      However, when I use the view as a source for RESTful WS, ORDS escapes quotes in JSON tables and I get the following:

       

      {

      "items": [

      {

      "name": "Parent1",

      "dependency": "[\"Parent1\"]"

      },

      {

      "name": "Parent2",

      "dependency": "[\"Parent2\"]"

      },

      {

      "hame": "Parent3",

      "dependency": "[\"Child1\",\"Child2\",\"Parent3\"]"

      }

      ],

      ...

      Apparently, ORDS handles JSON tables as simple strings.

       

      How to make the result like the following?

       

      {

      "items": [

      {

      "name": "Parent1",

      "dependency": ["Parent1"]

      },

      {

      "name": "Parent2",

      "dependency": ["Parent2"]

      },

      {

      "name": "Parent3",

      "dependency": ["Child1","Child2","Parent3"]

      }

      ],

      ...

       

      The service handller is defined like this:

       

      begin

        ORDS.DEFINE_HANDLER(

                p_module_name         => 'test'

               ,p_pattern             => 'persons/'

               ,p_method              => 'GET'

               ,p_source_type         => ords.source_type_collection_feed

               ,p_items_per_page      => 100

               ,p_mimes_allowed       => ''

               ,p_comments            => NULL

               ,p_source => '

      select    name

               ,dependency

      from      v_persons

      ');

      commit;

      end;

      /

       

      Thank you

        • 1. Re: How to make ORDS not to escape quotes in JSON objects/tables?
          EJ-Egyed

          Using json_arrayagg in a view is what is causing the quotes to appear escaped.  Try defining your handler like this:

           

          BEGIN
              ORDS.DEFINE_HANDLER (p_module_name      => 'test',
                                   p_pattern          => 'persons/',
                                   p_method           => 'GET',
                                   p_source_type      => ords.source_type_collection_feed,
                                   p_items_per_page   => 100,
                                   p_mimes_allowed    => '',
                                   p_comments         => NULL,
                                   p_source           => '
            SELECT prt.name,
                   CURSOR (  SELECT chd.name
                               FROM persons chd
                              WHERE chd.parent_id = prt.parent_id
                           ORDER BY chd.name)    dependency
              FROM persons prt
             WHERE prt.id = prt.parent_id
          GROUP BY prt.name
          ORDER BY prt.name');
          
          
              COMMIT;
          END;
          /