-
1. Re: ICRT : How to assign JSON o/p to Process objects
Mahesh Veluvolu Mar 8, 2018 9:47 PM (in response to Gopinath Vijayan)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 Mar 9, 2018 4:24 AM (in response to Mahesh Veluvolu)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 Mar 9, 2018 4:36 AM (in response to Gopinath Vijayan)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
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 Mar 9, 2018 5:49 AM (in response to Mateusz Wolf)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 Mar 9, 2018 6:02 AM (in response to Gopinath Vijayan)1 of 1 people found this helpfulCould 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'
-
6. Re: ICRT : How to assign JSON o/p to Process objects
Gopinath Vijayan Mar 9, 2018 6:32 AM (in response to Mateusz Wolf)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 Mar 9, 2018 6:40 AM (in response to Gopinath Vijayan)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 Mar 9, 2018 6:51 AM (in response to Gopinath Vijayan)1 of 1 people found this helpfulGopinath,
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
-
9. Re: ICRT : How to assign JSON o/p to Process objects
Mateusz Wolf Mar 9, 2018 6:52 AM (in response to Gopinath Vijayan)1 of 1 people found this helpfulHi,
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.
-
10. Re: ICRT : How to assign JSON o/p to Process objects
Gopinath Vijayan Mar 9, 2018 7:23 AM (in response to Rich Bateman)Thanks Rich.
In my scenario, email is out of address tag.
<catop>
- <email>bb@hy.net</email>
- <addr>
- <city>NewYork</city>
- <country>US</country>
- </addr>
</catop>
<catop>
- <email>sr@abd.com</email>
- <addr>
- <city>SanDiego</city>
- <country>US</country>
- </addr>
- <addr>
</catop>
-
11. Re: ICRT : How to assign JSON o/p to Process objects
Rich Bateman Mar 9, 2018 7:50 AM (in response to Gopinath Vijayan)1 of 1 people found this helpfulGopinath,
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
-
12. Re: ICRT : How to assign JSON o/p to Process objects
Gopinath Vijayan Mar 9, 2018 8:02 AM (in response to Rich Bateman) -
13. Re: ICRT : How to assign JSON o/p to Process objects
Rich Bateman Mar 9, 2018 8:07 AM (in response to Gopinath Vijayan)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 Mar 9, 2018 8:15 AM (in response to Rich Bateman)Tried below 3 combinations. No luck
for $catop in $output.allop/catop
for $catop in $output.allop/catop/*
for $catop in $output.allop/*