Solved

Cannot create a simple JSON using PLSQLAP_Document_API

  • 23 July 2023
  • 4 replies
  • 96 views
R
Rank
Userlevel 2
Badge +7
  • Rohit
  • Sidekick (Partner)
  • 36 replies

Hi,

I wanted to create a simple JSON clob like -

{
    "attribute1": "value1",
    "attribute2": "value2",
    "attribute3": "value3"
}

I am using PLSQLAP_Document_API to create this type of data. But the problem is, during initialization of the PLSQLAP Document, I have to give a name. And that name becomes a parent attribute for rest of my attributes and the output is like -

{
    "MAIN_DOC": {
        "attribute1": "value1",
        "attribute2": "value2",
        "attribute3": "value3"
    }
}

I can’t get rid of this “MAIN_DOC” attribute. Is there something I am doing wrong?

Below is my snippet -

DECLARE
   json_doc_ PLSQLAP_Document_API.Document :=
             PLSQLAP_Document_API.New_Document('MAIN_DOC');
   json_ CLOB;
BEGIN
   PLSQLAP_Document_API.Add_Attribute(json_doc_, 'attribute1', "value1");
   PLSQLAP_Document_API.Add_Attribute(json_doc_, 'attribute2', "value2");
   PLSQLAP_Document_API.Add_Attribute(json_doc_, 'attribute3', "value3");
   PLSQLAP_Document_API.To_Json(json_ => json_,
                                main_ => json_doc_);
END;

 

Thanks in advance,

Rohit.

icon

Best answer by flestephs 9 April 2024, 12:50

View original

4 replies

F
Rank
Userlevel 2
Badge +6

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.

F
Rank
Userlevel 2
Badge +6

PROCEDURE Remove_Outer_Json_Node___ (
                               json_        IN OUT NOCOPY CLOB,
                               raise_error_ IN            BOOLEAN DEFAULT TRUE )
IS
   json_ele_  JSON_ELEMENT_T;
   json_obj_  JSON_OBJECT_T;
   json_arr_  JSON_ARRAY_T;
   json_keys_ JSON_KEY_LIST;
BEGIN
   IF (json_ IS NULL OR json_ = empty_clob OR Dbms_Lob.GetLength(json_) = 0) THEN
      IF (raise_error_) THEN
         Error_SYS.Appl_General(lu_name_, 'JSON_ERR1: Could not remove outer node from JSON as it is empty.');
      ELSE
         RETURN;
      END IF;
   END IF;
   
   json_ele_ := JSON_ELEMENT_T.parse(json_);
   IF (json_ele_.Is_Object()) THEN   
      json_obj_  := TREAT(json_ele_ AS JSON_OBJECT_T);
         
      json_keys_ := json_obj_.Get_Keys;
      IF (json_keys_.COUNT <> 1) THEN
         IF (raise_error_) THEN
            Error_SYS.Appl_General(lu_name_, 'JSON_ERR2: Could not remove outer node from JSON as multiple keys exist.');
         ELSE
            RETURN;
         END IF;
      END IF;
      
      json_ := json_obj_.Get(json_keys_(1)).To_Clob();
   ELSIF (json_ele_.Is_Array()) THEN
      IF (json_ele_.Get_Size > 1) THEN
         IF (raise_error_) THEN
            Error_SYS.Appl_General(lu_name_, 'JSON_ERR3: Could not remove outer node from JSON as the outer node is an array with more than one element.');
         ELSE
            RETURN;
         END IF;
      END IF;
      
      json_arr_ := TREAT(json_ele_ AS JSON_ARRAY_T);
      json_ := json_arr_.Get(0).To_Clob();
   ELSE
      IF (raise_error_) THEN
         Error_SYS.Appl_General(lu_name_, 'JSON_ERR4: Could not remove outer node from JSON as the outer node is not an object or an array.');
      ELSE
         RETURN;
      END IF;
   END IF;
END Remove_Outer_Json_Node___;

F
Rank
Userlevel 2
Badge +6

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
    }
  ]
}

F
Rank
Userlevel 2
Badge +6

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___;

Reply


Terms & ConditionsCookie settings