Export Sql query result to json

Activity	exportToJson
Description	Extract data from a DbStore into a JSONfile

Input :

PropertyName	Rule of use (if revelant)	Type	Required	Description
connectionName		STRING	YES	name of the source connection (see DbStore)
QueryFileToken	If QueryFileToken is filled in, the content of the file specified will be used instead of Query	STRING	YES	Filetoken containing the query to execute
Query		STRING	YES	SQL Query to execute
Params		PARAMETERS ARRAY	NO	Query parameters
CommandTimeout		INT	NO	Execution timeout of the SQL query in the database
JsonColumnNames		STRING ARRAY	NO	Name of the columns containing JSON content (these columns need to be identified to include the content in the output in JSONformat)
OutputFileStoreName		STRING	YES	Output fileStore name
OutputFileName		STRING	YES	Name of the output file (.JSONextension will be added automatically)

Parameters

PropertyName	Type	Required	Default Value	Description
Name	STRING	YES		name of the parameter
Value	STRING	NO		value of the parameter

Output

JSONfile token

Example1: Export a query into Json

Input

JSON

{
   "connectionName": "dbStore1",
   "query":"SELECT name, role, acl FROM employees WHERE name = 'Bob'",
   "jsonColumnNames": ["acl"]
   "outputFileStoreName": "ftp1",
   "outputFileName": "employees"
}

Output

JSON

ftp://ftp1/employees.json

File content

JSON

[
   {
      "name": "bob",
      "role": "sales",
      "acl": {
          "id":1412,
          "name":"aclname"
      }
   },
   {
      "name": "bob",
      "role": "operations",
      "acl": {
          "id":4557,
          "name":"aclname"
      }
   }
]