This article is meant to familiarize users with the JSON Select Statement Wizard. For information on connecting to JSON data sources, see here. The JSON Select Statement Wizard is a user interface that allows users to build JPath statements without needing to know how code JPath. JPath is a language that allows a the traversal of a JSON structure with XPath like navigation. If you would like to learn more about how to build JPath statements this website is a great reference.
If you don't know XPath or JPath, not to worry, this is the reason we designed the wizard in order to assist you in selecting and filtering your data visually.
- Main Node - this is the main node in the JSON tree upon which you will base your additional conditions and node information returned. It is good practice to select the highest node possible in order to return as much data as you need. Then you can use conditions and additional nodes to filter the data you want returned later.
- Group(s) - Groups allow a user to logically organize a set of conditions and additional groups in order to create a final statement that will return a result based on your requirements. Groups can contain multiple conditions and multiple nested groups. You can also specify of all conditions (a logical AND) or any conditions (a logical OR) are true.
- Condition(s) - Multiple conditions can be nested inside each other. You can also specify of all conditions (a logical AND) or any conditions (a logical OR) are true. Each condition needs to select a node to compare, an inequality to use as a comparison and a value to compare against either a number/text or a variable value.
- Additional Node(s) - can be nested inside each other walking down the JSON tree to return the correct value.
- Limit - the ability to limit the number of results your JPath query returns by a number or variable.
- Order By - allows the results of your JPath query to be ordered by a specified node in either ascending or descending order.
- Results Pane - this is where you JPath query results are returned in real time as you building your query.
- JPath Pane - this shows the actual JPath query being built in real time. This is for reference for those familiar with JPath.
The JSON query wizard is very similar to the XPath wizard except that it lacks the ability to link conditions with logical operators.
The different features of the wizard will be outlined by assembling the example below.
Select Main JSON Node Employees in the JSON Tree
Open the sample template, select the [Employee Loop] tag and click the wizard icon. The top left pane text select the node(s) is where you select your main node that will contain all the possible information you want to your request to return.
- Specify the filter condition for Employee ID
Then you can create a filter to return the results based a single condition, seen here on the second line: "EmployeeID is greater than the value 1."
- Limit the number of results returned (Optional)
By the Limit icon you can specify the number of results your query will return. In the example we have set the value to 3.
- Order the filtered results by employee last name (Optional)
The results can then be ordered with the order by line. In this example we selected the node Last Name and chose ascending
- View filtered results in right pane
The top right pane is the results view. This will show the results of running the query as it is being built up. Each node labeled Element X, will correspond to a single iteration of a ForEachTag.
- Check bottom pane for JPath built from your JSON query in the wizard
The bottom pane displays the query as it is specified by the query builder.
- Final Generated Report