Spectre Build JSON Input

The Spectre Build JSON Input object brings JSON formatted data into the build script. It accepts input from a .json file.

The JSON input object has three panels where you set attributes.

Spectre JSON Input GUI Screen

Columns

The JSON input object Columns grid displays the columns from the input file. A count is included in the heading. The context menu on the Columns grid supports the following row actions: Refresh Columns, Copy, Paste, Insert Row, and Delete Row.

JSON Input Column Grid

For each column, specify a column name, data type, alias, and whether to keep or remove the column.

Attribute	Description
Column	If column headings exist, displays the name of each input column. As of Spectre 7.2(15), these column names correspond to the key tag in the underlying build script. If column headings do not exist, you must specify all column names in the Alias field. An empty string cannot be used as a column name. See the Column Properties tab. Column names are case-insensitive.
Type	Specifies the type of data in the column. Spectre attempts to auto-detect the type. However, it is a best practice to verify these and correct as necessary to ensure faster processing and less memory usage. TIP: Spectre does not auto detect datetime fields. If you want a column to be interpreted as a datetime, be sure to specify that type. Valid values are boolean, date, datetime, double, fixed100, integer, period, and string. See Spectre Data Types. NOTE: The type for date assumes that the input has a formatted date, not the binary date value.
Keep	Keeps only the specified columns from the input. This is useful when there are many columns and only a select few are needed. TIP: A best practice is to specify which columns to keep rather than which to remove.
Remove	Removes the specified column.
Alias	Renames the column.

JSON Input Properties

You set values for the JSON input object in the Properties pane.

JSON Input Properties Tab

Attribute Description

Json File Path

Defines a json file as a data source. Use project based pathing. The asterisk wildcard character is supported.

Include Other Columns

Ensures that no columns are dropped. Use when you explicitly define a few columns with special properties and want to ensure that all data is included in the build.

Click on the ellipses to open the Include other columns editor.

Include other columns editor dialog

Additional properties

Misc Property	Value
Include other columns	Select to toggle between True and False.
Suggested Dimension	Select True if you want the column treated as a dimension in ProDiver; otherwise False.
Summary Type	Select the summary type: sum, info, any, first, last, const.
Type	Select the column type: string, integer, double, fixed 100, date, datetime, period, boolean.

If no columns are specified in a Build script, Include Other Columns is assumed in order to avoid building a cBase with no columns.

Drop ID When the Parent attribute is an object and the Include Other Columns attribute is true, this prevents the creation of an ID column for the field names.

ID Column

Defines a column where each row is the name of a field in a JSON object.

For example, given the following input:

Copy

{
    "CA": { 
        "name": "California", 
        "population": 39237836 
    },
    "TX": { 
        "name": "Texas", 
        "population": 29527941 
    },
    "FL": { 
        "name": "Florida", 
        "population": 21781128 
    }
}

Set ID Column as "Abbrev".

The Abbrev column has the values CA, TX, FL.

NOTE: When using the Include Other Columns attribute, the default behavior for object parent values is to create a column named ID for the field names. You can use the Drop ID attribute to avoid creating that column.

Limit Rows Directs the build to use a specific number of rows. This is only applicable when the JSON input utilizes an array.

Parent If one is provided, it is a JSON path identifying where the parent value can be found in the document. The parent is the object that contains the values which will be mapped to rows. The parent value must be an object or an array. If parent is absent, the root of the JSON document is used as the parent.

Prefix

Prefixes all the outputted column names with the specified string.

NOTE: The prefix is applied after all operations.

Trim Removes any leading or trailing spaces from the data. This is the default behavior for Spectre. Set to false to disable the trimming of white space.

Column Properties

You set values for the input columns in the Column Properties pane. These are properties related to the selected column. Select a column on the left, then select the Column Properties tab on the right to set values.

JSON Input Column Properties Tab

