Cannot create a simple JSON using PLSQLAP_Document_API

I have the same issue. I have solved it by manually deleting the “outer json node” after generating the json.

There is furthermore the problem, that it is not possible to remove aggregation level. In the to_xml method it can be set to FALSE, but in to_json there is no such option.

I had to make a copy of the to_json method and implement it myself.

Problem with aggregation level:

   Plsqlap_Document_API.Clear(doc_);
   doc_ := Plsqlap_Document_API.New_Document('MAIN_DOC');   
   
   Plsqlap_Document_API.Add_Attribute(doc_, 'SomeElement', 'Honey', null);
   
   array_id_ := Plsqlap_Document_API.Add_Array(doc_, 'Array', null);
   
   FOR i IN 1..3 LOOP
      array_elem_id_ := Plsqlap_Document_API.Add_Document(doc_, 'ArrayElement' , array_id_);
      Plsqlap_Document_API.Add_Attribute(doc_, 'ArrayElement', i, array_elem_id_);
   END LOOP;
   
   Plsqlap_Document_API.To_Json(json_, doc_);

The Result is:

{
  "MAIN_DOC" :
  {
    "SomeElement" : "Honey",
    "Array" :
    [
      {
        "ArrayElement" :
        {
          "SomeElementInArray" : 1
        }
      },
      {
        "ArrayElement" :
        {
          "SomeElementInArray" : 2
        }
      },
      {
        "ArrayElement" :
        {
          "SomeElementInArray" : 3
        }
      }
    ]
  }
}

But the receiving REST API needs it like this:

{
  "SomeElement" : "Honey",
  "Array" :
  [
    {
      "SomeElementInArray" : 1
    },
    {
      "SomeElementInArray" : 2
    },
    {
      "SomeElementInArray" : 3
    }
  ]
}

I have copied/modified the to_json method to get rid of of the “ArrayElement” Objects:

PROCEDURE To_Json___ (
   json_          OUT CLOB,
   main_          IN  Plsqlap_Document_API.Document,
   agg_level_     IN  BOOLEAN    DEFAULT TRUE,
   indent_        IN  NUMBER  DEFAULT NULL,
   use_crlf_      IN  BOOLEAN DEFAULT FALSE)
