JSON formated data Web service Jobs deployed in ISD

Dedicated to DataStage and DataStage TX editions featuring IBM<sup>®</sup> Service-Oriented Architectures.

Moderators: chulett, rschirm

Post Reply
yijuanl
Participant
Posts: 23
Joined: Fri Jun 24, 2005 8:32 am

JSON formated data Web service Jobs deployed in ISD

Post by yijuanl »

Hi,
I have a web service JSON formatted job with two output stages: one is the sequential file only for viewing the JSON data and another is ISD output stage. I deployed this job in ISD binding it as rest2.0 HTTP. Here are the issues.
1) Since the output file in sequential file looks good as JSON formatted, I want the web service end point pass through the data untouched. However if I check the checked box on Output Pass through( in Operations -> Rest 2.0 settings), I only get one record as a result in Browser out of the 474 expected records.
2) I tried to uncheck the Output Pass through and check the array box in output tab, in the Browser, I get all the data but with the extra tag name as defined in the output column. Plus the data contains backslash in front of every double quotes.
Thanks!
Jane
yl
eostic
Premium Member
Premium Member
Posts: 3838
Joined: Mon Oct 17, 2005 9:34 am

Post by eostic »

I am not exactly sure of your complete scenario.....Please describe what you mean by "JSON" formatted Job...and what exactly are you sending into the Sequential file?

Are you building the JSON inside of the Job, using the Hierarchical Stage?...and then sending that output into your Sequential File and ISDOutput?

That would possibly explain the one row conditions and confusion.....

Ernie
Ernie Ostic

blogit!
<a href="https://dsrealtime.wordpress.com/2015/0 ... ere/">Open IGC is Here!</a>
yijuanl
Participant
Posts: 23
Joined: Fri Jun 24, 2005 8:32 am

Post by yijuanl »

In Datastage, My job design is
Oracle Connector -> XML stage -> ISD output and sequential file
In XML stage, I have two HJoin steps for hierarchy with two JSON composers respectively and the final JSON composer to mapping the previous steps result.
My expecting result is to send the JSON formatted stream data (a LongVarChar string) from XML stage output to sequential file stage and ISD output stage. So far the data in sequential file has the JSON formatted data as expect.
In ISD deployment, my binding is REST 2.0. I want to output pass through without touching the data from DS output. But I only get one record data instead of 474 expected records when I run URL in Browser.
And if I unchecked the output pass through, and check the array check box in output tab in Operation step in ISD console, I get all the records but with extra column name as a tag name in JSON data and backslash in front of every double quotes.

Thank you in advance!
Jane Liu

P.S.
My answer is below:

> Are you building the JSON inside of the Job, using the Hierarchical Stage?...
YES.
> and then sending that output into your Sequential File and ISDOutput?
YES.
>That would possibly explain the one row conditions and confusion.....
I am not sure that you meant, but the output in sequential file contains all 474 records with JOSN format as expected one longvarchar string data. But after deployment, the browser shows only one record.
yl
eostic
Premium Member
Premium Member
Posts: 3838
Joined: Mon Oct 17, 2005 9:34 am

Post by eostic »

If you are using the REST binding and want JSON as output, don't build it inside of DataStage....let ISD do that for you. Just send the output directly from your source into the ISDoutput Stage. You will get JSON at the other side at your web services client.

Ernie
Ernie Ostic

blogit!
<a href="https://dsrealtime.wordpress.com/2015/0 ... ere/">Open IGC is Here!</a>
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

yijuanl wrote:But after deployment, the browser shows only one record.
Is this actually a problem? XML doesn't require any 'fomatting' and can present itself as one long record... I am thinking JSON would be the same. ??
-craig

"You can never have too many knives" -- Logan Nine Fingers
eostic
Premium Member
Premium Member
Posts: 3838
Joined: Mon Oct 17, 2005 9:34 am

Post by eostic »

Nor does json. The real key here is that, if I understand the thread correctly, there is needless redundant json formatting going on.

If you want your REST clients to receive a properly formatted REST JSON payload, send your data DIRECTLY to the ISDoutput Stage. Dont build json in the job --- isd is going to do that for you.

If you build json in the job and send to isd output, you are sending one single row (a large chunk of json) that is then packaged AGAIN inside of json. Interesting but not the goal.

If you still really need the job to do its own json work, or if it is for debugging, just use a copy in front of the xml stage......send your rows to xml and seq on one link and then to isd output on the other.

Ernie
Ernie Ostic

blogit!
<a href="https://dsrealtime.wordpress.com/2015/0 ... ere/">Open IGC is Here!</a>
yijuanl
Participant
Posts: 23
Joined: Fri Jun 24, 2005 8:32 am

