How Do I Create a Select Statement With the JsonPath Wizard?
In this article, we'll go step by step through an example of using the JsonPath Wizard to create a select statement for a ForEach Tag. If you aren't familiar with the ForEach Tag, please review our ForEach Tag Reference before continuing.
In this example we'll create a table of products whose unit price is greater than $50, sorted by the number of units in stock. You can begin with a blank Word document, or follow along with the attached example template that uses our public Northwind JSON data source.
Six Steps to Using the JsonPath Wizard
Although this example is specific to a ForEach Tag, this procedure can be followed to create a JsonPath select statement for any Tag returning data from a JSON data source.
- Create the Tag where the select statement will be applied
- Bring up the JsonPath Wizard
- Select the node you wish to display data about in your output
- Create a condition the data must satisfy to be displayed
- Sort the data satisfying the condition (optional)
- Generate output
Create the Tag Where the Select Statement Will be Applied
We've opened a Word document and connected to our example Northwind JSON data source (located at http://json.windward.net/Northwind.json). We create a table displaying product ID, product name, unit price and units in stock by dragging the "Products" node from the Data Bin and dropping it onto the report template.
This creates a table containing a ForEach Tag and some Out Tags. We'll use the JsonPath Wizard to modify the select statement in the ForEach Tag. You can format the Out Tags' output as desired (e.g. set the UnitPrice Out Tag output format to Currency).
Bring Up the JsonPath Wizard
To bring up the JsonPath Wizard on the ForEach Tag, select the ForEach Tag, then click on the Wizard button on the AutoTag tab of the Word ribbon.
Select the Node You Wish to Display Data About in Your Output
Since we created the ForEach Tag by dragging and dropping from the Data Bin, the node we wish to query is already selected. If we had inserted the ForEach Tag manually, we would click on "click here to add a node", then use the popup window to select the node we wish to query.
Create a Condition the Data Must Satisfy to Be Displayed
We want to display those products whose unit price is greater than $50. To do so we must create a condition. A condition is a node, a comparison and a value, such as "unit price > 50."
First, click on "click here to add a group":
Then click on "click here to enter a condition":
Next, click on "click here to select a node", then select the UnitPrice node in the popup window:
Click on "equal to", and change it to "greater than":
Then click on "click here to set the value", and enter "50":
Sort the Data Satisfying the Condition (Optional)
Now we have a select statement which will return the ID for each product with a unit price greater than $50. To sort those product IDs by units in stock, click on "Click here to add an order by":
Click on "ascending", change it to "descending", then click on "OK":
Now double-click on the ForEach Tag to bring up the Tag Editor, and in the Query Pane you'll see the select statement we just built using the Wizard.
Optionally, you can add some Word formatting such as a table style and text alignment. Also, change the formatting of the [UnitPrice] Out Tag so it displays values as currency. Then generate output!