JSON Input

The JSON Input tool reads JSON-formatted records and transforms nested data into a relational data model. The tool produces multiple outputs. The number of outputs and their kinds (labels) is determined by the tool's configuration.

To automate output definition, you can import a predefined schema specifying field names, field descriptions, and (optionally) field sizes. Note that linkage fields (ID fields that associate parent and child records) must be of type Integer (4).

JSON Input tool configuration parameters

The JSON Input tool has two sets of configuration parameters in addition to the standard execution options:

Configuration

Options

Multi-doc mode

Data Management's JSON Input tool supports the processing of files and fields containing multiple JSON documents with Multi-doc mode.

Multi-doc mode has four options:

• Single document

• Document per line

• Multi-line documents

• Map-enclosed array

• Multi-document mashup

Single document

Each file or field contains a single JSON document. (This is the default option.) For example, both of the following are valid Single document inputs:

A single map:

{ 
    "first": "joe", 
    "last": "smith" 
}

An array of maps:

[
    { 
        "first": "joe", 
        "last": "smith" 
    },
    { 
        "first": "mary", 
        "last": "lamb" 
    },
    { 
        "first": "george", 
        "last": "washington" 
    }
]

Document per line

• Each file or field contains multiple JSON documents, with one document per line of text.

• These documents may have trailing commas, and may be enclosed by surrounding array brackets [] on separate lines.

• This is commonly seen in Twitter feed archives.

For example, all of the following are valid Document per line inputs:

A list of maps:

{ "first" : "joe", "last" : "smith" }
{ "first" : "mary", "last" : "lamb" }
{ "first" : "george", "last" : "washington" }

A list of maps with trailing commas:

{ "first" : "joe", "last" : "smith" },
{ "first" : "mary", "last" : "lamb" },
{ "first" : "george", "last" : "washington" }

A list of maps with surrounding brackets on their own lines:

[{ "first" : "joe", "last" : "smith" }]
[{ "first" : "mary", "last" : "lamb" }]
[{ "first" : "george", "last" : "washington" }]

Multi-line documents

• Each file or field contains multiple JSON documents, and each document spans more than one line.

• The documents may be separated by commas.

• The entire input may be surrounded by array brackets [], in which case the brackets are ignored and the input is treated as a series of documents.

• In order for the JSON Input tool to find breaks between documents, the input must adhere to this rule: The closing } of each JSON document must be the first non-whitespace character on the line, and must appear at an indentation level less than or equal to the indentation level of the opening {.

For example, this is a valid Multi-line documents input:

{
  "first" : "joe", "last" : "smith" 
}
{ 
  "first" : "mary", "last" : "lamb" 
}
{ 
  "first" : "george", "last" : "washington"
}

This is a valid Multi-line documents input:

[{ 
  "first" : "joe", "last" : "smith" 
},
{ 
  "first" : "mary", "last" : "lamb" 
},
{ 
  "first" : "george", "last" : "washington"
}]

And this is a valid Multi-line documents input:

[
  { 
    "first" : "joe", 
    "last" : "smith" 
  },{ 
    "first" : "mary", 
    "last" : "lamb" 
  },{ 
    "first" : "george", 
    "last" : "washington"
  }
]

However, this is not valid Multi-line documents input, because the closing } of the document does not occur at the beginning of a line:

{
  "first" : "joe", 
  "last" : "smith" }
{ 
  "first" : "mary", 
  "last" : "lamb" }
{ 
  "first" : "george", 
  "last" : "washington" }