Attribute	Description
Calendar	Adds a calendar for the selected column. If the column type is period, a calendar tag is required. Valid calendars are: standard—Gregorian calendar; period type options are: year year-half year-quarter year-tertile year-month offset <month>—Fiscal year similar to the standard calendar except starting on a different month; period type options are the same as those for standard calendars. iso 8601—ISO week-numbering system; period type options are: year year-week custom—Custom calendar created from a text file with a contiguous range of dates; required columns are date and year, optional columns are half, quarter, tertile, month, and week. Deprecated but available for compatibility: gregorian month—Gregorian year-month gregorian quarter—Gregorian year-quarter iso8601 week—ISO-8601 year-week gregorian+<N> month—Offset Gregorian year-month for fiscal calendars. Specifying gregorian+6 month means add six months to the real year-mo to get the fiscal year-mo, which is like saying The fiscal year starts in July. <custom calendar file>—For more tailored fiscal calendars See Calendar Declarations.
Column Name	Displays the column name to indicate which column's properties you are configuring.
Custom Properties	Defines custom properties to associate extra data with a column. The custom property can be extracted after the build and used in expressions. Adding custom properties Select the column. Under Properties, select the Custom Properties row, and click the browse button. The Custom Properties Editor opens. Select the plus button to add a row. Enter a Name and a Property Value. For example: Click OK.
Disable Similar Values Warning	Turns off data warnings of similar values for this column. Use only after data validation steps have determined that the warnings are in fact not worrisome.
Format	Use to format numbers, dates, periods, currency, and Boolean values. Sets both the display format and the input properties, unless a separate input format is specified. Setting the format Select the column. Click the Format property, and select a format from the list. The list shows different suggestions based on the type of the column. Also, see the input format and Spectre Format Conventions.
Input Format	Clarifies the incoming data. For example, the default format for date data is YYYY/MM/DD. Use the input format to define the date format that is in the input file to ensure correct interpretation. Setting the Input Format Select the column. Click the Input Format property, and enter a format or select one from the list. The list shows different suggestions based on the type of the column.
Keep Column Status	Indicates the column status as determined by options in the Keep and Remove columns. Possible values: Explicit Keep, Explicit Remove, and Auto Remove, where nothing is checked.
Label	Defines the display label used for a column.
Nulls	Maps known values used to represent no data to the null value or a special value in the cBase. Enter a string for the known value followed by a string for the substitute value. Or enter a string for the value to be set to null.
Period Type	Refines the period type when the Spectre data type for the column is period. See the calendar attribute for valid values.
Required Dimension	Sets a column to behave like an Info Field. In ProDiver, the info field is available as a column that you can add to other columns displayed in a dive. For example, to see Address requires that Customer is available. Setting required dimensions Select the column you want to define as an Info Field. Select Required Dimension in the Properties pane. Locate and select the core dimension from the list. The first column is only available when the second column is displayed in the client. See Defining Info Relationships in Build Code Samples. Also, see ProDiver Help.
Sort By	Specifies an alternate sort. When specifying columns, use sort by to order them based on another column's value. Setting sort by Select the column to which you want to apply an alternate sort order. Select Sort By in the Properties pane, and select from the available columns list. NOTE: An alternate sort can only be applied to a string column, and the sort-by column must be a string or an integer column. You cannot add, remove, or change alternate sorts after a cBase is built. Dive and cPlan files also have a sort tag, but for string data, rely on alternate sorts (if any) that are established when the cBase is built. Dives can also specify sorting on numeric columns. By default Spectre uses numeric collation when sorting strings. When a subset of a string looks like a number, the numeric value is considered during the sort. For example, Spectre sorts "a2" before "a10". Also, see the sort natural option. Null strings sort at the end. If two distinct strings in the column to be sorted are set to sort as the same value in the sort-by column, the normal ordering of those distinct strings resolves the tie. If a row has the value null or the value unknown in the column with an alternate sort, the sort-by column must also have the same value. If the sort-by column has type string AND has the value null or the value unknown, the column to be sorted must have the same value. Spectre issues a warning if you attempt to alternate sort null as non-null, or unknown as not-unknown.
Sort Natural	Disables natural string sorting in a text-input object. The default is True—Spectre interprets value of digits as decimal numbers and sorts according to the numeric values in the string. TIP: This feature only makes sense together with sort-by if the sort-by column is also a string column.
Suggested Dimension	Allows you to promote or demote a column. NOTE: Set to True to promote a numeric column type to become a diveable dimension and have it appear in the ProDiver console. Set to False to demote a column from classification as a dimension so it does not appear in the ProDiver console. When set to False, the new dimension does not appear in the console (which requires dimcount processing), but it can be promoted to a dimension for diving—equivalent to the concept of a dynamic dimension for a classic model. Using Required Dimension on a column effectively sets Suggested Dimension to False.
Summary Type	Sets the column's summary type explicitly. Any summary function that takes a single argument (except for count and percentile) is allowed. See Summary Functions. The most common values are: sum, info, any, first, last and const. The default for numeric data is sum; use const for a column with only one value. The rest of the functions (for example, min, average, median) are better done as a separate calculation, that is, not the default.

Mentioned in:

Knowledge Forum: Importing JSON Data