Load data into a Database Table from Json

Activity	sqlInsertFromJson
Description	Load data into a Database Table from Json

Input :

PropertyName	Type	Required	Description
connectionName	STRING	YES	name of the source connection (see DbStore)
TableName	STRING	YES	the table name
SchemaName	STRING	NO	the schema name
FileToken	URL	NO	The file token containing the json file (must be either an Json Array or an Json Object)
Json	JSON	NO	The Json to load (must be either an Json Array or an Json Object) Json Array : One line will be inserted for each element in the Array Json Object : Single element will be inserted
JsonColumn	STRING	NO	Name of the column that will contains the complete json
Columns	DICTIONARY	NO	Mapping between columns and json properties Starting from 1.4.11, The Value can be a JsonPath If not specified the properties name of the json will be used as column name and the property values as values. (if it’s an array, the first item will be used as template)

Output

The insert query

Example1: Insert inner json into a table

Input

JSON

{
   "connectionName": "dbStore1",
   "tableName": "table1",
   "schemaName" : "stg",
   "jsonColumn": "js",
   "json":[
      {"firstname":"Bob","lastname":"Marley"},
      {"firstname":"Frank","lastname":"Zappa"},
      {"firstname":"Billie","lastname":"Holiday"},
   ],
   "columns":{
      "name":"firstname",
      "lastname":"$.lastname"
   }
}

Output

JSON

INSERT INTO stg.table1 (js,name,lastname) VALUES(@js,@p1,@p2)