I would like to build a CSV file, pick the schema based on the header of
CSV file and then move into SQL server.
For example:
My JSON:
[{ "query": {"count": 1,"created": "2017-09-15T11:20:26Z","lang":
"en-US","results": {"channel": {"item": {"condition":{"code": "33","date":
"Fri, 15 Sep 2017 06:00 AM EDT","temp": "63","text": "Mostly Clear"}}}}}
}, { "query": {"count": 1,"created": "2017-09-15T11:20:28Z","lang":
"en-US","results": {"channel": {"item": {"condition":{"code": "331","date":
"Fri, 15 Sep 2017 06:10 AM EDT","temp": "631","text": "Mostly Clear1"}}}}}
}]
MY CSV expectation:
"query__count","query__created","query__lang","query__results__channel__item__condition__code","query__results__channel__item__condition__date","query__results__channel__item__condition__temp","query__results__channel__item__condition__text"
"1","2017-09-15T11:20:26Z","en-US","33","Fri, 15 Sep 2017 06:00 AM
EDT","63","Mostly Clear"
"1","2017-09-15T11:20:28Z","en-US","331","Fri, 15 Sep 2017 06:10 AM
EDT","631","Mostly Clear1"
Now, I will create schema based on the resultant CSV file header and move
to SQL. I need a way to handle complex nested JSON into proper dot notation
header CSV file. Please share your suggestion.
Regards,
Prabhu Mahendran
On Thu, Aug 9, 2018 at 2:10 PM Pierre Villard <pierre.villard...@gmail.com>
wrote:
> Wondering how you're going to manage insertion of data in a SQL database
> if you don't know anything about the schema of your data?
>
> 2018-08-09 6:46 GMT+02:00 Mahendra prabhu <prabhuu161...@gmail.com>:
>
>> I could write JQ for direct json values, but for dynamic json nested
>> object I couldn't achieve with JQ. Can you please share your suggestion?
>> major goal is to move complex nested json values into SQL after using
>> transformation with expression language. If I get the intermediate result
>> into CSV, it is wasy to use transformation, create insert query and move to
>> SQL.
>>
>> Regards,
>> Prabhu Mahendran
>>
>> On Tue, Aug 7, 2018 at 6:26 PM dan young <danoyo...@gmail.com> wrote:
>>
>>> You could look at using ExecuteStreamCommand + JQ. We use this pattern a
>>> lot...
>>>
>>> Regards
>>>
>>> Dano
>>>
>>> On Tue, Aug 7, 2018, 5:09 AM Mahendra prabhu <prabhuu161...@gmail.com>
>>> wrote:
>>>
>>>> Expected behaivor is similiar to this task created -
>>>> https://issues.apache.org/jira/browse/NIFI-4398 I couldnt achieve this
>>>> using ConvertRecord. Can you guide to proceed further to handle nested json
>>>> elements ?
>>>>
>>>> Thanks in advance
>>>>
>>>> On Tue, Aug 7, 2018 at 4:31 PM Mahendra prabhu <prabhuu161...@gmail.com>
>>>> wrote:
>>>>
>>>>> Hi Pierre,
>>>>>
>>>>> I need to move nested json data into SQL table after using expression
>>>>> language. So I thought to use this processor, and do transformation for
>>>>> the
>>>>> resultant CSV and move to SQL.
>>>>>
>>>>> Regards,
>>>>> Prabhu Mahendran
>>>>>
>>>>> On Tue, Aug 7, 2018 at 3:59 PM Pierre Villard <
>>>>> pierre.villard...@gmail.com> wrote:
>>>>>
>>>>>> Hi,
>>>>>>
>>>>>> I believe it can be closed because it's doable with ConvertRecord
>>>>>> processor (which is the recommended approach).
>>>>>> Does it answer your requirement ?
>>>>>>
>>>>>> Thanks,
>>>>>> Pierre
>>>>>>
>>>>>> 2018-08-07 11:43 GMT+02:00 Mahendra prabhu <prabhuu161...@gmail.com>:
>>>>>>
>>>>>>> Hi Folks,
>>>>>>>
>>>>>>> Is there is plan to include this feature or dropped -
>>>>>>> https://issues.apache.org/jira/browse/NIFI-1583 ?
>>>>>>>
>>>>>>> Is this PR closed because of licensing or incomplete rebase of code ?
>>>>>>>
>>>>>>>
>>>>>>> Regards,
>>>>>>> Prabhu Mahendran
>>>>>>>
>>>>>>
>>>>>>
>