IS
   nl_   VARCHAR2(2);
   buf_  VARCHAR2(32767);

   PROCEDURE Append(str_ VARCHAR2) IS
   BEGIN
      buf_ := buf_ || str_;
   EXCEPTION
      WHEN value_error THEN
         Dbms_Lob.WriteAppend(json_, length(buf_), buf_);
         buf_ := str_;
   END;

   FUNCTION Encode_Clob_Value (value_ IN CLOB) RETURN CLOB IS
   BEGIN
      RETURN replace(replace(replace(replace(replace(replace(replace(value_,chr(8),'\b'),chr(9),'\t'),chr(10),'\n'),chr(12),'\f'),chr(13),'\r'),chr(34),'\"'),chr(92),'\\');
   END;

   FUNCTION Encode_Text_Value (value_ IN VARCHAR2) RETURN VARCHAR2 IS
   BEGIN
      RETURN replace(replace(replace(replace(replace(replace(replace(value_,chr(8),'\b'),chr(9),'\t'),chr(10),'\n'),chr(12),'\f'),chr(13),'\r'),'\','\\'),chr(34),'\"');
   END;

   PROCEDURE Append_Element (element_ IN Plsqlap_Document_API.Element, level_ IN NUMBER) IS
      cnt_  BINARY_INTEGER := element_.children.count;
      name_ VARCHAR2(4000) := element_.name;
      quot_ VARCHAR2(1);

      PROCEDURE Append_Clob_Value (value_ CLOB) IS
      BEGIN
         Append(rpad(' ', level_)||'"'||name_||'":'||quot_);
         Dbms_Lob.WriteAppend(json_, length(buf_), buf_);
         Dbms_Lob.Append(json_, CASE element_.type = Plsqlap_Document_API.TYPE_BINARY WHEN TRUE THEN value_ ELSE Encode_Clob_Value(value_) END);
         buf_ := quot_;
      END;

   BEGIN
      IF element_.type NOT IN (Plsqlap_Document_API.TYPE_COMPOUND, Plsqlap_Document_API.TYPE_DOCUMENT) THEN -- simple attribute
         IF element_.type IN (Plsqlap_Document_API.TYPE_INTEGER, Plsqlap_Document_API.TYPE_FLOAT, Plsqlap_Document_API.TYPE_BOOLEAN) THEN
            quot_ := '';
         ELSE
            quot_ := '"';
         END IF;
         IF element_.type = Plsqlap_Document_API.TYPE_BOOLEAN THEN
            Append(rpad(' ', level_)||'"'||name_||'":'||lower(element_.value));
         ELSE
            IF element_.value IS NOT NULL THEN
               BEGIN
                  Append(rpad(' ', level_)||'"'||name_||'":'||quot_||CASE element_.type = Plsqlap_Document_API.TYPE_BINARY WHEN TRUE THEN element_.value ELSE Encode_Text_Value(element_.value) END||quot_);
               EXCEPTION
                  WHEN value_error THEN
                     Append_Clob_Value(element_.value);
               END;
            ELSIF element_.clob_val IS NOT NULL THEN
               Append_Clob_Value(element_.clob_val);
            ELSE
               Append(rpad(' ', level_)||'"'||name_||'":null');
            END IF;
         END IF;
      ELSE -- compound attribute or document
         IF element_.type = Plsqlap_Document_API.TYPE_DOCUMENT THEN
            -- (-/+) 230331  FLESTEPHS  TK_I_019.01 (START)
            --Append(rpad(' ', level_)||'{"'||name_||'":{'||nl_);
            IF agg_level_ OR level_ = 0 THEN
              Append(rpad(' ', level_)||'{"'||name_||'":{'||nl_);
            ELSE
              Append(rpad(' ', level_)||'{'||nl_);
            END IF;            
            -- (-/+) 230331  FLESTEPHS  TK_I_019.01 (FINISH)
         ELSIF element_.type = Plsqlap_Document_API.TYPE_COMPOUND THEN
            IF(level_ = 0) THEN
                Append(rpad(' ', level_)||'{"'||name_||'":['||nl_);
            ELSE
                Append(rpad(' ', level_)||'"'||name_||'":['||nl_);
            END IF;
         END IF;
         FOR ix_ IN 1..cnt_ LOOP
            Append_Element(main_.elements(element_.children(ix_)), level_+indent_);
            IF ix_ < cnt_ THEN
               Append(','||nl_);
            ELSE
               Append(nl_);
            END IF;
         END LOOP;
         IF element_.type = Plsqlap_Document_API.TYPE_DOCUMENT THEN
            --Append(rpad(' ', level_)||'}}');            
            -- (-/+) 230331  FLESTEPHS  TK_I_019.01 (START)
            IF agg_level_ OR level_ = 0 THEN
              Append(rpad(' ', level_)||'}}');
            ELSE
              Append(rpad(' ', level_)||'}');
            END IF;
            -- (-/+) 230331  FLESTEPHS  TK_I_019.01 (FINISH)
         ELSIF element_.type = Plsqlap_Document_API.TYPE_COMPOUND THEN
             IF(level_ = 0) THEN
                 Append(rpad(' ', level_)||']}');
             ELSE
                Append(rpad(' ', level_)||']');
             END IF;
         END IF;
      END IF;
   END;

BEGIN
   IF indent_ IS NOT NULL THEN
      IF indent_ < 0 THEN
         Error_SYS.Appl_General(lu_name_, 'PLAPDOCIND: Indent [:P1] may not be less than zero', indent_);
      END IF;
      nl_ := CASE use_crlf_ WHEN TRUE THEN chr(13)||chr(10) ELSE chr(10) END;
   END IF;

   Dbms_Lob.CreateTemporary(json_, TRUE, Dbms_Lob.CALL);
   Dbms_Lob.Open(json_, Dbms_Lob.LOB_READWRITE);

   Check_Document___(main_);
   Append_Element(main_.elements(main_.root_id), 0);
   Dbms_Lob.WriteAppend(json_, length(buf_), buf_);
   Dbms_Lob.Close(json_);
END To_Json___;