About JSON Databases

By default, BarTender uses the “root” or a single array as the master record node. However, you can use the Master Record Selection page in the Database Setup wizard to specify the node in the JSON file that you want to be the master record node. When you do this, you define what constitutes a record and how many records are generated from the JSON. Only one JSON element can be designated as the master record node.

In the wizard, the schema of the JSON is shown in a tree view. Select the node in the tree that you want for the master record node, and a preview of the resulting database table is displayed on the Fields tab.

After you select the master record node, you can use the Field Selection page of the Database Setup wizard to select the nodes that you want to contribute field data (columns) to your database. To do this, click to select or clear the check boxes in the Available Nodes area to include or exclude nodes (columns). A preview of the resulting database table is displayed on the Fields tab.

You can also use this page to specify how you want repeating nodes, which contribute multiple values to a column, to be handled.

For example, suppose that you are using the following JSON file.

You select Name as the master record node, exclude the type node, include the species node, and then select Concatenate in the Select option for repeating node list. The resulting database is as follows.

JSON arrays can result in multiple values (repeating nodes) for a single database column. BarTender supports several methods for handling these values.

The options that are available in the Database Setup wizard vary depending on the value's data type and include the following:

• Use First: Specifies that the field value is retrieved from the first occurrence of the repeating value.

• Use Last: Specifies that the field value is retrieved from the last occurrence of the repeating value.

• Concatenate: Specifies that the field value is a concatenation of all occurrences of the selected node, which are separated by the specified separation character. In the Separation character(s) field, enter the separation character that you want to use, and then click Apply.

• Sum: Specifies that the field value is the sum of the repeating values.

• Average: Specifies that the field value is the average of the repeating values.

• Min: Specifies that the field value is the minimum value that occurs in the repeating values.

• Max: Specifies that the field value is the maximum value that occurs in the repeating values.

• Create Columns for Each (Indexed): Specifies that separate new database columns are created for each repeating value. You can specify the number of database columns that you want to create.

• Create Columns for Each (Distinguished): Specifies that separate new database columns are created for each value based on some "distinguishing" sibling node, which you specify. The sibling node must contain a single unique value (it cannot be a sub-object or array). Available when the node for which you want to create multiple columns is part of a JSON object and has a sibling node.

  Example

  Suppose that you are using the following JSON file.

  

  You want each variation of the repeating "number" node to be treated as a unique field (column) of data according to its "type" value. To do this, select the number node on the Field Selection page of the Database Setup wizard, select the Create Columns for Each (Distinguished) option, and then select type for the distinguishing node. When you do this, the following database results.