This section describes how to define a file connection and upload a JSON schema for an input file. To define an output JSON file connection and schema, see Setting up JSON metadata for an output file.
Defining the general properties
In the wizard, fill in the general information in the relevant fields to identify the JSON file metadata, including Name, Purpose and Description.
The Name field is required, and the information you provide in the Description field will appear as a tooltip when you move your mouse pointer over the file connection.
In this step, it is advisable to enter information that will help you distinguish between your input and output connections, which will be defined in the next step.
If needed, set the version and status in the Version and Status fields respectively.
If needed, click the Select button next to the Path field to select a folder under the File Json node to hold your newly created file connection.
Click Next to select the type of metadata.
Setting the type of metadata and loading the input file
In the dialog box, select Input Json and click Next to proceed to the next step of the wizard to load the input file.
From the Read By list box, select the type of query to read the source JSON file.
JsonPath: read the JSON data based on a JsonPath query.
This is the default and recommended query type to read JSON data in order to gain performance and to avoid problems that you may encounter when reading JSON data based on an XPath query.
Xpath: read the JSON data based on an XPath query.
Click Browse... and browse your directory to the JSON file to be uploaded. Alternatively, enter the full path to the file or the URL that links to the JSON file.
The Schema Viewer area displays a preview of the JSON structure. You can expand and visualize every level of the file's JSON tree structure.
Enter the Encoding type in the corresponding field if the system does not detect it automatically.
In the Limit field, enter the number of columns on which the JsonPath or XPath query is to be executed, or 0 if you want to run it against all of the columns.
Click Next to define the schema parameters.
Defining the schema
In this step you will set the schema parameters.
The schema definition window is composed of four views:
Tree view of the JSON file.
Extraction and iteration information.
Preview of the target schema, together with the input data of the selected columns displayed in the defined order.
Preview of the JSON file's data.
Populate the Path loop expression field with the absolute JsonPath or XPath expression, depending on the type of query you have selected, for the node to be iterated upon. There are two ways to do this, either:
enter the absolute JsonPath or XPath expression for the node to be iterated upon (enter the full expression or press Ctrl+Space to use the autocompletion list),
drag the loop element node from the tree view under Source schema into the Absolute path expression field of the Path loop expression table.
An orange arrow links the node to the corresponding expression.
The Path loop expression definition is mandatory.
In the Loop limit field, specify the maximum number of times the selected node can be iterated.
Define the fields to be extracted by dragging the nodes from the Source Schema tree into the Relative or absolute path expression fields of the Fields to extract table.
You can select several nodes to drop onto the table by pressing Ctrl or Shift and clicking the nodes of interest.
If needed, you can add as many columns to be extracted as necessary, delete columns or change the column order using the toolbar:
Add or delete a column using the and buttons.
Change the order of the columns using the and buttons.
If you want your file schema to have different column names than those retrieved from the input file, enter new names in the corresponding Column name fields.
Click Refresh Preview to preview the target schema. The fields are consequently displayed in the schema according to the defined order.
Click Next to finalize the schema.
Finalizing the schema
The last step of the wizard shows the end schema generated and allows you to customize the schema according to your needs.
If needed, rename the schema (by default, metadata) and leave a comment.
Customize the schema if needed: add, remove or move schema columns, export the schema to an XML file, or replace the schema by importing an schema definition XML file using the tool bar.
If the JSON file which the schema is based on has been changed, click the Guess button to generate the schema again. Note that if you have customized the schema, the Guess feature does not retain these changes.
Click Finish. The new file connection, along with its schema, is displayed under the relevant File Json metadata node in the Repository tree view.
Now you can drag and drop the file connection or the schema of it from the Repository tree view onto the design workspace as a new tFileInputJSON or tExtractJSONFields component or onto an existing component to reuse the metadata. For further information about how to use the centralized metadata in a Job, see How to use centralized metadata in a Joband How to set a repository schema.
To modify an existing file connection, right-click it from the Repository tree view, and select Edit JSON to open the file metadata setup wizard.
To add a new schema to an existing file connection, right-click the connection from the Repository tree view and select Retrieve Schema from the contextual menu.
To edit an existing file schema, right-click the schema from the Repository tree view and select Edit Schema from the contextual menu.