Post by yijuanl »

Hi Ernie,
My JSON format data is required to be hierarchy structured, the expecting result is like the following.
{"results":[{"organization":"{"OrganizationID":"17136","OrganizationNumber":"16292","OrganizationName":"THE ASSOCIATION OF THE UNITED STATES ARMY","LastUpdateDate":"05/06/2004","Relationship_With_MITRE":["Billing & Contracts Customer"],"Organization_Type":["Government Military"]}"},{"organization":"{"OrganizationID":"23232","OrganizationNumber":"22412","OrganizationName":"Directorate General of Civil Aviation of Mexico","Abbreviation":"DGAC","KnownAs":"DIRECCION GENERAL DE AERONAUTICA CIVIL (DGAC)","LastUpdateDate":"02/05/2015","Relationship_With_MITRE":["Billing & Contracts Customer"],"Organization_Type":["Foreign Agency"]}"}]}

However I get this with the backslash.
{"results":[{"organization":"{\"OrganizationID\":\"17136\",\"OrganizationNumber\":\"16292\",\"OrganizationName\":\"THE ASSOCIATION OF THE UNITED STATES ARMY\",\"LastUpdateDate\":\"05/06/2004\",\"Relationship_With_MITRE\":[\"Billing & Contracts Customer\"],\"Organization_Type\":[\"Government Military\"]}"},{"organization":"{\"OrganizationID\":\"23232\",\"OrganizationNumber\":\"22412\",\"OrganizationName\":\"Directorate General of Civil Aviation of Mexico\",\"Abbreviation\":\"DGAC\",\"KnownAs\":\"DIRECCION GENERAL DE AERONAUTICA CIVIL (DGAC)\",\"LastUpdateDate\":\"02/05/2015\",\"Relationship_With_MITRE\":[\"Billing & Contracts Customer\"],\"Organization_Type\":[\"Foreign Agency\"]}"}

How can I get rid of backslash?
Thanks!
Jane
yl
phand@mitre.org
Premium Member
Premium Member
Posts: 3
Joined: Thu Mar 09, 2006 11:16 am

Post by phand@mitre.org »

Hi Craig,
Based on the IBM red book, you are right.
However I do not get the whole longvarchar string as result neither for JSON nor for XML.
my DS output contains the longvarchar string with more than one records in it. If I check the check box in REST2.0 setting as output pass through, I only get one record instead of the whole content of the DS output. The reason I check the output pass through because I already get the data formatted as JSON in DS job XML stage and I want ISD to just pass the output through untouched.
Why do I only get one record in the big string which actually contains hundreds of records?
Since I do not get the whole content of the data, I try to check the array check box in output tab, then I encounter another issue - backslash.
Thank you both very much for helping!
Jane
yijuanl
Participant
Posts: 23
Joined: Fri Jun 24, 2005 8:32 am

Post by yijuanl »

To avoid confusing, the expecting data is like the following. Notice that Relationship_With_Mitre and Organization_type are the arrays. This is required.

{"results":[{"organization":"{"OrganizationID":"17136","OrganizationNumber":"16292","OrganizationName":"THE ASSOCIATION OF THE UNITED STATES ARMY","LastUpdateDate":"05/06/2004","Relationship_With_MITRE":["Billing & Contracts Customer","Sponsor"],"Organization_Type":["Government Military"]}"},{"organization":"{"OrganizationID":"23232","OrganizationNumber":"22412","OrganizationName":"Directorate General of Civil Aviation of Mexico","Abbreviation":"DGAC","KnownAs":"DIRECCION GENERAL DE AERONAUTICA CIVIL (DGAC)","LastUpdateDate":"02/05/2015","Relationship_With_MITRE":["Billing & Contracts Customer"],"Organization_Type":["Foreign Agency","Military"]}"}]}
yl
eostic
Premium Member
Premium Member
Posts: 3838
Joined: Mon Oct 17, 2005 9:34 am

Post by eostic »

Best I can suggest is to work with the Text over Http binding instead of the rest binding. It was designed for this purpose ......when you want absolute passthru....where you are doing all of the formattinf of the payload. I havent used text-over-http with json and rest, but have used it lots with soap when i had to also build a deep hierarchy. You may have tonplay with the detailed options at the operations output tab to define the http content, but i bet it will get you cloaer to your objective.

Ernie
Ernie Ostic

blogit!
<a href="https://dsrealtime.wordpress.com/2015/0 ... ere/">Open IGC is Here!</a>
Post Reply