Skip to main content

Table Builder — REST and JSON/XML Data Sources

The Table Builder is available in the Jet Analytics REST and Jet Analytics JSON & XML data source providers. It allows nested XML or JSON data to be transformed into a flattened table using drag-and-drop functionality to define the underlying XSLT code — without writing XSLT manually.

To use the Table Builder, a REST or JSON & XML data source must already be added to a running Ingest instance.

Add table flattening to an endpoint

  1. Open the data source, expand the endpoint, click Table flattening under Additional configurations, and click Add.
  2. Enter a name for the table flattening (this becomes the name of the resulting flattened table) and click Open to open the Table Builder interface. The Table Builder interface has five sections:
    • Input: Paste in the XML or JSON to flatten and select the applicable data structure.
    • Nodes in table: Displays the schema of the input data.
    • Root: Sets the XPath location in the schema to iterate over data records.
    • Columns: Drop nodes from the schema here to define the columns of the resulting table.
    • Output/XSLT: Toggle between a preview of the transformed output and the generated XSLT code.
  3. Provide input data by selecting one of the following methods:
    • Local File Path: A local file path. If a directory is specified, the first file in it is parsed. The file must be XML or JSON.
    • XML/JSON: Paste valid XML or JSON directly into the text area.
    • URL (JSON & XML data source only): A URL that returns valid XML or JSON content. Authentication settings are not applied for this input method.
    • Current Endpoint (REST data source only): Retrieves XML/JSON from the related endpoint, including all authentication and dynamic value settings.
  4. Click Get schema to load the schema for the input data.

Set the root

The root XPath defines where in the source document data records are iterated. If data records contain nested lists — for example, each Employee record contains a list of Phone numbers — set the root to the XPath of the innermost iteration level. The Table Builder will repeat the outer data for each iteration of the root.

Example input:

[ { "Employee Name": "John Doe", "Phone numbers": [999009999, 666006666] }, { ... } ]

Setting the root to Phone numbers produces the following records (with both Employee Name and Phone numbers selected as columns):

[ { "John Doe", 999009999 }, { "John Doe", 666006666 } ]

Note:

Set the root before dragging nodes into the Columns section. Column names are generated relative to the root in the schema hierarchy, so setting the root first produces more accurate automatic names.

Select columns

  1. Drag nodes from the Nodes in table list into the Columns section. Dragging a parent node adds all its child leaf nodes.
  2. Right-click any node in the Columns section to apply transformations:
    • Delete: Remove the node from the selection.
    • Edit name: Rename the column in the resulting table.
    • Set replace value: Apply a simple string replacement transformation to the column values.
    • Set default value: Use a specified default when the column value is null or an empty string.
  3. Click Add static node to add a column with a fixed value. Dynamic values can be used within static node transformations to inject runtime values.

Data structures

Generic structure

The default and most common structure. Data points appear as named nodes or objects:

{ "My data": [ { "My datapoint": "value1", "My datapoint2": "value1" }, { "My datapoint": "value2", "My datapoint2": "value2" } ] }

Column structure

Data points reside within arrays — each data point key contains the entire column of values in one array:

{ "My data": { "My datapoint": ["value1", "value2", ...], "My datapoint2": ["value1", "value2", ...] } }

When using the Column structure, the root behaves differently. Instead of pointing to the row-level iteration, set the root to the parent node of the array columns. In the example above, setting the root to My data produces a table with two columns: My datapoint and My datapoint2.

Note:

For the Column structure to work correctly, all arrays must be the same length.

Save and reopen

Click Save and close to save the configuration. The generated XSLT is saved along with the selected root XPath and column nodes. Saving overwrites any existing XSLT for that table flattening entry.

When reopening the Table Builder, the previously selected root and columns are retained. However, new input data must be pasted and Get schema clicked again to reload the node list. If a loaded column node does not have a matching XPath in the schema, it is indicated with a red icon — this makes it easy to detect schema drift when the source API structure has changed.

Example: multiple sub-objects in a single flattened table

Only one root node can be selected per flattened table. However, fields from sibling or parent nodes can be included alongside the root-level fields by using ../ to navigate to parent nodes in the XSLT. The Table Builder generates this navigation automatically when nodes from outside the root are dragged into the Columns section.

For example, given the following structure with both businessRelation and country as sub-objects alongside an id field:

{ "id": 123, "businessRelation": { "id": 456, ... }, "country": { "id": 789, ... } }

Setting the root to businessRelation and adding the country/id field via ../country/id produces a flattened table containing all three ID values. The XSLT for this scenario looks like this:

<?xml version="1.0" encoding="UTF-8" ?> <xsl:stylesheet version="1.0" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"> <xsl:output method="xml" indent="yes"/> <xsl:template match="/"> <tx_root> <xsl:apply-templates select="/TX_Autogenerated_Root/TX_Autogenerated_Element/businessRelation" mode="rows"/> </tx_root> </xsl:template> <xsl:template match="/TX_Autogenerated_Root/TX_Autogenerated_Element/businessRelation" mode="rows"> <BusinessRelation> <xsl:element name="Id"> <xsl:value-of select="../id"/> </xsl:element> <xsl:element name="BusinessRelationId"> <xsl:value-of select="id"/> </xsl:element> <xsl:element name="CountryId"> <xsl:value-of select="../country/id"/> </xsl:element> </BusinessRelation> </xsl:template> </xsl:stylesheet>

Note:

This approach works when each parent-level record contains exactly one instance of the sibling sub-object. If a sub-object can appear multiple times per record, a separate flattened table (with a different root) is required.

Was this article helpful?

We're sorry to hear that.