Geoffrey, The biggest problem with JSON columns across the board is that the JDBC and java.sql.Types specs don't handle them natively, and NiFi records don't recognize JSON as a particular type, we are only interested in the overall datatype such as String since NiFi records can be in any supported format. In my experience these are handled by setting the JSON column to type java.sql.OTHER (like PostgreSQL) and they are willing to accept the value as a String (see NIFI-5901 [1]), and we put in code to handle it as such (see NIFI-5845 [2]). For NiFi it's been more of an ad-hoc type of support where maybe if the SQL type is custom and unique we can handle such things (like sql_variant in MSSQL via NIFI-5819 [3]), but due to the nature of the custom type it's difficult to handle in any sort of consistent way. Happy to hear your thoughts and input, perhaps we can add some ad-hoc support for your use case?
Regards, Matt [1] https://issues.apache.org/jira/browse/NIFI-5901 [2] https://issues.apache.org/jira/browse/NIFI-5845 [3] https://issues.apache.org/jira/browse/NIFI-5819 On Wed, Dec 14, 2022 at 3:55 PM Greene (US), Geoffrey N <geoffrey.n.gre...@boeing.com> wrote: > > Some databases (postgres, sql server, others) support native json columns. > > With postgres, there’s a native jsonb type, with sql server it’s a string > type, that you can treat as json. > > > > In any event, once you have the json in the database, one can then query it, > e.g.: > > > > SELECT id,product_name, > > JSON_VALUE(attributes, '$.material') AS material > > FROM jsontest; > > > > So, here’s my question: > > > > If you have a flow file that contains json, whats the best way to insert that > into a database? > > The only thing I’ve thought of so far is if you have the json string > > {“material” : “plastic”} > > You then use a TEXT processor to turn that into > > {“attributes”: {‘{“material” : “plastic”}’} > > And then use a PutDatabaseRecord to actually write the entry. > > > > Is there a better, or more efficient way to do it? > > > > > >
