topic Exception handling with JSON column names in Talend Studio

Exception handling with JSON column names

Anonymous — Wed, 10 Feb 2016 16:40:52 GMT

I'm working on exception handling with our JSON data. Specifically we are able to capture and reject malformed data using the rejection connectors within Talend, but it does not take into account things such as:

removing a schema column name from JSON (ex. remove "username" from JSON)
renaming a schema column name (ex. rename stepName to strepName in JSON)
What is the "proper" method to take a JSON structure, evaluate the column header information and compare it with an expected known good value. Those that don't match get kicked out as exceptions. It doesn't appear that the tSchemaComplianceCheck component will validate column names. A general JSON validator will only validate the JSON structure but not any of the naming of columns. Any other thoughts on this?
Examples of data:
Expected column names

{
	"solution": "ladkfj",
	"stepName": "1234",
	"stepOrder": 1,
	"origin": "abc",
	"actionTimestamp": "2016-02-08T13:53:20.954-06:00",
	"batch": ,
}

Exceptions of column names

{
	"solutionJJ": "ladkfj",
	"stR2epName": "1234",
	"stepOrder@": 1,
	"": "abc",
	"actionTimestamp": "2016-02-08T13:53:20.954-06:00",
	"batch": ,
}

Re: Exception handling with JSON column names

Anonymous — Thu, 11 Feb 2016 03:59:24 GMT

Hi,
I think ,You want to rename your JSON structure. Yes, It is possible.
First, you extract JSON using tExtractJsonField component
Then, In tExtractJsonField component,
Column XpathQuery
solution "solutionJJ"
stepName " stR2epName"
......
So on.
Note that I roughly extract JSON for your hint. Extract JSON as per your need.
and
Second, pass it to tMap with proper schema and then ,pass it to tWriteJsonField in order to get expected JSON output.
I hope, it will help you.
Regards,
Amol

Re: Exception handling with JSON column names

Anonymous — Thu, 11 Feb 2016 15:37:36 GMT

Thanks for the reply, Amol. In actuality, I'm looking to flag those entries where rows exist which contain columns such as "solutionJJ" or "stR2epName" since we are expecting "solution" and "stepName". Hopefully the screenshot below of the tExtractJsonField component will explain what we're going for.

Re: Exception handling with JSON column names

Anonymous — Fri, 12 Feb 2016 07:29:21 GMT

Hi bbrooksux,
First of all, I liked to know that wheather mentioned json is well formed or not.
Input json is :
{"solutionJJ": "ladkfj", "stR2epName": "1234", "stepOrder@": 1, "": "abc","actionTimestamp": "2016-02-08T13:53:20.954-06:00", "batch": }
Note that I slightly changed your json by removing comma (,) after "]" bracket in order to have perfect json.
When I tried to extract json, I uesd to get below error:
Error on line 1 of document : The content of elements must consist of well-formed character data or markup. Nested exception: The content of elements must consist of well-formed character data or markup.
Interesting thing is that when I made change in json input ,error has gone.
Change in input Json:
{"solutionJJ": "ladkfj", "stR2epName": "1234", "stepOrder": 1, "actionTimestamp": "2016-02-08T13:53:20.954-06:00", "batch": }
First change : I have removed "@" character from stepOrder
Second change: removed "": "abc" node from json
Then error disappeared.
So, Question is "Is your json is well formed" and can you modify your json input as below in order to fulfill your requirement :
{"solutionJJ": "ladkfj", "stR2epName": "1234", "stepOrder": 1, "actionTimestamp": "2016-02-08T13:53:20.954-06:00", "batch": }
Let me know if it is possible.
so we can find solution for it
Regards,
Amol

Re: Exception handling with JSON column names

Anonymous — Fri, 12 Feb 2016 15:10:48 GMT

Hi Amol,
Sorry about the initial bad JSON in the original post. I've been working on some other malformed JSON testing and I guess I grabbed the wrong line! That said, we are able to capture malformed JSON (such as this) with the simple rejection path within Talend (see screenshot).
What this rejection path doesn't capture is the scenario where the naming structure changes (expecting "solution" for column name and get "solutionJJ"). The formatting of the JSON is correct, but the extraction breaks down because it does not see the proper column name. Does this help?