Please enable JavaScript to view this site.

Altova XMLSpy 2020 Enterprise Edition

XQuery Filters and Formulas in JSON Grid View enable you to use XQuery 3.1 expressions to, respectively: (i) filter the components that are displayed in the view, and (ii) calculate results that can be stored in the document. Each filter and formula is executed independently and is not affected by other filters or formulas.

 

For information about constructing XQuery expressions for JSON documents, see the section XQuery Expressions for JSON.

 

Note:When entering expressions for filters and formulas in Grid View, you might want to display an expression over several lines for better readability. To add a new line in the expression, press Ctrl+Enter.

 

Filters

Filters enable you to filter the display of object and arrays. For example, in the screenshot below, a filter has been applied to an array so that only those tracks written by Brian May are displayed. In JSON terms, only those object children of Tracks are displayed that have a Writer property containing the string value Brian May.) The filter's XQuery expression looks up all child objects of Tracks and selects those for which a lookup of the Writer property matches the string 'Brian May'.

 

JSONGridViewFilters01

 

To set up a filter, double-click in the Filter cell of the object or array you want to filter, enter the XQuery expression, and click Enter. The Filter cell is indicated by the icon ic_jsongridfilteroff (see screenshot above). You can activate/deactivate the filter by clicking the Filter icon.

 

Note the following points about filters:

 

Filters can be applied only to objects and arrays.

The context node of the filter's XQuery expression is the current node. In the screenshot above, for example, the context node of the filter's XQuery expression is the Tracks node.

Filters are not stored in the JSON document as JSON content. They are stored in the metadata of the document, and are applied to the respective object/array when the document is opened in JSON Grid View. Each filter affects only the display of the document in Grid View.

Filters can be nested. A nested filter will be applied to the filtered content of the parent filter.

To add a new line in an expression, press Ctrl+Enter.

 

Formulas

A formula enables you to generate output (nodesets as well as calculations) and to display the output in JSON Grid View. In the screenshot below, for example, the total price of the items 1 to 4 is calculated and the output (28) is displayed in a separate line.

 

If the document is a JSON5 or JSONC document, then:

 

The formula will be written into the document as a JSON comment

Optionally, the output of the formula can be stored as (non-commented) code in the JSON document. To do this, click the formula's disk icon (see screenshot below). If the formula is not stored as JSON content, then it will be stored only as document metadata and will be displayed in Grid View. Note that the disk icon is available only in JSON5 and JSONC documents; it is not displayed in other types of JSON document.

JSONGridViewFormulas01

 

 

In the screenshot above, the formula sums up the members of a sequence. These members are each the product of the price and quantity values of each object contained in the items array. The iteration to select each object and assign it in turn to the $item variable is specified by: for $item in ?items?*. It is important at this point to note the context node, which is the parent of the formula—and, consequently, the parent of the items node. Each product is obtained by looking up the price and quantity child nodes of the object currently in the $item variable, and multiplying these two values with one another. The products obtained in this way are the members of the sequence, which are then summed to generate the total price.

 

To create a formula, do the following:

 

1.Select the node to which you want to add the formula, either as a sibling or child. Right-click, and add the sibling or child (whichever you want). In deciding where you want the formula to appear (as sibling or child), bear in mind that the context node of the formula's XQuery expression will be the parent node of the formula. For example, in the XQuery expression shown in the screenshot above, the context node is the parent node of the formula (totalPrice) and of its sibling, the items array. To create the formula in the screenshot above, a sibling node was appended to the items array (see screenshot below).

2.Change the type of the node to Formula (see screenshot below).

JSONGridViewFormulas02AddNode

3.Double-click in the cell containing the f(x) icon and enter the name of the formula (see screenshot below). If the document is a JSON5 or JSONC document, then a disk icon is displayed. You can click this icon to save the output of the formula to the document.

JSONGridViewFormulas03NameNode

4.By default, the XQuery expression is the string 'XQuery', so the output will be the string XQuery (displayed in the cell below the expression). Enter your XQuery expression by double-clicking in the expression's cell, typing in the expression, and then clicking Enter. This causes the formula to be evaluated and its result to be displayed. Other formulas will not be modified.

5.If there are multiple formulas in the document, click the menu command JSON | Re-evaluate All to update the results of all formulas. This command is especially useful if formulas in the document look up dynamically changing data (for example, exchange rates).

 

Note the following points about filters:

 

The context node of the formula's XQuery expression is the parent node of the formula node.

Formulas are stored in the metadata of the document. If the document is JSON5 or JSONC, then the formula is additionally saved as a comment in the JSON content.

The output generated by a formula is displayed in the cell below the formula's XQuery expression. In the case of JSON5 and JSONC, the output can be stored in the document by clicking the disk icon next to the XQuery expression. Whether the disk is clicked or not, the formula's output will be calculated and stored in the document's metadata.

ic_jsongridFormulaOutputNotSaved

Formula output not saved to JSON content; click to save. Only in JSON5 and JSONC.

ic_jsongridFormulaOutputSaved

Formula output saved to JSON content; click to not save. Only in JSON5 and JSONC

When the output is a calculation and is stored in content, it is stored as a property, which has the name you assigned the formula. For example, in the totalPrice formula described above, the output will be stored like this: "totalPrice": 28.

Note this difference: In JSON5 and JSONC documents, formulas are saved as JSON comments, their outputs are saved as JSON properties.

 

Note:The JSON Grid View options enable you to specify whether or not formulas are stored in JSON5 and JSONC documents. The option to store formulas is selected by default.

 

Formulas in tables

If all the cells of a table column (in Table Display) contain the same formula, then the formula is displayed only once—in the header of the column (see screenshot below). The results of the formula calculation, however, are displayed in the respective cells.

 

The formula in the column header is a Grid View representation. In the JSON document content (in Text View), the formula is repeated for each table-row item.

JSONGridViewFormulasInTables01

If even a single formula is different (as in the highlighted cell of the screenshot below), then each formula is displayed in its respective cell.

JSONGridViewFormulasInTables02

Re-evaluate all formulas

To update the results of all formulas in the document, click the menu command JSON | Re-evaluate All.

 

Procedures for relevant actions

 

Add a new empty column to the table as follows: Switch to List Display from Table Display, right-click any key:value pair in the list display, and append or insert a new key:value pair via the item's context menu. When you switch back to Table Display, a new column is created for the new key:value pair that was appended/inserted. You can now edit this column in Grid View.

If all formulas of a table column are the same so that the formula appears in the header and you now want to create a different formula for an individual cell, switch to List Display and edit the formula of that cell. When you switch back to Table Display, formulas will be displayed in individual cells (for all cells).

To add a new line in an expression, press Ctrl+Enter.

 

© 2019 Altova GmbH