Data that is displayed within Query Results can be formatted as needed to improve the experience of readability and analysis. The formatting changes are for display purposes only and do not impact the underlying data. 

• Column Formatter Menu

• Global vs. Current Scope

• Formatters Tab

• JSONQuery Formatter

• XPATHQuery Formatter

• Default Formatter Preferences

To access Formatter options for a column, right mouse click on the desired column. The Formatter menu is the first option that appears in the menu and provides options to Apply To (for Global vs. Current Scope) and for a selected Pattern. 

The formatting options (Pattern) will be applicable to the type of data contained in the selected column. For example, you cannot display a Date value as a percentage. 

Select the desired Pattern to apply the new format to the column:  

Open

If a Formatter is selected for a column(s) and then a SQL statement is rerun in the Query Editor, the new formatting will be removed automatically (it will default back to original settings).

This is because the change was not saved to a scope (Global or Current). If a Formatter is applied to a column and needs to be saved, this can be done through the Global or Current Scope options, or through Default Formatter Preferences. 

To customize the ResultSet value displayed for JSON and XML content, use the advanced formatter to enter a query String that can return a displayable value created by a query string based on either the Groovy "dot notation" or XML xpath statement.

NOTE: GPath and XPath fragments are saved for the life of the current table where it is applied. If you rerun the query, the fragments will be removed and will need to be re-added.

A JSON query allows you to display one or more concatenated fields with null query results being ignored. The variable name must prefix each individual GPath fragment with dots separating the variable name and each path in the statement.

The following JSON is an example of the JSON in rows 2-n for the following JSON example.

In the following example x is the variable name, squadName is the query, so x.squadName will search each JSON document and display the result, in this case "Super hero squad".

Open

To query a JSON array or array element use the [] array notation. For instance x.members[0] will display the JSON for the first (zero based) member found.

Open

To account for this and allow different documents to display different elements you can used the append method and question mark "?" operator as shown.

• ? -  allows a null value returned to stop the query and return null, each included query will execute. Note for the x.members?[0]?.name query there are two question marks, the first verifies that members resolves to a non-null value, the second verifies that the [0] is valid. If either return null, the name portion of the query will not be executed.

Open

Other useful patterns:

• Equality test returns a boolean true or false where == tests for equality and != tests for inequality

  • x.squadName == "Super hero squad"

  • x.squadName != "Super hero squad"

• Search over a list and find matches, in our example we can search over each level of the members array for 0-n matches.  All matches for 29 in this case will be return as a list.

  • x.members.findAll { member -> member.age == 29 }

• Get element (name) from each array (members) and return as a comma delimited list

  • x.members.name

NOTE: Refer to Groovy Semantics (2.1. GPath expressions) for more information on GPath syntax

An XPATH query allows you to display one or more concatenated fields with null query results being ignored. To quickly create a valid XPath statement, double click on XML - (Double Click for viewing options) to display the XML Search and Display dialog. You can now :

• enter XPATH statements and click Search to view the result

• Generate an XPATH statement which starts with // which allows it to search through the document and return all elements/values which match. For example, //SpecialProcessing would return the XML in the sample below. Note the optional *: before the element name which is required if namespaces are used and the element namespace is not the default namespace.

  • double click on Element Names button to select a search element

  • double click on the element in the XML content to generate an XPath statement

NOTE: Refer to Reference: XPath syntax for more information on how to use XPath.

The XML in the following samples is from the California Franchise Tax Board and is provided sample form.

Open

Now that you have a valid XPath statement right click on the column and select the XPATH formatter.

Open

To display multiple comma separated values as a set of values, add 1-n expressions. The values will be added with the "&" character between the values to separate them. 

If an XPath fragment is going to return more than one result, you can use array syntax to return only one of the elements. To do this, the XPath fragment must be wrapped in braces to have it extract first and then add the brackets after the ending paren. The index is 1 based meaning the first element will be [1] NOT [0]. For Example: (//CombatZone)[1].

There are two options for saving formatting: Global Scope and Current Scope.

Open

The Global Scope option, when applied, will be used by all connections (across any open Query Editors and all database connections). 

The Current Scope option applies the format to only the specific database connection of the Query Editor being used. 

The Formatters Tab (located beneath the Query Editor) is available as an administration tool to view and remove both Global and Current Scope formatters:

Open

The columns that have Formatters applied will appear under the Column Names, and can be selected to view applied formatting: 

Open

The Scope column will display a * to indicate it is a Global scope, or will provide the database connection information if it is a Current scope: 

Open

To remove a Formatter, select one from the list of active formatters and then select the button to delete it from the list.

Open

The Default Formatters (located under the Application Preferences) allow users to set global formatting for data results in the Query Editor:

Open

Note: For more information regarding application preferences, check out the Help Center article Andi Application Preferences!