Unable to save JSON to database with field named as order (NiFi)

Question

I have a JSON file:

[ {
  "Order" : "Nestle billboard 100%x250",
  "Country" : "Russia",
  "Order_ID" : 287259619,
  "Country_ID" : 243,
  "Order_lifetime_impressions" : "3385377",
  "Total_unique_visitors" : "1090850",
  "Total_reach_impressions" : "3385525",
  "Average_impressions_unique_visitor" : 3.1,
  "Date" : "2021-07-01"
}, {
  "Order" : "Nestle_june_july 2021_ mob 300x250",
  "Country" : "Russia",
  "Order_ID" : 28734,
  "Country_ID" : 263,
  "Order_lifetime_impressions" : "1997022",
  "Total_unique_visitors" : "1012116",
  "Total_reach_impressions" : "1997036",
  "Average_impressions_unique_visitor" : 1.97,
  "Date" : "2021-07-01"
}]

And table with the same column names. I'm using PutDatabaseRecord processor with this configuration:

When I'm trying to save this file, I get an error.

ERROR: syntax error (at or near: ",") Position: 110

I renamed column in the table and in the json to order_name and processor was able to save it.

But I still want to save it as order if it possible.

I really dont understand why this happens. Yes, order is a keyword for sql, but it's inside ". Is it a bug? How can I fix it without renaming columns?

If I will keep Order as column in JSON, but change column name in database - works fine as well. But of course, I cannot save Order to this renamed column.

The problem could be related with the float on line 10 `"Average_impressions_unique_visitor" : 3.1`, have you tried inserting as text `"Average_impressions_unique_visitor" : "3.1",`? If it works, then you know the problem is there. — Rui Costa, Jul 08 '21 at 10:00
https://www.postgresql.org/docs/current/sql-keywords-appendix.html Order is a reserved word and should not be used as a column name, you will run in to issues. https://stackoverflow.com/questions/10891368/postgres-table-column-name-restrictions — Sdairs, Jul 08 '21 at 12:08
I have no experience in `apache-nifi` but I assume it creates some kind of SQL query in the background that has the SQL keyword `ORDER` in it, because you used it as a column name. That's a really bad idea, but you might be able to work around it by specifying the scheme in the JSON... Have you tried ```"public.Order" : "Nestle_june_july 2021_ mob 300x250",``` (replacing public with the according schema you're using)? — SvenTUM, Jul 08 '21 at 12:28

score 1 · Accepted Answer · answered Jul 08 '21 at 12:12

Order is a reserved word and you should absolutely avoid using it as a column name if you can. [1] [3]

If you absolutely can't, you need to set the Quote Column Identifiers property to True in the PutDatabaseRecord processor config. [2]

Unable to save JSON to database with field named as order (NiFi)

1 Answers1