json

Load data from a JSON file.

Introduction

Use the json instruction to parse data from a JSON file. The JSON file must contain a JSON array as the top level object. Define which data from the JSON to parse using the transformation.

Creating a custom script transformation using the json instruction

To create and configure a custom script using the json instruction, follow these steps:

1. Create a custom script transformation.

2. In the Instructions step, select the json instruction.

3. To configure the custom script instruction, fill in the following fields. Required fields are marked with an asterisk (*).

Data Key

Enter the root object of the JSON file. If you do not enter a key, the instruction uses the first node as the root node.

Header

Enter the name of the fields used to identify the header of the JSON file. These fields must match the keys in the JSON file that you want to prase. The values entered here will be used as the header of the data extract.

Sample

If you do not any specify values in the Header fields, enter the number of lines to search through to find the headers.

Mapping

Enter the name of the fields to find parse. In the left field, enter the key in the JSON file to parse (this must match the name of the header provided in the field above). In the right field, enter the name of the source field (this is usually the name as the value entered into the left mapping field and the header).

You can parse date from sub-nodes within a JSON array by appending a node with .sub_node. For example, to access a sub-node called mailHost within an array called init-param, enter init-param.mailHost in the left field.

You can parse data from arrays using the format node[index] where node is the name of the node with the array and index is the array.

Missing

Enter a value to use as an identifier for missing values. For example, enter null to treat any null values in the JSON file as missing.

Example

Below is an example of the contents of a JSON file to parse into a data extract using the json transformation.

{"web-app": {
    "servlet": [   
      {
        "servlet-name": "cofaxCDS",
        "servlet-class": "org.cofax.cds.CDSServlet",
        "init-param": {
          "mailHost": "mail1",}}
      {
        "servlet-name": "cofaxEmail",
        "servlet-class": "org.cofax.cds.EmailServlet",
        "init-param": {
          "mailHost": "mail1",
          "mailHostOverride": "mail2"}},
      {
        "servlet-name": "cofaxAdmin",
        "servlet-class": "org.cofax.cds.AdminServlet"},
      {
        "servlet-name": "fileServlet",
        "servlet-class": "org.cofax.cds.FileServlet"},
      {
        "servlet-name": "cofaxTools",
        "servlet-class": "org.cofax.cms.CofaxToolsServlet",
        "init-param": {
            "mailHost": "mail2",
            "betaServer": true}}],

    "servlet-mapping": {
      "cofaxCDS": "/",
      "cofaxEmail": "/cofaxutil/aemail/*",
      "cofaxAdmin": "/admin/*",
      "fileServlet": "/static/*",
      "cofaxTools": "/tools/*"}}}

Transformation configuration

Data Key

web-app

Header

• servlet-class

• init-param

Sample

1000

Mapping

Header to map	Type	Mapping
servlet-class	string	servlet-class
init-param	string	init-param.mailHost

Missing

Enter the value null

Data table using the json transformation

servlet-class	init-param
org.cofax.cds.CDSServlet	mail1
org.cofax.cds.EmailServlet	mail2
org.cofax.cds.AdminServlet	null
org.cofax.cds.FileServlet	null
org.cofax.cms.CofaxToolsServlet	mail2