JSON to ABAP transformation: missing field issue

[johnm16]

Hi Folks,

hoping that sandra_rossi will pick this up, but hey, maybe someone else will have an answer.

I have an incoming JSON structure to transform into ABAP; so far, so good. Unfortunately, the JSON content is inconsistent. I have learned how to deal with occasional null values using this technique in the transformation:

          <tt:cond>
            <num name="ewbno">
              <tt:value ref="einvoice_response.results.message.ewbno"/>
            </num>
          </tt:cond>
          <tt:cond>
            <null name="ewbno">
              <tt:value ref="einvoice_response.results.message.ewbno"/>
            </null>
          </tt:cond>
          <tt:cond>

However, I there is another issue; I'm hoping that one of the ST commands will solve it, but the ABAP keyword help isn't very detailed. The problem is this: is there any way to deal with a key pair in the JSON string that might not even be present? So the JSON structure might look like this:

{
  "QRCodeUrl": "https://sandb-api..../",
  "EinvoicePdf": "https://sandb-api..../",
  "EwaybillPdf": "https://sandb-api..../",
  "Status": "ACT",
  "Remarks": ""
}

Or it might look like this:

{
  "QRCodeUrl": "https://sandb-api..../",
  "EinvoicePdf": "https://sandb-api..../",
  "Status": "ACT",
  "Remarks": ""
}

Furthermore, the (external) API programming team who produce this stuff are not about to play ball. I'm stuck with it.

There is an obvious workaround of course: I could just use two almost identical transformations, and search the incoming string for 'EwaybillPdf', then process accordingly.

But I'm hoping there is a more elegant solution.

Thank you for reading,

John M.

[thkolz]

Sometimes I'm wondering that in 99% I'm managing to solve ABAP issues on my own...

Did you try like that (copied from here)?

TYPES:
  BEGIN OF s_person,
    name TYPE string,
    title TYPE string,
    age TYPE i,
  END OF s_person.

TYPES: t_person TYPE STANDARD TABLE OF s_person WITH DEFAULT KEY.

DATA: json TYPE string VALUE '{"VALUES":[{"NAME":"Horst","TITLE":"Herr","AGE":30},{"NAME":"Jutta","AGE":35},{"NAME":"Ingo","TITLE":"Herr","AGE":31}]}'.

DATA(it_persons) = VALUE t_person( ).
* JSON -> ABAP (iTab)
CALL TRANSFORMATION id SOURCE XML json RESULT values = it_persons.

LOOP AT it_persons INTO DATA(s_persons).
  WRITE: / s_persons-name, s_persons-title, s_persons-age.
ENDLOOP.

[johnm16]

Hi Thorsten,

that looks interesting, but I'm not sure what you are suggesting.

What would the relevant lines of your Simple Transformation look like?

Cheers

John

[thkolz]

You don't need an XSLT transformation.
Just create a type with the needed fields... (s_person in this example).

Be aware that in this case you need to put

{"VALUES":[ <your data here> ]} 

to wrap your JSON data.

[johnm16]

Hi Thorsten,

what happens if you call the transformation and the VALUES key is not in the JSON string at all? Because you have the "values" key in your ABAP command line.

Cheers

John

[thkolz]

Without VALUES (must be upper case) it will fail...

lv_json = '{VALUES:[' && lv_json && ']}'.

[johnm16]

Hi Thorsten,

now I think you understand my problem.

The incoming JSON string might contain the key pair (VALUES, in your example); or it might not.

So this really is a Transformation question...

Thank you for your suggestion.

Cheers

John

[Tomas_Buryanek]

And what is the exact issue? It is normal behavior ignore empty JSON fields.

[Tomas_Buryanek]

Anyway I have very good experience using /UI2/CL_JSON.

Or the approach from Thorsten looks good too 🙂

[johnm16]

Hi Thomas,

please read the original question again. It is not an empty field. You have made the same assumption as Thorsten did.

Cheers

John

[thkolz]

This report works for me (and hopefully for you):

REPORT ztest.

TYPES:
  BEGIN OF ty_data,
    qrcodeurl   TYPE string,
    einvoicepdf TYPE string,
    ewaybillpdf TYPE string,
    status      TYPE string,
    remarks     TYPE string,
  END OF ty_data.

