[jira] [Commented] (NIFI-4359) Enhance ConvertJSONToSQL processor to handle JSON containing fields having complex type

["ASF GitHub Bot (JIRA)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/NIFI-4359?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16156060#comment-16156060 ] 

ASF GitHub Bot commented on NIFI-4359:
--------------------------------------

GitHub user samratbandgar opened a pull request:

    https://github.com/apache/nifi/pull/2132

    NIFI-4359 Based on field node type, whether value node or not, fetching the value of field.

    Based on field node type, whether it's value node or not, fetching the value of field.
    
    Thank you for submitting a contribution to Apache NiFi.
    
    In order to streamline the review of the contribution we ask you
    to ensure the following steps have been taken:
    
    ### For all changes:
    - [ ] Is there a JIRA ticket associated with this PR? Is it referenced 
         in the commit message?
    
    - [ ] Does your PR title start with NIFI-XXXX where XXXX is the JIRA number you are trying to resolve? Pay particular attention to the hyphen "-" character.
    
    - [ ] Has your PR been rebased against the latest commit within the target branch (typically master)?
    
    - [ ] Is your initial contribution a single, squashed commit?
    
    ### For code changes:
    - [ ] Have you ensured that the full suite of tests is executed via mvn -Pcontrib-check clean install at the root nifi folder?
    - [ ] Have you written or updated unit tests to verify your changes?
    - [ ] If adding new dependencies to the code, are these dependencies licensed in a way that is compatible for inclusion under [ASF 2.0](http://www.apache.org/legal/resolved.html#category-a)? 
    - [ ] If applicable, have you updated the LICENSE file, including the main LICENSE file under nifi-assembly?
    - [ ] If applicable, have you updated the NOTICE file, including the main NOTICE file found under nifi-assembly?
    - [ ] If adding new Properties, have you added .displayName in addition to .name (programmatic access) for each of the new properties?
    
    ### For documentation related changes:
    - [ ] Have you ensured that format looks appropriate for the output in which it is rendered?
    
    ### Note:
    Please ensure that once the PR is submitted, you check travis-ci for build issues and submit an update to your PR as soon as possible.


You can merge this pull request into a Git repository by running:

    $ git pull https://github.com/samratbandgar/nifi NIFI-4359

Alternatively you can review and apply these changes as the patch at:

    https://github.com/apache/nifi/pull/2132.patch

To close this pull request, make a commit to your master/trunk branch
with (at least) the following in the commit message:

    This closes #2132
    
----
commit ea84dbbe1fd96e1bd4da9e71eb9ef710feec3178
Author: Samrat Bandgar <sa...@gmail.com>
Date:   2017-09-06T21:05:36Z

    NIFI-4359
    
    Based on field node type, whether value node or not, fetching the value of field.

----


> 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)