Convert string to boolean with Pentaho Kettle

Question

I'm moving an Oracle db to a Mongo DB, using Pentaho Kettle. I've succeded in doing almost everything I wanted, but I just have a last problem.

In the oracle db, they have a field that have two states : Y or N, instead of a nice boolean true and false. I wanted to make a mapping in the transformation to say that Y is true and N is false, but Pentaho translates this as strings, and not like real true and false.

Do I have an easy way to set up my booleans?

Thanks in advance!

Answer 1

You change the type with the Select values step, on the Meta-data panel.

And it works !

However, as the string representation of boolean in PDI is Y/N as well as true/false depending on the context, the guilty step may be the Mongo DB Output.

Right-click on the Oracle Table Input, select output field and check the type of your field. if it is not boolean, convert it with the Select value.

Then right-click on the Mongo DB Output, select input field and check the type of your field. If it is boolean, then it gets silently converted to Y/N. In that case, use a Replace in string step and convert Y/N to "true"/"false" as strings and let Mongo DB silently convert these special string to boolean.