Submitted a PR to fix the issue. https://issues.apache.org/jira/browse/NIFI-4729 https://github.com/apache/nifi/pull/2366
I confirmed that JSON Boolean values can be converted (ConvertJSONToSQL, true -> 1, false -> 0), inserted (PutSQL) then retrieved back into JSON object correctly (ExecuteSQL and ConvertRecord 1 -> true, 0 -> false) using following NiFi flow. https://gist.github.com/ijokarumawak/5b8d7dd5d799764dfd13dc6195025785 I hope this to get merged soon and available in the next release. Thanks, Koji On Wed, Jan 3, 2018 at 8:13 AM, Koji Kawamura <[email protected]> wrote: > Hi Jennifer, > > Thank you very much for reporting this. It seems the line converts a > Boolean to "0" or "1" at ConvertJSONToSQL is implemented wrongly. > Looks like a careless mistake. Sorry for the inconvenience. > https://github.com/apache/nifi/commit/8acee02393f9557b9679038b933ba49705984cf8#diff-d99a1a0b78bf07b01f45c26059fc0d67R533 > > I will submit a JIRA and a PR to fix this. > > Thanks, > Koji > > On Wed, Jan 3, 2018 at 6:31 AM, Jennifer Kissinger > <[email protected]> wrote: >> Good morning, >> >> We've uncovered a bug between 1.3.0 and 1.4.0 in the way ConvertJSONToSQL >> handles booleans, which is flipping true to false and vice versa in our >> data. >> >> Our input to ConvertJSONToSQL is a JSON array that contains true/false >> values, like this: >> >> [ >> { >> "foo": true >> }, >> { >> "foo": false >> } >> ... >> ] >> >> After splitting the array and converting to sql, the output of >> ConvertJSONToSQL feeds into PutSQL, which saves the data to a Postgres RDS >> database. >> >> In 1.3.0, the processor outputs attributes from booleans like this: >> ``` >> sql.args.N.type >> -7 >> sql.args.N.value >> t >> ``` >> >> But in 1.4.0, the boolean attributes become: >> >> ``` >> sql.args.N.type >> -7 >> sql.args.N.value >> 0 >> ``` >> >> In both cases, the value above was converted from 'true'. When PutSQL >> executes the sql command, it interprets '0' as false--effectively flipping >> our boolean values when saved to the database. >> >> It looks like this is a collision between >> https://issues.apache.org/jira/browse/NIFI-1613 and >> https://issues.apache.org/jira/browse/NIFI-3372--the latter associates "1" >> and "t" with true in the PutSQL processor, while the former associates "0" >> with true in the ConvertJSON processor (see PR: >> https://github.com/apache/nifi/commit/8acee02393f9557b9679038b933ba49705984cf8#diff-d99a1a0b78bf07b01f45c26059fc0d67R533). >> Personally I would think "1" would be true and "0" would be false, but it >> doesn't matter, as long as it's consistent. >> >> We're rolling back to 1.3.0, but there is functionality in 1.4.0 I would >> love to have--any chance of a patch? >> >> Thanks, >> >> ~Jenni >> >> -- >> Jennifer Kissinger >> Senior Data Engineer >> SemanticBits, LLC >> [email protected] >> 603-290-1711