TYPES tt_data TYPE STANDARD TABLE OF ty_data WITH DEFAULT KEY.

DATA: lt_data TYPE tt_data.

DATA:
  lv_json TYPE string.

lv_json = '{"QRCodeUrl": "https://sandb-api..../","EinvoicePdf": "https://sandb-api..../","EwaybillPdf": "https://sandb-api..../","Status": "ACT","Remarks": ""},' &&
          '{"QRCodeUrl": "https://sandb-api..../","EinvoicePdf": "https://sandb-api..../","Status": "ACT","Remarks": ""}'.

* Convert field names to upper case (otherwise CALL TRANSFORMATION doesn't work)
DATA(lt_strings) = VALUE stringtab( ( `QRCodeUrl` ) ( `EinvoicePdf` ) ( `EwaybillPdf` ) ( `Status` ) ( `Remarks` )  ).

LOOP AT lt_strings INTO DATA(lv_string).
  REPLACE ALL OCCURRENCES OF lv_string IN lv_json WITH to_upper( lv_string ).
ENDLOOP.

* Manipulate JSON to make CALL TRANSFORMATION work
lv_json = '{"VALUES":[' && lv_json && ']}'.

TRY.
    CALL TRANSFORMATION id SOURCE XML lv_json RESULT values = lt_data.

  CATCH cx_root INTO DATA(r_cx_root).

    DATA(lv_msg) = r_cx_root->get_text( ).
    MESSAGE lv_msg TYPE 'I'.
    RETURN.
ENDTRY.

LOOP AT lt_data INTO DATA(s_data).
  WRITE: / s_data-qrcodeurl, s_data-einvoicepdf, s_data-ewaybillpdf, s_data-status, s_data-remarks.
ENDLOOP.

[johnm16]

Thank you Thorsten; neat programming.

But doesn't your concatenation mean that your JSON string will always contain the "EwaybillPdf" field name?

What happens if it's not in the string?

With complex deep ABAP structures, I still prefer using a transformation where possible.

Cheers

John

[thkolz]

Hi John,

as you can see from my code, I took exactly the JSON data that you provided (also without 'EwaybillPdf' filled).

The transformation also works if fields are not contained OR empty.
If you had tried the code yourself, you would have found out.

Best regards,
Thorsten.

[johnm16]

Hi Thorsten,

yes, I know it works, and I'm grateful to you for spending your time and effort: but I would still like a way to do the same thing with a transformation.

I'm using deep data structures that contain many fields and multiple levels of nested tables: and for me, transformations are easier to maintain. However, if our external development partners continue to give us a hard time, I might still try to adapt your approach to deal with it.

Thanks again,

John

[thkolz]

Hi John,

I also use this with a deep structure and table types.

Therefore I’ve created a DDIC structure.

Give it a try! Personally I like this approach much more than XSLT transformations.

But you’re right: XSLT transformations are more flexible.

Best regards,

Thorsten.

[Tomas_Buryanek]

johnm16 I read the question three times but still I am not sure what do you mean. "Key pair" can be missing in JSON. Anything can be missing in JSON in theory. Your JSON parsing logic should not expect any field to always be there.

[Sandra_Rossi]

I think you have a specific problem for which you didn't give enough information to reproduce.

<tt:cond>...</tt:cond> is sufficient for your case.

Whatever it's XML or JSON. JSON won't behave differently from XML as SAP JSON-XML format (<object...> <str...> etc.) is just a layer over XML.

My JSON test (no issue), with or without tag EwaybillPdf:

  METHOD test_json.
    TYPES: BEGIN OF ztest_xml,
             einvoicepdf TYPE string,
             ewaybillpdf TYPE string,
             status      TYPE string,
           END OF ztest_xml.
    transfo_name = create_transfo( concat_lines_of( sep = |\r\n| table = VALUE string_table(
              ( |<?sap.transform simple?>        | )
              ( |<tt:transform xmlns:tt="http://www.sap.com/transformation-templates">| )
              ( |  <tt:root name="CONFIRMATION"/>                                     | )
              ( |  <tt:template>                                                      | )
              ( |    <object tt:ref=".CONFIRMATION">                                  | )
              ( |      <str name="EinvoicePdf" tt:value-ref="EINVOICEPDF"/>           | )
              ( |      <tt:cond>                                                      | )
              ( |        <str name="EwaybillPdf" tt:value-ref="EWAYBILLPDF"/>         | )
              ( |      </tt:cond>                                                     | )
              ( |      <str name="Status" tt:value-ref="STATUS"/>                     | )
              ( |    </object>                                                        | )
              ( |  </tt:template>                                                     | )
              ( |</tt:transform>                                                      | ) ) ) ).
    DATA(confirmation) = VALUE ztest_xml( ).
    DATA(json) ='{'
             && '"EinvoicePdf": "https://sandb-api..../",'
*             && '"EwaybillPdf": "https://sandb-api..../",'
             && '"Status": "ACT"'
             && '}'.
*    CALL TRANSFORMATION id
*        SOURCE XML json
*        RESULT XML data(json_xml).
    CALL TRANSFORMATION (transfo_name)
        SOURCE XML json
        RESULT confirmation = confirmation.
    cl_abap_unit_assert=>assert_equals( act = confirmation
        exp = VALUE ztest_xml( einvoicepdf = 'https://sandb-api..../'
*                               ewaybillpdf = 'https://sandb-api..../'
                               status = 'ACT' ) ).
  ENDMETHOD.

NB: same test but for XML:

    TYPES: BEGIN OF ztest_xml,
             einvoicepdf TYPE string,
             ewaybillpdf TYPE string,
             status      TYPE string,
           END OF ztest_xml.
    transfo_name = create_transfo( concat_lines_of( sep = '\r\n' table = VALUE string_table(
              ( '<?sap.transform simple?>        ' )
              ( '<tt:transform xmlns:tt="http://www.sap.com/transformation-templates">' )
              ( '  <tt:root name="CONFIRMATION"/>                                     ' )
              ( '  <tt:template>                                                      ' )
              ( '    <Confirmation tt:ref=".CONFIRMATION">                            ' )
              ( '      <EinvoicePdf tt:value-ref="EINVOICEPDF"/>                      ' )
              ( '      <tt:cond>                                                      ' )
              ( '        <EwaybillPdf tt:value-ref="EWAYBILLPDF"/>                    ' )
              ( '      </tt:cond>                                                     ' )
              ( '      <Status tt:value-ref="STATUS"/>                                ' )
              ( '    </Confirmation>                                                  ' )
              ( '  </tt:template>                                                     ' )
              ( '</tt:transform>                                                      ' ) ) ) ).
    DATA(confirmation) = VALUE ztest_xml( ).
    DATA(xml) ='<?xml version="1.0" encoding="UTF-8"?> '
            && '<Confirmation>'
            && '  <EinvoicePdf>https://sandb-api..../</EinvoicePdf> '
*            && '  <EwaybillPdf>https://sandb-api..../</EwaybillPdf> '
            && '  <Status>ACT</Status> '
            && '</Confirmation>'.
    CALL TRANSFORMATION (transfo_name)
        SOURCE XML xml
        RESULT confirmation = confirmation.
    cl_abap_unit_assert=>assert_equals( act = confirmation
        exp = VALUE ztest_xml( einvoicepdf = 'https://sandb-api..../'
*                               ewaybillpdf = 'https://sandb-api..../'
                               status = 'ACT' ) ).

[johnm16]

Hi Sandra,

thank you: I was sure there would be a simple and elegant way to solve this.

The main issue (for me) was trying to find working examples of the ST commands. Be great if SAP had thought to include an ST with an example of every command with every switch: I can dream...

Cheers,

John

[Sandra_Rossi]

I guess that for all these things SAP will never do, the community could start create libraries and share them (like abap2xlsx, abapGit, etc.), but we can dream too 😛

[johnm16]

Hi Sandra,

further to the conversation above, do you know if it's still possible to use simple transformations where the key pairs in a specific JSON object for deserialization might arrive in varying sequences?

The definition of a JSON object says that varying the key pair sequences is normal, but this doesn't seem to sit with the fixed key pair sequence in an ST.

Cheers

John

[Sandra_Rossi]

Hi John, yes you must use <group> which means items in any order.