This is also not valid Multi-line documents input, because the closing } of the document is at a higher indentation level than the opening {:

[
{
  "first" : "joe", 
  "last" : "smith" 
  }
{ 
  "first" : "mary", 
  "last" : "lamb" 
  }
{ 
  "first" : "george", 
  "last" : "washington" 
  }
]

Map-enclosed array

Each input file or field contains multiple JSON documents nested inside a single element:

{  
   "value": [
      {
         JSON document
      },
      {
         JSON document
      },
      {
         JSON document
      }
   ]
}  

Multi-document mashup

Like Multi-line document mode, but documents are simply strung together as a stream, with no newlines. This option accommodates undifferentiated JSON "dumps".

Configure the JSON Input tool

The procedure for configuring a JSON Input tool depends on the data source:

Note that this tool will not display an output connector until you have configured the input.

...to read files

To configure the JSON Input tool to read files:

1. Select the JSON Input tool, and then go to the Configuration tab on the Properties pane.

2. Select Input from and choose File, and then specify the input file or files. You can use wildcards to configure a single JSON Input tool to read a sequence of files with the same layout and format.

3. Optionally, specify Multi-doc mode:

   • Single document: Each input file or field contains exactly one document. Default.

   • Document per line: Each input file or field contains multiple JSON documents, with one document per line of text.

   • Multi-line documents: Each input file or field contains multiple JSON documents, and each document spans more than one line.

   • Map-enclosed array: Each input file or field contains multiple JSON documents nested inside a single element.

   • Multi-document mashup: Each input file or field contains a stream of JSON documents, and the documents are not delimited by newlines.

4. Define the input format:

Note that output connectors are unavailable until input specifications have been defined.

5. Select each cell in the Record column, and examine the fields grid to verify that the schema is correct and the data is accurately described. If a field type is incorrect, select the cell in the Type column, and specify the field's data type and size. You can also use the Change selected types and Change all types commands on the field menu to edit field types.

Note that linkage fields (ID fields that associate parent and child records) must be of type Integer (4).

6. Optionally, select the Options tab and configure advanced options:

   • To include the name of the input file as a new field, select Produce file name field and specify a Field name and Field size. Select Output full path to include the complete file specification. This can be useful when reading a wildcarded set of files. Select Output URI path to express the complete file specification as a Uniform Resource Identifier.

   • If you don't want to process the entire file, select Limit records and type the desired number of records to process.

   • To produce a single output in which array values are combined into a single field and separated by commas, rather than sent to a separate output as a child relation of the root document, select Flatten value arrays, and then analyze the data.

   • Configure error handling.

7. Optionally, go to the Execution tab and Enable trigger input, configure reporting options, or set Web service options.

...to read fields

To configure the JSON Input tool to read fields:

1. Select the JSON Input tool, and then go to the Configuration tab on the Properties pane.

2. Select Input from and choose Field.

3. Select Commit to display the input connector, and then connect the desired input tool.

4. Specify the Input field from which the data will be read. If you are connecting to an upstream Web Service Input tool, this is requestBody.

Note that Field data must be binary. If necessary, use an upstream Calculate tool configured with the BinaryRecastFromText function to convert the input data to type binary.

If you have...	Do this:
A JSON schema for this file already defined in the repository.	Select Use repository schema, and select the schema from the drop-down list.
No schema defined for this file.	Select the Analyze tab and enter a formatted sample of the expected JSON input. Select Analyze and analyze the data. Note that the Analyze tab is only available when Input from is set to Field.

Output connectors are unavailable until input specifications have been defined.

5. On the Configuration tab, select each cell in the Record column, and examine the Fields grid to verify that the schema is correct and the data is accurately described. (See About JSON paths for details.) If a field type is incorrect, select the cell in the Type column, and specify the field's data type and size. You can also use the Change selected types and Change all types commands on thefield menu to edit field types.

Note that linkage fields (ID fields that associate parent and child records) must be of type Integer (4).

6. Optionally, specify a Filter for one or more Outputs. This is a Boolean expression to detect variant records within an Output. If specified, the filter must evaluate to True for the record to be output.

7. Optionally, select the Options tab and configure advanced options:

   • To include the name of the input file as a new field, select Produce file name field and specify a Field name and Field size. Select Output full path to include the complete file specification. This can be useful when reading a wildcarded set of files. Select Output URI path to express the complete file specification as a Uniform Resource Identifier.

   • If you don't want to process the entire file, select Limit records and type the desired number of records to process.

   • To produce a single output in which array values are combined into a single field and separated by commas, rather than sent to a separate output as a child relation of the root document, select Flatten value arrays, and then analyze the data.

   • Configure error handling.

8. Optionally, go to the Execution tab and Enable trigger input, configure reporting options, or set Web service options.

JSON paths

Data Management supports the following JSON Path elements:

For example, this formatted JSON:

{
	"firstName": "John",
	"lastName": "Smith",
	"age": 25,
	"address": {
		"streetAddress": "21 2nd Street",
		"city": "New York",
		"state": "NY",
		"postalCode": "10021"
	},
	"phoneNumber": [
		{
			"type": "home",
			"number": "212 555-1234"
		},
		{
			"type": "fax",
			"number": "646 555-4567"
		}
	]
} 

would be decomposed into the following relational model with a Parent record and a Child record:

Parent record

Path: /

Values found at various field paths:

Child record

Path: /phoneNumber

Values found at various field paths:

First child record:

Second child record: