[ https://issues.apache.org/jira/browse/NIFI-4359?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16200619#comment-16200619 ]
ASF GitHub Bot commented on NIFI-4359: -------------------------------------- Github user mattyb149 commented on a diff in the pull request: https://github.com/apache/nifi/pull/2132#discussion_r144077813 --- Diff: nifi-nar-bundles/nifi-standard-bundle/nifi-standard-processors/src/main/java/org/apache/nifi/processors/standard/ConvertJSONToSQL.java --- @@ -37,6 +37,7 @@ import java.util.Set; import java.util.UUID; import java.util.concurrent.atomic.AtomicReference; +import org.apache.commons.lang.StringEscapeUtils; --- End diff -- All other NiFi code that uses StringEscapeUtils refers to the Commons Lang 3 class `org.apache.commons.lang3.StringEscapeUtils` > Enhance ConvertJSONToSQL processor to handle JSON containing fields having > complex type > --------------------------------------------------------------------------------------- > > Key: NIFI-4359 > URL: https://issues.apache.org/jira/browse/NIFI-4359 > Project: Apache NiFi > Issue Type: Improvement > Components: Extensions > Reporter: Samrat Vilasrao Bandgar > Attachments: NIFI-4359.patch > > > Processor: ConvertJSONToSQL > *Problem statement: * > Sample JSON: > {noformat} > { > "prop1": "value1", > "prop2": "value2", > "prop3": "value3", > "prop4": { > "prop5": "value5", > "prop6": "value6" > } > } > {noformat} > Sample table: > {noformat} > mysql> desc mytable; > +-------+------------+------+-----+---------+----------------+ > | Field | Type | Null | Key | Default | Extra | > +-------+------------+------+-----+---------+----------------+ > | id | bigint(20) | NO | PRI | NULL | auto_increment | > | prop1 | char(30) | NO | | NULL | | > | prop2 | char(30) | NO | | NULL | | > | prop3 | char(30) | NO | | NULL | | > | prop4 | text | NO | | NULL | | > +-------+------------+------+-----+---------+----------------+ > 5 rows in set (0.00 sec) > {noformat} > With the above mentioned sample json and table, I want to convert the json > into insert sql in such a way that prop4 column will get inserted with value > {"prop5":"value5","prop6":"value6"}. However, when I use the current > ConvertJSONToSQL processor, prop4 column gets inserted with empty string. > *Expected:* > {noformat} > mysql> select * from mytable; > +----+--------+--------+--------+-------------------------------------+ > | id | prop1 | prop2 | prop3 | prop4 | > +----+--------+--------+--------+-------------------------------------+ > | 1 | value1 | value2 | value3 | {"prop5":"value5","prop6":"value6"} | > +----+--------+--------+--------+-------------------------------------+ > 1 row in set (0.00 sec) > {noformat} > *Actual:* > {noformat} > mysql> select * from mytable; > +----+--------+--------+--------+----------+ > | id | prop1 | prop2 | prop3 | prop4 | > +----+--------+--------+--------+----------+ > | 1 | value1 | value2 | value3 | | > +----+--------+--------+--------+----------+ > 1 row in set (0.00 sec) > {noformat} > *Attributes details captured from Provenance Event UI for the above use case > are:* > sql.args.1.type > 1 > sql.args.1.value > value1 > sql.args.2.type > 1 > sql.args.2.value > value2 > sql.args.3.type > 1 > sql.args.3.value > value3 > sql.args.4.type > -1 > sql.args.4.value > {color:red}Empty string set{color} > sql.table > mytable > The ConvertJSONToSQL.java has a method createSqlStringValue(final JsonNode > fieldNode, final Integer colSize, final int sqlType) which is responsible for > populating attribute values for each column. This method uses below line to > get field value. > {code:java} > String fieldValue = fieldNode.asText(); > {code} > Documentation for org.codehaus.jackson.JsonNode.asText() method tells us: > *asText()* > Method that will return valid String representation of the container value, > if the node is a value node (method isValueNode() returns true), otherwise > empty String. > Since prop4 in this case is not a value node, empty string is returned and > get set to attribute value for the column prop4. > Suggested improvement is as below. > If the fieldNode is value node, use asText() else use toString() with > StringEscapeUtils.escapeSql() to take of characters like quotes in insert > query. I have tested this locally. Please let me know if it makes sense to > add this improvement. I will attach the patch file for code changes. > Thanks -- This message was sent by Atlassian JIRA (v6.4.14#64029)