Support Questions
Find answers, ask questions, and share your expertise

ConvertJSONToSQL Issue: None of the fields in the JSON map to the columns

New Contributor

Hello,

I'm trying to load the data from a sql server DB to another sql server DB. Please find the flow below.


ExecuteSQL -> ConvertAvroToJSON -> ReplaceText -> ConvertJSONToSQL -> PutSQL


I'm having the issue in the ConvertJSONToSQL processor. Please find the log below. Can you please help me how to fix it.



ConvertJSONToSQL[id=xxxx] Failed to convert StandardFlowFileRecord[uuid=xxxx,claim=StandardContentClaim [resourceClaim=StandardResourceClaim[id=1556828161853-12488, container=default, section=200], offset=596441, length=1116],offset=0,name=11007398837232637,size=1116] to a SQL INSERT statement due to org.apache.nifi.processor.exception.ProcessException: None of the fields in the JSON map to the columns defined by the dbo.xxxx table; routing to failure: org.apache.nifi.processor.exception.ProcessException: None of the fields in the JSON map to the columns defined by the dbo.SITE_TYP table


ConvertJSONToSQL properties

108455-1557030277811.png

Sample Json

[{"TYP_ID": 1, "TYP_NM": "JERY"}]


Target DDL


CREATE TABLE dbo.xxxx(

TYP_ID intIDENTITY(1,1),

TYP_NM varchar(256)NOT NULL,

DEL_FLG char(1)DEFAULT 'N' NOT NULL,

CRT_DTM datetimeDEFAULT GETDATE() NOT NULL,

UPDT_USER_ID varchar(11)DEFAULT CURRENT_USER NOT NULL,

UPDT_DTM datetimeDEFAULT GETDATE() NOT NULL,

CONSTRAINT PK_TYP PRIMARY KEY CLUSTERED (TYP_ID)

)


3 REPLIES 3

Super Guru

What does the generated SQL coming from ConvertJSONToSQL look like?

Are the fields correctly uppercased?

Does your database lowercase the column names?

Did you try setting the "Translate Field Names" property to "true" in ConvertJSONToSQL?

Does the case of the table name in the SQL match the case of the table name in the DB?

If you're using "dbo.xxxx" as the Table Name property in ConvertJSONToSQL, instead try using just "xxxx" as the Table Name, and setting either Catalog Name or Schema Name (depending on your DB) to "dbo" (or DBO if necessary).

New Contributor

Hi,

Have a scenario :

any JSON array, has key in double quotes and value in double quotes, i.e. { "jsonElementKey1" : "jsonElementValue1",...}

convertJSONToSQL processor expects the JSON key and value to be in double quotes, else it can't convert to SQL. For the MS SQL inserts, the string column values expects single quote, so if the above JSON example is converted to {"jsonElementKey1" : 'jsonElementValue1" } using replaceText processor prior to convertJSONtoSQL, the convertJSONtoSQL fails stating single quote as invalid character. Can you help to get out of this deadlock scenario?

 

Thanks,

Sam

New Contributor

Hi, I had the same problem using Oracle database (11g), and this was solved by changing the<Table Name> field and<Schema name> field to UPPER CASE in the ConvertJSONToSQL Processor.