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

Insert JSON to Database (PutDatabaseRecord)

Insert JSON to Database (PutDatabaseRecord)

85557-putdatabaserecord.png

85558-jsontreereader.png

Hello,

I would like to use PutDatabaseRecord, to insert to oracle tables milions of rows from JSON file. So i start with two records from json, but I came across a problem,

PutDatabaseRecord[id=70264994-b0a7-1501-0000-00003bbb7199] Failed to process StandardFlowFileRecord[uuid=afa320f6-9d5f-4e91-a7fc-abb3136464d0,claim=StandardContentClaim [resourceClaim=StandardResourceClaim[id=1533633308238-185, container=default, section=185], offset=567109, length=32],offset=0,name=1138608139480143,size=32] due to None of the fields in the record map to the columns defined by the ... table: 

My flow for test:

Generateflowfile (JSON) --> PutDatabaseRecord (with JSONTreeReader)

I used different properties for processor PutDatabaseRecord for (Translate Field Names/Unmatched Field Behavior/Unmatched Column Behavior/Quote Column Identifiers/Quote Table Identifiers) base on this thread: none of the fields in the records map to the columns CSV to putDatabaseRecord

I also created flow with CSV flowfile, but nothing change, the same error occured

Table in database:

COLUMN NAME DATA TYPE

TEST_ID NUMBER

STRING VARCHAR2 (3000 CHAR)

85556-generateflowfile.png

1 REPLY 1
Highlighted

Re: Insert JSON to Database (PutDatabaseRecord)

Super Guru

Translate Field Names "normalizes" the column names by uppercasing them, but also by removing the underscores, which should explain why TEST_ID isn't matching, but I can't tell why STRING isn't matching. Can you try setting the field names in the schema to their uppercase counterparts, as well as the keys in the JSON file?

For JSON inputs, you can also use JoltTransformJSON (for a flat JSON file of simple key/value pairs) check out this spec which lowercases the field names, you can change the modify function to =toUpper instead of =toLower.