Working with JSON File Format in Spectre

JSON JavaScript Object Notation is a structured data format that can be used as a data input. Because the JSON format does not necessarily conform to the structure of a cBase, certain values must be set in the json-input object to properly import values from a JSON file. Consider the following JSON file as an example:

{
    "type": "collection",
    "entries": [
    {
    "resource": {
        "id": "1",
        "name": {
            "first": "Jane",
            "last": "Doe"
        },
        "birthDate": "1973-05-31"
    },
    ...
    }]
}

To read this data, each record in the JSON file needs to be defined in Spectre. In this example, that is the entries array. Spectre calls that the parent. Next, Spectre creates a single row for each element in the parent. Those elements can be complex JSON objects or arrays. By configuring the columns in the json-input object, users can control which values are imported. The following build script returns the ID, First Name, Last Name, and Birth Date from the JSON file:

build {

json-input "people.json" parent="entries" {

column "ID" key="resource.id"

column "First Name" key="resource.name.first"

column "Last Name" key="resource.name.last"

column "Birth Date" key="resource.birthDate"

}

output "people.cbase"
}

JSON value types are:

• null

• Boolean

• number

• string

• object

• array

Object and array values cannot be mapped to Spectre values. Instead, they can be deconstructed into multiple columns using the key attribute. If no type is given for a column, Spectre determines the column type while reading the file. For string JSON values, Spectre determines if the string is an integer, fixed100, double, date, datetime, or period before setting the overall column type.

Some options are interpreted as JSON Paths (parent in json-input, and key in column). A JSON Path is a sequence of field names or array indexes, identifying where to find a value in a complex object or array.

For instance, in the following input:

[{
    "name": "Boston",
    "info": {
        "state": "Massachusetts",
        "lat long": [
            42.361145,
            -71.057083
        ]
    }
    },
    ...
]

Here are some sample JSON paths and the values they would extract:

• name—Boston

• info.state—Massachusetts

• info.'lat long'.0—42.361145

• info.'lat long'.1— -71.057083

The tokens in a JSON path are separated by periods. Each token in a JSON path is either a plain string or a quoted string.

For object values, the token is a field name. For array values, the token is the array name followed by an integer representing the index of the array item. Array indexes are zero-based. In the above example, the first element of the lat long array is referenced as info.'lat long'.0, where the zero represents the first element of the array. For each subsequent element of the array, the index iterates by one.

Use quoted strings for field names that contain characters other than letters, numbers or underscores. Within a quoted string, use backslashes to escape the quotation mark character or other backslashes.

NOTE: Because the JSON path is already in double quotation marks in the Spectre script, you need an extra layer of backslashes for double quotation marks in quoted strings. For this reason, DI recommends that you use single quotation marks.

As of Spectre 7.2(14), the asterisk (*) character is used to retrieve all values or objects from an input JSON array.
For instance, consider the following input:

[{
    "cust": "A",
    "addr": [ "A-1", "A-2" ]
}, 
{
    "cust": "B",
    "addr": [ "B-1" ]
}]

Using a wildcard character in the key attribute for addr retrieves all of the values of the addr array.

json-input "test.json" {
	column "Customer" key="cust"
	column "Address" key="addr.*"
}

This produces the following output:

Note that a row is created for each element in the array, not for each element in the JSON object.

Multiple columns can be pulled from the same sub-array.

For instance, consider the following input:

[ {
    "cust": "A",
    "addr": 
    [ 
        {"city": "A-1", "state": "AA-1" },
        { "city": "A-2", "state": "AA-2" } 
    ]
}, {
    "cust": "B",
    "addr": [ {"city": "B-1", "state": "BB-1" } ]
}]

Using the wildcard character followed by the name of an object in a sub-array specifies that the input column is populated only with values from that object.

json-input "test.json" {
	column "Customer" key="cust"
	column "City" key="addr.*.city"
	column "State" key="addr.*.state"
}

This produces consider the following output:

NOTE:The wildcard character cannot be used in the parent tag.