Pentaho Data Integration JSON Output /" instead of "

Question

Experiencing an issue with Pentaho JSON transformations. I imagine it is a simple issue I just don't know the tool. I have a JSON input and a JSON output, I am using the auto field mapping.

INPUT:

{
   "ArrayExample":[
      {
         "fruitId":"asdasg22389khsdall"
      }
   ],
   "name":"Test",
   "fruits":[
      "Apples",
      "Bananas"
   ]
}

OUTPUT:

{
   "data":[
      {
         "ArrayExample":"[{\"fruitId\":\"asdasg22389khsdall\"}]",
         "fruits":"[\"Apples\",\"Bananas\"]",
         "name":"Test"
      }
   ]
}

I am trying to just get "Apples" instead of \ "Apples \ " but I cannot seem to find the answers. I want to use the JSON in an API call, but the API call will not work with the \ ". I even tried using another API tool called Talend, which was having the same issue and even more.

Any help [greatly appreciated], at my wits' end.

configuration example link

Answer 1

I have recreated your transformation in my environment, and received the same result. However, I have noticed the following:

1. Preview Data tab is showing the data with regular double quote, instead of escape-char double quote (").
2. When I try to continue the transformation, i.e. using JSON Output as an input for the next stage, it is aware of the escape-char double quote and transforms it into regular double quote.

Therefore, my conclusion is that you should either continue using the JSON in API call inside Pentaho, so it can process the escape-char double quote automatically, or you should post-process the file as regular text file, in which you will manually transform each escape-char double quote into regular double quote.