1 2 Previous Next 16 Replies Latest reply on Mar 9, 2018 9:29 AM by Gopinath Vijayan

    ICRT : How to assign JSON o/p to Process objects

    Gopinath Vijayan Guru

      Hi All,

       

      How to retrieve JSON data from Rest API output?

       

      I am not able to assign the results to a process object. Below is my sample json output from the Rest API.

       

         { 

                "address":{ 

              "city":"Kerrville",

               "country":"US",

                    },

            "email":"xyz@abc.net",

           },

        { 

                "address":{ 

              "city":"Kerrville",

               "country":"US",

                    },

            "email":"abcd@123.net",

           }

      ]

       

      My goal is to read the JSON and write  into CSV.

       

      Final File to be:

       

      city,country,email

      Kerrville,US,xyz@abc.net

      Kerrville,US,abcd@123.net

       

      Thanks,

      Gopi

        • 1. Re: ICRT : How to assign JSON o/p to Process objects
          Mahesh Veluvolu Seasoned Veteran

          Hello Gopi,

           

          Couple of confusions here. Are you using ICRT or ICS. It is not possible in ICRT to write the data into CSV. Because these are API's and the output will be in XML/JSON format only.

          In ICS it is definitely possible. Create a Business service connector and call the same in mapping by using webservice transformation. Create a flat file connection and define a CSV file with the required structure. Pull that target into the mapping and link the webservice transformation to this target. While linking it will ask for "choose the object". Choose the correct object (Not fault group) and map the columns.

          Try this and post the exact error if this is not working.

           

          -Mahesh

          • 2. Re: ICRT : How to assign JSON o/p to Process objects
            Gopinath Vijayan Guru

            Mahesh,

            Thanks for your reply.

             

            I am using ICRT for the REST API calls.

            Once I capture the return of the API in Process objects, I can then use File writer(delimited) process to write in Flatfile.

             

            Thanks,

            Gopi

            • 3. Re: ICRT : How to assign JSON o/p to Process objects
              Mateusz Wolf Active Member

              First of all, in your service connector you'll need to create a process object which corresponds to the structure of the JSON you're getting back, so in you're case it would have the following fields:

              • address
              • city
              • country
              • email

               

              Then you'll need to define an output property for your service connector action, which is of type Object List > {name of the object you created above}.

               

              Then in your action output you'll need to use an xquery expression to parse the XML which Service Connector produces after receiving JSON.

               

              then you'll need to loop through the objects and return an instance of PO for each record in JSON e.g:

               

              for $element in //

              return

              <object>

                 <address>{$element/address/data()}</address>

                 ...

              </object>

              • 4. Re: ICRT : How to assign JSON o/p to Process objects
                Gopinath Vijayan Guru

                Hello Mateusz Wolf,

                 

                Thanks for your response.

                 

                I did create PO and output as you mentioned.

                And when I tried to write xquery as below, I am getting "unexpected token "{" in path expression" error

                 

                for $element in //

                return

                <catop>

                   <email>{$element/email/data()}</email>

                   <addr>

                      <city>{$element/address/city/data()}</city>

                      <country>{$element/address/country/data()}</country>

                   </addr>

                </catop>

                • 5. Re: ICRT : How to assign JSON o/p to Process objects
                  Mateusz Wolf Active Member

                  Could you try

                   

                  for $element in /root

                  return

                  <catop>

                     <email>{$element/email/data()}</email>

                     <addr>

                        <city>{$element/address/city/data()}</city>

                        <country>{$element/address/country/data()}</country>

                     </addr>

                  </catop>

                   

                  or  for $element in /root/* or just /*

                   

                  and make sure that Type is set to 'Xquery'

                  1 of 1 people found this helpful
                  • 6. Re: ICRT : How to assign JSON o/p to Process objects
                    Gopinath Vijayan Guru

                    The below worked

                     

                    for $element in /root

                    return

                    <catop>

                       <email>{$element/email/data()}</email>

                       <addr>

                          <city>{$element/address/city/data()}</city>

                          <country>{$element/address/country/data()}</country>

                       </addr>

                    </catop>

                     

                    However test results shows NULL

                     

                    allop<catop><email/><addr><city/><country/></addr></catop>

                     

                    Also I created a Process to read PO but it just returns the tag names.

                    • 7. Re: ICRT : How to assign JSON o/p to Process objects
                      Gopinath Vijayan Guru

                      I included * as below,

                       

                      for $element in /root/*

                      return

                      <catop>

                         <email>{$element/email/data()}</email>

                         <addr>

                            <city>{$element/address/city/data()}</city>

                            <country>{$element/address/country/data()}</country>

                         </addr>

                      </catop>

                       

                      Now the test results are fine

                      <catop><email>muuurph@sbcglobal.net</email><addr><city>Kerrville</city><country>US</country></addr></catop>

                       

                      but still I am figuring out how to parse the PO and write in flat file.

                       

                      Thanks for your assistance

                      • 8. Re: ICRT : How to assign JSON o/p to Process objects
                        Rich Bateman Seasoned Veteran

                        Gopinath,

                        Please see the attached artifacts.  This is a sample that consumes a JSON payload and outputs to a CSV.

                         

                         

                        InformaticaCommunity-connection-250888: This is a File connection with 1 Event Target configured.  You will run this on a local secure agent.  You will want to update the Directory to your desired location.  I have the delimiter set to |.

                         

                        InformaticaCommunity-connection-250888:  This is the process that will consume the JSON (I just used POSTMAN in this example).  1 Assignment step and 1 Service Call. 

                         

                        JSON input -

                         

                        {
                        "input": {
                        "address": [
                        {
                        "city": "Kerrville",
                        "country": "US",
                        "email": "xyz@abc.net"
                        },
                        {
                        "city": "Kerrville",
                        "country": "US",
                        "email": "abcd@123.net"
                        }
                        ]
                        }
                        }
                        

                         

                         

                        CSV produced -

                        city|country|email
                        Kerrville|US|xyz@abc.net
                        Kerrville|US|abcd@123.net
                        

                         

                         

                        Hope this helps!

                        Rich

                        1 of 1 people found this helpful
                        • 9. Re: ICRT : How to assign JSON o/p to Process objects
                          Mateusz Wolf Active Member

                          Hi,

                           

                          No problem.

                           

                          To write it out to the flat file, you might need to flatten the object, so that it only has one 'level' of properties.

                          e.g. like that:

                           

                          <catop>

                              <email>muuurph@sbcglobal.net</email>

                              <addrcity>Kerrville</addrcity>

                              <addrcountry>US</addrcountry>

                          </catop>

                           

                          as in my experience the delimited content writer omits the child nodes when creating the CSV.

                           

                          for writing out the objects to CSV have a look at this thread ICRT Sample - Reading and Writing - XML to Delimited CSV or JSON and Vice Versa.

                          1 of 1 people found this helpful
                          • 10. Re: ICRT : How to assign JSON o/p to Process objects
                            Gopinath Vijayan Guru

                            Thanks Rich.

                             

                            In my scenario, email is out of address tag.

                                           <catop>

                              1. <email>bb@hy.net</email>
                              2. <addr>
                                1. <city>NewYork</city>
                                2. <country>US</country>
                              3. </addr>

                                           </catop>

                             

                                   

                                        <catop>

                              1. <email>sr@abd.com</email>
                                1. <addr>
                                  1. <city>SanDiego</city>
                                  2. <country>US</country>
                                2. </addr>

                                           </catop>

                             

                            • 11. Re: ICRT : How to assign JSON o/p to Process objects
                              Rich Bateman Seasoned Veteran

                              Gopinath,

                              Sorry about that, try out these changes.

                               

                              Change on the Assignment step of the process.

                              Update this assignment DelimitedContentSerializationTask>Custom objects

                              With this Formula-

                              for $catop in $input.input
                              return
                                              <root>
                                                  <email>{$catop/email/text()}</email>
                                                  <city>{$catop/addr/city/text()}</city>
                                                  <country>{$catop/addr/country/text()}</country>
                                              </root>
                              

                               

                              Update this assignment DelimitedContentSerializationTask>Custom headers

                              (
                              <header>
                              <fieldIndex>1</fieldIndex>
                                  <name>email</name>
                              </header>,
                              <header>
                              <fieldIndex>2</fieldIndex>
                                  <name>city</name>
                              </header>,
                              <header>
                              <fieldIndex>3</fieldIndex>
                                  <name>country</name>
                              </header>
                              )
                              

                               

                              Updated Input to use in your request -

                              {
                              "input": [
                              {
                              "email": "bb@hy.net",
                              "addr": {
                              "city": "NewYork",
                              "country": "US"
                              }
                              },
                              {
                              "email": "sr@abd.com",
                              "addr": {
                              "city": "SanDiego",
                              "country": "US"
                              }
                              }
                              ]
                              }
                              
                              
                              
                              

                               

                              You should get your desired results in the CSV.

                               

                              Thank you,

                              Rich

                              1 of 1 people found this helpful
                              • 12. Re: ICRT : How to assign JSON o/p to Process objects
                                Gopinath Vijayan Guru

                                Process return empty file. Below are the screenshots

                                 

                                my service connector test results:

                                 

                                my service connector output in process (allop)

                                 

                                m

                                 

                                 

                                delimited serialization

                                 

                                assignment

                                 

                                Formula

                                • 13. Re: ICRT : How to assign JSON o/p to Process objects
                                  Rich Bateman Seasoned Veteran

                                  Try changing your customObjects formula -

                                  for $catop in $output.allop/catop

                                  • 14. Re: ICRT : How to assign JSON o/p to Process objects
                                    Gopinath Vijayan Guru

                                    Tried below 3 combinations. No luck

                                     

                                    for $catop in $output.allop/catop

                                     

                                    for $catop in $output.allop/catop/*

                                     

                                    for $catop in $output.allop/*

                                    1 2 Previous Next