5 Replies Latest reply on May 24, 2021 2:02 AM by Suvam Pani

    How can i parse a json response

    Saswata Dutta Seasoned Veteran

      i

      I am calling an api which returns an json response as below

      [

            {

            "path": "Raw/Audit/WLA/2345/458c7024-2934-46e3-9831-0b899aae628a/Shoreway JE Detail Report P01-P12 2016.xlsx",

            "metadata":       {

               "Client_code": "123456",

               "Client_name": "Client 1",

               "Engagement_id": "1234",

               "Engagement_name": "Engagement 1",

               "Engagement_start_date": "2021-04-30",

               "File_type": "GeneralLedger",

               "Processing_app_name": "WLA",

               "Project_id": "12345",

               "Request_id": "12345",

               "Service_line_name": "Tax"

            }

         },

            {

            "path": "Raw/Audit/WLA/2345/458c7024-2934-46e3-9831-0b899aae628a/test1.txt",

            "metadata":       {

               "Client_code": "22222",

               "Client_name": "Client 2",

               "Engagement_id": "3456",

               "Engagement_name": "Engagement 2",

               "Engagement_start_date": "2021-04-30",

               "File_type": "GeneralLedger",

               "Processing_app_name": "WLA",

               "Project_id": "34567",

               "Request_id": "666666",

               "Service_line_name": "Tax"

            }

         }

      ]

       

      I need to process this information.

      In order to do that i created a process object as below

       

      PO-FileList

      path Text

      metadata  reference filemetadata

       

      filemetadata has similar structure as below as the json response.

       

      I am using using this process object in the output as below.

       

      But the problem is that when i am using this process object, it is not able to populate the process object as an array and i am not able to each element of the array. How can i parse a json response as above?

       

      Thanks

      Saswata

        • 1. Re: How can i parse a json response
          Suvam Pani New Member

          Hi Saswata,

           

          Since the JSON response is array type, and IICS automatically parses the JSON to XML, the response comes as such:

           

          <root>

             <_1>

                <path>Raw/Audit/WLA/2345/458c7024-2934-46e3-9831-0b899aae628a/Shoreway JE Detail Report P01-P12 2016.xlsx</path>

                <metadata>

                   <File_type>GeneralLedger</File_type>

                   <Engagement_start_date>2021-04-30</Engagement_start_date>

                   <Request_id>12345</Request_id>

                   <Client_name>Client 1</Client_name>

                   <Client_code>123456</Client_code>

                   <Project_id>12345</Project_id>

                   <Service_line_name>Tax</Service_line_name>

                   <Engagement_name>Engagement 1</Engagement_name>

                   <Processing_app_name>WLA</Processing_app_name>

                   <Engagement_id>1234</Engagement_id>

                </metadata>

             </_1>

             <_2>

                <path>Raw/Audit/WLA/2345/458c7024-2934-46e3-9831-0b899aae628a/test1.txt</path>

                <metadata>

                   <File_type>GeneralLedger</File_type>

                   <Engagement_start_date>2021-04-30</Engagement_start_date>

                   <Request_id>666666</Request_id>

                   <Client_name>Client 2</Client_name>

                   <Client_code>22222</Client_code>

                   <Project_id>34567</Project_id>

                   <Service_line_name>Tax</Service_line_name>

                   <Engagement_name>Engagement 2</Engagement_name>

                   <Processing_app_name>WLA</Processing_app_name>

                   <Engagement_id>3456</Engagement_id>

                </metadata>

             </_2>

          </root>

           

          Hence, it is not able to parse directly into the process object.

           

          So, we can try the following XQuery expression to parse into fileList:

           

          for $i in util:parseJSON($RESTResponse/*:payload/data())

          return <fileList>{$i/*}</fileList>

           

          Please take care to name the fields of the Process object appropriately (same name, same case) so that the data can get parsed properly. Sample structure of process objects:

           

          Kindly check if this helps you.

           

          Thanks,

          Suvam

          • 2. Re: How can i parse a json response
            Saswata Dutta Seasoned Veteran

            Hi

             

            Thank you so much for your response. Can you please explain the logic of assigning a json response back to process object. How does it work?

             

            Second thing is what is this logic is doing

             

            for $i in util:parseJSON($RESTResponse/*:payload/data())

            return <fileList>{$i/*}</fileList>

            • 3. Re: How can i parse a json response
              Suvam Pani New Member

              Hi Saswata,

               

              A process object is treated internally as XML. Hence, the structure of fileList would be:

               

              <fileList>

                 <path></path>

                 <metadata>

                    <File_type></File_type>

                    <Engagement_start_date></Engagement_start_date>

                    <Request_id></Request_id>

                    <Client_name></Client_name>

                    <Client_code></Client_code>

                    <Project_id></Project_id>

                    <Service_line_name></Service_line_name>

                    <Engagement_name></Engagement_name>

                    <Processing_app_name></Processing_app_name>

                    <Engagement_id></Engagement_id>

                 </metadata>

              </fileList>

               

              When the output field of type "Object List of fileList" is assigned with "Entire Response", the JSON response is automatically parsed, and it receives the following as input:

               

              <root>

                 <_1>

                    <path>Raw/Audit/WLA/2345/458c7024-2934-46e3-9831-0b899aae628a/Shoreway JE Detail Report P01-P12 2016.xlsx</path>

                    <metadata>

                       <File_type>GeneralLedger</File_type>

                       <Engagement_start_date>2021-04-30</Engagement_start_date>

                       <Request_id>12345</Request_id>

                       <Client_name>Client 1</Client_name>

                       <Client_code>123456</Client_code>

                       <Project_id>12345</Project_id>

                       <Service_line_name>Tax</Service_line_name>

                       <Engagement_name>Engagement 1</Engagement_name>

                       <Processing_app_name>WLA</Processing_app_name>

                       <Engagement_id>1234</Engagement_id>

                    </metadata>

                 </_1>

                 <_2>

                    <path>Raw/Audit/WLA/2345/458c7024-2934-46e3-9831-0b899aae628a/test1.txt</path>

                    <metadata>

                       <File_type>GeneralLedger</File_type>

                       <Engagement_start_date>2021-04-30</Engagement_start_date>

                       <Request_id>666666</Request_id>

                       <Client_name>Client 2</Client_name>

                       <Client_code>22222</Client_code>

                       <Project_id>34567</Project_id>

                       <Service_line_name>Tax</Service_line_name>

                       <Engagement_name>Engagement 2</Engagement_name>

                       <Processing_app_name>WLA</Processing_app_name>

                       <Engagement_id>3456</Engagement_id>

                    </metadata>

                 </_2>

              </root>

               

              Since there is structure mismatch between the process object and the parsed response, the assignment could not be done successfully, hence it requires additional configuration. The restructuring of the response is done by using the XQuery expression shared earlier, i.e:

               

              for $i in util:parseJSON($RESTResponse/*:payload/data())

              return <fileList>{$i/*}</fileList>

               

              Note: util:parseJSON is used here, because the payload in $RESTResponse is not automatically parsed to XML, it contains the actual JSON response, and needs to be parsed explicitly.

               

              Thanks,

              Suvam

              • 4. Re: How can i parse a json response
                Saswata Dutta Seasoned Veteran

                HI Suvam

                 

                Thanks for your reply. I am trying to understand the XQUERY that you have written. Can you please tell me what this $RESTResponse/* is doing.

                 

                How $RESTResponse and RESTResponse/* is different?

                 

                Thanks

                 

                Saswata

                • 5. Re: How can i parse a json response
                  Suvam Pani New Member

                  Hi Saswata,

                   

                  $RESTResponse is an output field that is available for use in Service Connector by default, which contains all the headers and payload of the response invoked from the service. For example, it will be something like this:

                   

                  <rest:RESTResponse xmlns:rest="http://schemas.activebpel.org/REST/2007/12/01/aeREST.xsd"

                                     statusCode="200">

                     <rest:headers>

                        <rest:header name="Cache-Control" value="private"/>

                        <rest:header name="Connection" value="keep-alive"/>

                        ...

                     </rest:headers>

                     <rest:payload contentType="application/json;charset=utf-8">[ { "path": "Raw/Audit/WLA/2345/458c7024-2934-46e3-9831-0b899aae628a/Shoreway JE Detail Report P01-P12 2016.xlsx", "metadata": { "Client_code": "123456", "Client_name": "Client 1", "Engagement_id": "1234", "Engagement_name": "Engagement 1", "Engagement_start_date": "2021-04-30", "File_type": "GeneralLedger", "Processing_app_name": "WLA", "Project_id": "12345", "Request_id": "12345", "Service_line_name": "Tax" } }, { "path": "Raw/Audit/WLA/2345/458c7024-2934-46e3-9831-0b899aae628a/test1.txt", "metadata": { "Client_code": "22222", "Client_name": "Client 2", "Engagement_id": "3456", "Engagement_name": "Engagement 2", "Engagement_start_date": "2021-04-30", "File_type": "GeneralLedger", "Processing_app_name": "WLA", "Project_id": "34567", "Request_id": "666666", "Service_line_name": "Tax" } } ]</rest:payload>

                  </rest:RESTResponse>

                   

                  The payload is available under rest:payload child element as a string. We can obtain it by using $RESTResponse/rest:payload or $RESTResponse/*:payload (using * makes it ignore the prefix/namespace and search for the element name "payload" which is child of $RESTResponse).  Using $RESTResponse/*:payload/data() fetches the data within payload element (without data(), it will come like <rest:payload contentType="application/json;charset=utf-8">...data...</rest:payload>)

                   

                  Thanks,

                  Suvam