To Matt's point, I've tested insert by doing a record field of String going to JSON/JSONB in Postgres and MySQL, and that worked just fine. I'm not sure if we're at a point where we can do a reader with one schema and a writer with another schema, but it should be pretty straight forward to fix so that worst case scenario that is ConvertRecord -> PutDatabaseRecord
On Thu, Dec 15, 2022 at 10:21 PM Matt Burgess <mattyb...@apache.org> wrote: > > 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? > > > > > > > > > > > >
