<!DOCTYPE html><html lang="en"><head><meta charset="utf-8"></meta><title>ConvertJSONToSQL</title><link rel="stylesheet" href="../../../../../css/component-usage.css" type="text/css"></link></head><script type="text/javascript">window.onload = function(){if(self==top) { document.getElementById('nameHeader').style.display = "inherit"; } }</script><body><h1 id="nameHeader" style="display: none;">ConvertJSONToSQL</h1><h2>Description: </h2><p>Converts a JSON-formatted FlowFile into an UPDATE, INSERT, or DELETE SQL statement. The incoming FlowFile is expected to be "flat" JSON message, meaning that it consists of a single JSON element and each field maps to a simple type. If a field maps to a JSON object, that JSON object will be interpreted as Text. If the input is an array of JSON elements, each element in the array is output as a separate FlowFile to the 'sql' relationship. Upon successful conversion, the original FlowFile is routed to the 'original' relationship and the SQL is routed to the 'sql' relationship.</p><h3>Tags: </h3><p>json, sql, database, rdbms, insert, update, delete, relational, flat</p><h3>Properties: </h3><p>In the list below, the names of required properties appear in <strong>bold</strong>. Any other properties (not in bold) are considered optional. The table also indicates any default values, and whether a property supports the <a href="../../../../../html/expression-language-guide.html">NiFi Expression Language</a>.</p><table id="properties"><tr><th>Display Name</th><th>API Name</th><th>Default Value</th><th>Allowable Values</th><th>Description</th></tr><tr><td id="name"><strong>JDBC Connection Pool</strong></td><td>JDBC Connection Pool</td><td></td><td id="allowable-values"><strong>Controller Service API: </strong><br/>DBCPService<br/><strong>Implementations: </strong><a href="../../../nifi-hadoop-dbcp-service-nar/1.19.1/org.apache.nifi.dbcp.HadoopDBCPConnectionPool/index.html">HadoopDBCPConnectionPool</a><br/><a href="../../../nifi-dbcp-service-nar/1.19.1/org.apache.nifi.dbcp.DBCPConnectionPoolLookup/index.html">DBCPConnectionPoolLookup</a><br/><a href="../../../nifi-dbcp-service-nar/1.19.1/org.apache.nifi.dbcp.DBCPConnectionPool/index.html">DBCPConnectionPool</a><br/><a href="../../../nifi-dbcp-service-nar/1.19.1/org.apache.nifi.dbcp.HikariCPConnectionPool/index.html">HikariCPConnectionPool</a></td><td id="description">Specifies the JDBC Connection Pool to use in order to convert the JSON message to a SQL statement. The Connection Pool is necessary in order to determine the appropriate database column types.</td></tr><tr><td id="name"><strong>Statement Type</strong></td><td>Statement Type</td><td></td><td id="allowable-values"><ul><li>UPDATE</li><li>INSERT</li><li>DELETE</li><li>Use statement.type Attribute</li></ul></td><td id="description">Specifies the type of SQL Statement to generate</td></tr><tr><td id="name"><strong>Table Name</strong></td><td>Table Name</td><td></td><td id="allowable-values"></td><td id="description">The name of the table that the statement should update<br/><strong>Supports Expression Language: true (will be evaluated using flow file attributes and variable registry)</strong></td></tr><tr><td id="name">Catalog Name</td><td>Catalog Name</td><td></td><td id="allowable-values"></td><td id="description">The name of the catalog that the statement should update. This may not apply for the database that you are updating. In this case, leave the field empty<br/><strong>Supports Expression Language: true (will be evaluated using flow file attributes and variable registry)</strong></td></tr><tr><td id="name">Schema Name</td><td>Schema Name</td><td></td><td id="allowable-values"></td><td id="description">The name of the schema that the table belongs to. This may not apply for the database that you are updating. In this case, leave the field empty<br/><strong>Supports Expression Language: true (will be evaluated using flow file attributes and variable registry)</strong></td></tr><tr><td id="name">Translate Field Names</td><td>Translate Field Names</td><td id="default-value">true</td><td id="allowable-values"><ul><li>true</li><li>false</li></ul></td><td id="description">If true, the Processor will attempt to translate JSON field names into the appropriate column names for the table specified. If false, the JSON field names must match the column names exactly, or the column will not be updated</td></tr><tr><td id="name">Unmatched Field Behavior</td><td>Unmatched Field Behavior</td><td id="default-value">Ignore Unmatched Fields</td><td id="allowable-values"><ul><li>Ignore Unmatched Fields <img src="../../../../../html/images/iconInfo.png" alt="Any field in the JSON document that cannot be mapped to a column in the database is ignored" title="Any field in the JSON document that cannot be mapped to a column in the database is ignored"></img></li><li>Fail <img src="../../../../../html/images/iconInfo.png" alt="If the JSON document has any field that cannot be mapped to a column in the database, the FlowFile will be routed to the failure relationship" title="If the JSON document has any field that cannot be mapped to a column in the database, the FlowFile will be routed to the failure relationship"></img></li></ul></td><td id="description">If an incoming JSON element has a field that does not map to any of the database table's columns, this property specifies how to handle the situation</td></tr><tr><td id="name">Unmatched Column Behavior</td><td>Unmatched Column Behavior</td><td id="default-value">Fail on Unmatched Columns</td><td id="allowable-values"><ul><li>Ignore Unmatched Columns <img src="../../../../../html/images/iconInfo.png" alt="Any column in the database that does not have a field in the JSON document will be assumed to not be required.  No notification will be logged" title="Any column in the database that does not have a field in the JSON document will be assumed to not be required.  No notification will be logged"></img></li><li>Warn on Unmatched Columns <img src="../../../../../html/images/iconInfo.png" alt="Any column in the database that does not have a field in the JSON document will be assumed to not be required.  A warning will be logged" title="Any column in the database that does not have a field in the JSON document will be assumed to not be required.  A warning will be logged"></img></li><li>Fail on Unmatched Columns <img src="../../../../../html/images/iconInfo.png" alt="A flow will fail if any column in the database that does not have a field in the JSON document.  An error will be logged" title="A flow will fail if any column in the database that does not have a field in the JSON document.  An error will be logged"></img></li></ul></td><td id="description">If an incoming JSON element does not have a field mapping for all of the database table's columns, this property specifies how to handle the situation</td></tr><tr><td id="name">Update Keys</td><td>Update Keys</td><td></td><td id="allowable-values"></td><td id="description">A comma-separated list of column names that uniquely identifies a row in the database for UPDATE statements. If the Statement Type is UPDATE and this property is not set, the table's Primary Keys are used. In this case, if no Primary Key exists, the conversion to SQL will fail if Unmatched Column Behaviour is set to FAIL. This property is ignored if the Statement Type is INSERT<br/><strong>Supports Expression Language: true (will be evaluated using flow file attributes and variable registry)</strong></td></tr><tr><td id="name">Quote Column Identifiers</td><td>jts-quoted-identifiers</td><td id="default-value">false</td><td id="allowable-values"><ul><li>true</li><li>false</li></ul></td><td id="description">Enabling this option will cause all column names to be quoted, allowing you to use reserved words as column names in your tables.</td></tr><tr><td id="name">Quote Table Identifiers</td><td>jts-quoted-table-identifiers</td><td id="default-value">false</td><td id="allowable-values"><ul><li>true</li><li>false</li></ul></td><td id="description">Enabling this option will cause the table name to be quoted to support the use of special characters in the table name</td></tr><tr><td id="name"><strong>SQL Parameter Attribute Prefix</strong></td><td>jts-sql-param-attr-prefix</td><td id="default-value">sql</td><td id="allowable-values"></td><td id="description">The string to be prepended to the outgoing flow file attributes, such as &lt;sql&gt;.args.1.value, where &lt;sql&gt; is replaced with the specified value<br/><strong>Supports Expression Language: true (will be evaluated using flow file attributes and variable registry)</strong></td></tr><tr><td id="name"><strong>Table Schema Cache Size</strong></td><td>table-schema-cache-size</td><td id="default-value">100</td><td id="allowable-values"></td><td id="description">Specifies how many Table Schemas should be cached</td></tr></table><h3>Relationships: </h3><table id="relationships"><tr><th>Name</th><th>Description</th></tr><tr><td>sql</td><td>A FlowFile is routed to this relationship when its contents have successfully been converted into a SQL statement</td></tr><tr><td>failure</td><td>A FlowFile is routed to this relationship if it cannot be converted into a SQL statement. Common causes include invalid JSON content or the JSON content missing a required field (if using an INSERT statement type).</td></tr><tr><td>original</td><td>When a FlowFile is converted to SQL, the original JSON FlowFile is routed to this relationship</td></tr></table><h3>Reads Attributes: </h3>None specified.<h3>Writes Attributes: </h3><table id="writes-attributes"><tr><th>Name</th><th>Description</th></tr><tr><td>mime.type</td><td>Sets mime.type of FlowFile that is routed to 'sql' to 'text/plain'.</td></tr><tr><td>&lt;sql&gt;.table</td><td>Sets the &lt;sql&gt;.table attribute of FlowFile that is routed to 'sql' to the name of the table that is updated by the SQL statement. The prefix for this attribute ('sql', e.g.) is determined by the SQL Parameter Attribute Prefix property.</td></tr><tr><td>&lt;sql&gt;.catalog</td><td>If the Catalog name is set for this database, specifies the name of the catalog that the SQL statement will update. If no catalog is used, this attribute will not be added. The prefix for this attribute ('sql', e.g.) is determined by the SQL Parameter Attribute Prefix property.</td></tr><tr><td>fragment.identifier</td><td>All FlowFiles routed to the 'sql' relationship for the same incoming FlowFile (multiple will be output for the same incoming FlowFile if the incoming FlowFile is a JSON Array) will have the same value for the fragment.identifier attribute. This can then be used to correlate the results.</td></tr><tr><td>fragment.count</td><td>The number of SQL FlowFiles that were produced for same incoming FlowFile. This can be used in conjunction with the fragment.identifier attribute in order to know how many FlowFiles belonged to the same incoming FlowFile.</td></tr><tr><td>fragment.index</td><td>The position of this FlowFile in the list of outgoing FlowFiles that were all derived from the same incoming FlowFile. This can be used in conjunction with the fragment.identifier and fragment.count attributes to know which FlowFiles originated from the same incoming FlowFile and in what order the SQL FlowFiles were produced</td></tr><tr><td>&lt;sql&gt;.args.N.type</td><td>The output SQL statements are parametrized in order to avoid SQL Injection Attacks. The types of the Parameters to use are stored in attributes named &lt;sql&gt;.args.1.type, &lt;sql&gt;.args.2.type, &lt;sql&gt;.args.3.type, and so on. The type is a number representing a JDBC Type constant. Generally, this is useful only for software to read and interpret but is added so that a processor such as PutSQL can understand how to interpret the values. The prefix for this attribute ('sql', e.g.) is determined by the SQL Parameter Attribute Prefix property.</td></tr><tr><td>&lt;sql&gt;.args.N.value</td><td>The output SQL statements are parametrized in order to avoid SQL Injection Attacks. The values of the Parameters to use are stored in the attributes named sql.args.1.value, sql.args.2.value, sql.args.3.value, and so on. Each of these attributes has a corresponding &lt;sql&gt;.args.N.type attribute that indicates how the value should be interpreted when inserting it into the database.The prefix for this attribute ('sql', e.g.) is determined by the SQL Parameter Attribute Prefix property.</td></tr></table><h3>State management: </h3>This component does not store state.<h3>Restricted: </h3>This component is not restricted.<h3>Input requirement: </h3>This component requires an incoming relationship.<h3>System Resource Considerations:</h3>None specified.<h3>See Also:</h3><p><a href="../org.apache.nifi.processors.standard.PutSQL/index.html">PutSQL</a></p></body></html>