question Re: Convert JSON array to SQL database in Support Questions

Convert JSON array to SQL database

YueikO — Fri, 03 Feb 2023 14:29:33 GMT

Hi all,

I'm very new to NiFi and trying to find ways how to convert json array to be stored in SQL database. I currently have this input json

{ "QueryHandle" : "#qb9ec959946924620a6f46013d745fe03", "Rows" : [ { "Values" : [ "A1", "Test Data A1" ] }, { "Values" : [ "A2", "Test Data A2" ] }, { "Values" : [ "A3", "Test Data A3" ] }, { "Values" : [ "A4", "Test Data A4" ] } ], "TotalRowCount" : 197, "DataTypes" : { "Values" : [ { "ColumnName" : "Value", "Type" : "String" }, { "ColumnName" : "Description", "Type" : "String" } ] } }

Here's the end result I would like to have before I can use PutSQL processor

[ { "VALUES" : "A1", "DESCRIPTION" : "Test Data A1" }, { "VALUES" : "A2", "DESCRIPTION" : "Test Data A2" }, { "VALUES" : "A3", "DESCRIPTION" : "Test Data A3", }, { "VALUES" : "A4", "DESCRIPTION" : "Test Data A4", }

I have tried to use jolttransform, but couldn't be able to remove the array or to add the field name to the data. Eventually, I would like to see this in my DB

VALUE	DESCRIPTION
A1	Test Data A1
A2	Test Data A2
A3	Test Data A3
A4	Test Data A4

Thank you in advance.

Re: Convert JSON array to SQL database

SAMSAL — Fri, 03 Feb 2023 16:48:08 GMT

Hi,

I think this can be achieved using the following flow:

(Data Source) -> SplitJson ->JoltTransformationJSON ->ConcertJSONToSQL->PutSQL

The Configuration for each processor is as follows:

1- SplitJson (To get Each of the Rows Element as flowfile)

JsonPath Expression = $.Rows

2-JoltTransformationJSON (Convert Each Row Values Element to proper format):

Example Input: { "Values": [ "A4", "Test Data A4"] }

Output: { "VALUE" : "A4", "DESCRIPTION" : "Test Data A4" }
Jolt Spec:

[ { "operation": "shift", "spec": { "Values": { "0": "VALUE", "1": "DESCRIPTION" } } } ]

Note: The Output Json Keys has to match the column name in the SQL table

3- ConverJSONToSQL ( This is to convert the jolt output json to sql statement that will feed into the PutSql

Note: you need to Populate the JDBC Connection Pool

4- PutSql (used to execute the insert sql statement generated from above into the DB Table.

Note: You need to create the JDBC Connection Pool. SQL Statement Property remains empty to use the SQL statement from the flowfile generated in step 3.

If that helps please accept solution.

Thanks

Re: Convert JSON array to SQL database

YueikO — Mon, 06 Feb 2023 14:42:25 GMT

Thank you very much!!