This is a map-type SmartModule that converts arbitrary JSON records into SQL model, which is a self-describing representation of SQL INSERT statements. This SmartModule is intended to be used in [SQL Sink Connector][sql-sink-connector], to execute a command in a SQL database.
Mapping
The mapping between incoming JSON records and the resulting SQL record is defined in the configuration of the SmartModule in the mapping init parameter. Let’s look at the example:
Here, we create insert statements to target_table database table. Each statement has three columns:
device_id with int type. The value for this column will looked-up at the following hierarchy $.device.device_id of the input record. If it is not present, the error will be thrown, as the mapping states, it is a required field.
device_type - text field with mobile marked as default value. Not required. Will be taken from $.device.type hierarchy.
record - jsonb column that contains the whole input record.
With the given mapping, the Json-Sql SmartModule will convert the input:
$ smdk test --text '{"device":{"device_id":1}}' --transforms-file ./transform.yaml
{"Insert":{"table":"target_table","values":[{"column":"record","raw_value":"{\"device\":{\"device_id\":1}}","type":"Json"},{"column":"type","raw_value":"mobile","type":"Text"},{"column":"device_id","raw_value":"1","type":"Int"}]}}
As mentioned at the beginning of this page, the outputed records can be consumed by SQL Sink Connector to be executed on SQL database. By convention, SQL Sink connector expects JSON inputs.