Overview
JSON (JavaScript Object Notation) has become a highly used interchange format because of its lightweight, simplicity, and ability for humans to read and write easily. Many REST APIs produce JSON format and the format is used predominantly in Atlassian REST APIs. There is also an abundance of support libraries associated with the format which makes it ideal for scripting output.
The JSON Table macro can import, format and display JSON (JavaScript Object Notation) data from anywhere, by:
Reading the JSON data from any of these sources:
Within your Confluence page.
From a file residing on the Confluence server.
From a space template.
From a page attachment.
From an external URL.
Allowing customizable access to fields.
Supporting inclusion of Wiki Markup macros within the JSON data.
Combining with the Chart macro to produce powerful data visualization.
Leveraging the same table styling capabilities as the Table Plus macro.
This macro supports certain Common table capabilities.
Available from release 6.3 and above.
Applicable upto app version 8.1:
To enable using HTML content with JSON data, you must enable the Stop encoding of html characters parameter. In such a case, it is recommended to contact your administrator to use Macro Security for Confluence with this app to provide an additional layer of security to your data and privacy. Refer to this documentation for more information on Macro Security for Confluence app.
From app version 8.2 onwards, if the Stop encoding of html characters parameter is enabled, use of Macro Security for Confluence is no longer needed as the macro itself provides additional security to your data.
Basic use
This macro can be deployed using one of the following methods:
Selected from the macro browser | Advanced Tables - JSON Table |
|---|---|
Markup shortcut | {json-table} |
Screenshot
Parameters
JSON settings
Macro editor label | Default | Description | Macro parameter |
|---|---|---|---|
Data source | |||
Method of locating JSON data | macro body | Select the source of the JSON data from a drop-down with a list of options where JSON data is located. The included data follows the body data based on the location selected.
While previewing the page before publishing for the first time, the result for @parent may not be displayed as expected. Once you save the document, you can view the result as expected.
Special note: How to deal with templates on Confluence 4.3 and later.
| script |
Profiles | Select a profile to be used to connect to a specific remote location. Contains a list of all profiles that are configured for this instance. Administrators configure a profile to connect to a remote location using a base URL and users then provide the relative path to the actual location of the JSON file in URL of JSON data. Appears if Method of locating CSV data is set to Profile. Available from 8.1.0 version. | profile | |
URL of JSON data | Enter the URL to a JSON file. If a profile is specified the Profiles field, enter the relative path of the actual location of the CSV file(s) here. Support of profiles is available from 8.1.0 version. Use of this parameter may be restricted for security reasons. See your administrator for details. | url | |
Username (if required) | Enter the user name to be used for URL access to JSON files. | user | |
Password (if required) | Enter the user password to be used for URL access to JSON files. The Password is encrypted and remains secure (available from version 8.6.0). If the first attempt fails while decrypting the password, the JSON Table macro initiates three retry attempts to decrypt the password. (Available since app version 8.7.1.) | password | |
Connection timeout | Enter time in milliseconds to wait until the URL connection times out before getting data. Use this to increase time needed for slow connections. Note that if a zero is given, the connection may wait indefinitely. | timeout | |
File encoding | Specify the encoding for an external file, if different from the system default handling. Example: UTF-8. Refer to this article for more information. Available from 4.1.0 version. | encoding | |
Data format | |||
Paths to fields | <required> | Specify a single path to generate a single table. Simple form is a dot separated list of field names to reach the specific field within the JSON string. Example: total.monthlies. When a comma-separated list of paths to fields is provided, a table is produced for each valid field identified by the path. No table is produced for references to fields that cannot be found, empty arrays, or similar. Paths are expressed in JSONPath syntax. | paths |
Paths to fields to be included | all fields | Enter a comma-separated list of paths to fields to be included in the output. Paths are relative to the JSON field specified in the paths parameter. If not specified, all fields found are included. Example for the issues field from the Jira search REST API: | fieldPaths |
Regex patterns for ordering fields | Enter a comma-separated list of regular expression (regex) patterns. This parameter is used only when the Paths to fields to be included field defaults to all fields; otherwise, the order specified in Paths to fields to be included is used. Useful when multiple tables are generated and column ordering is not ideal. Field order is not specific in JSON strings so when field order is important, these regex patterns can be used to control the order. A field found by an earlier pattern is ordered before fields not found or fields found by a later pattern. | fieldOrderRegexPatterns | |
Paths used to determine sort order | Enter a comma-separated list of path references to fields (simple only) to be used to determine how rows are initially sorted. Paths are relative to the JSON field specified in the Paths to fields parameter. If specified, JSON array elements are sorted based on the comparison of field values represented by the paths. The first path is the primary sort value. Subsequent paths are used only if the first comparison is equal. The sort direction is ascending unless Sort descending is selected. This sorting is done before the HTML is generated for display. | sortPaths | |
Data settings | |||
Columns to display | Enter a comma-separated list of column names or numbers in any order. By default, all columns are displayed in its existing order. Columns are enumerated starting at 1. | columns | |
Output format | html | Specify how the output is formatted. The options are as follows:
| output |
Strip leading qualifiers from generated headings | Off | Enable this option to make table heading columns look better by stripping leading qualifiers (using a dot separator (.)) for names generated as a result of Paths to fields to be included. For example, field.type is converted to type. Available from 6.5 version. | stripQualifiers |
Show result as table | On | Disable this option to display the JSON data as plain text without any whitespace or column delimiters. By default, the JSON data is rendered in a tabular format. Available since 8.2.0 version. | table |
Capitalize first character of generated headings | On | Enable this option to make table heading columns look better by capitalizing leading character of names generated as a result of Paths to fields to be included. Available from 6.5 version. | capitalize |
Show non-formatted version of generated wiki | Off | Enable this option to show a non-formatted version of the wiki table following the formatted table. This is used to help resolve formatting issues. It can also be used to convert JSON to Confluence markup by cut and paste. | showWiki |
Escape special characters in wiki | Off | Enable this option to allow few special characters to be escaped so that the formatting is unaffected. When wiki output is requested (Output format is set to wiki), some special characters (like '|', '[', ']', '{', '}') in data can cause undesirable formatting of the table. By default, data that has wiki markup is rendered correctly. | escape |
Render wiki markup macros in body | Off | Enable this option to render wiki markup macros found in the body prior to processing as CSV. This is useful to run macros from apps such as Scripting for Confluence, Run CLI Actions in Confluence, SQL for Confluence, or similar, that can produce JSON output. | macros |
Stop encoding of html characters | Off | Enable this option to display the HTML content such as links correctly for JSON tables with Output format as html. By default, HTML content like <a href=http://google.com>google> displays as text. Your administrator must grant specific users or groups to use this capability using Macro Security for Confluence. | disableAntiXss |
Augment parameters
See Augments for details for modifying column headings and column data.
Common parameters
The parameters listed on this page are part of our common table capabilities that are available in many macros that produce or modify tables.
Click a column heading to toggle the sorting of that column.
Tabs | Macro Editor Label | Default | Description | Macro Parameter |
|---|---|---|---|---|
Table settings | ||||
Formatting | Table id | auto generated | Enter an ID for the table. Can be referenced for use in macros like the chart macro or Javascript. | id |
Table class | @default | Enter a class to set the styles for the table. Sets the class of the table. Normally, it is confluenceTable. Use a blank value (one or more spaces) to not have any table class. | class | |
Table style |
| Enter CSS styles to be used for the table. For example: | style | |
Table width |
| Enter width of the table in pixels or %. Recommended to use Table style instead. | width | |
Border width |
| Enter the border width for the table in pixels. Set class to blank also. Recommended to use Table style instead. | border | |
Advanced settings | Enable download and export as attachment | Off | Enable this option to allow users to download or export the current view of the table as a CSV file. Downloads to the local file system. Exports as an attachment to the current page and is only available to users that have permission to add attachments to the page. When enabled, download and export icons () are available to the right of the table. Only those users that have the permission to add attachments to the page are allowed to export tables. If enabled, select a character from the drop-down to be used as a delimiter for the data to be downloaded or exported. The options provided are:
Valid only if Enable download and export as attachment is enabled. Available since 8.2.0 version. For Macro Security users:From app version 8.3.4 onwards, if this parameter is added to Macro Security Configuration in the macroname.allowExport format, the download/export icons are displayed only to the permitted users, groups, spaces, and/or pages based on the specified restrictions. For more information about Macro Security settings, refer to this article. | allowExport |
Display data filter | Off | Enable this option to display a drop-down list with unique column values under each column heading. By default, the None option is selected. Select filter values in multiple columns to see a more specific row. Select None to display all rows for that column. As filters are applied on other columns, the display of information differs. Select None for all columns to show all rows for all columns. Available since 8.3.0.
There is a known issue with the Display data filter parameter. When you select the drop-down for the first time, the column gets sorted though the list is not visible, and from the next click onwards, the list drops down displaying the unique values available in that column. This does not affect the working of the filters in any way. | displayDataFilter | |
Column settings | ||||
Formatting | Column styles |
| Click Start Formatting to format your table columns. Each of the column styles is made up of one or more CSS properties (semi-colon separated). The styles are applied to the respective columns as provided in the editor window. A style can be reused for subsequent columns by using a numeric reference of the column. (For example, you want to reuse 3rd column style in 6th - use 3 as style value in column 6) Column styles are applied to the table column and participate with other CSS properties to determine the look of an element. In particular, some properties may be overridden by table, row, or element styles or classes. Example: When the Insert auto number column parameter is On, say you want to repeat a column style, use numeric reference of this column +1 in the intended column. For example, to reuse 3rd column style in 6th when Insert auto number column is On - use 4 as style value in column 6. | columnStyles |
Column type |
| columnTypes | ||
Total type |
| columnCalculations | ||
Column attributes |
| Click and select Show column attributes to view the respective columns attributes field in the editor. Specifies a comma separated list of values used to modify cell attributes for the respective column. Each value is a double semi-colon list of attributeName=value pairs to be applied to the column cells. | columnAttributes | |
Numbering & Totaling | Insert auto number column | Off | Enable this option to show an additional column that show row number for each data row. | autoNumber |
Auto number column styles |
| Enter the CSS styles to be applied on the auto number column. Valid only if Insert auto number column is enabled. | autoNumberColStyles | |
Enable sorting on auto number column | Off | Enable this option to enable the auto number column to be sortable. This retains the original data row count even after row sorting. | autoNumberSort | |
Apply column attributes to header rows | On | Disable this option to have the column attributes apply only to data rows. By default, any column attributes provided are applied to the all column rows including heading rows. | enableHeadingAttributes | |
Apply Column styles to data cells | Off | Enable this option to make the styles in the Column styles field (Formatting tab) persist for the specified columns. Available since 8.1.0. |
| |
Auto total numeric columns | Off | Enable this option to append a row to the end of the table that contains the totals of all numeric columns. | autoTotal | |
Sorting
| Enable column sorting | On | Disable this option to disable sorting. | enableSorting |
Automatically sort this column |
| Enter a valid column name or number to automatically sort the table before it is displayed. No automatic sorting is done if this value is not provided or is invalid. A column number is indexed beginning at "1" and excludes any auto-numbered columns. | sortColumn | |
Automatically sort in descending order | Off | Enable this option to sort in the descending order, to be done automatically, before display. This works only if Auto sort column is specified. | sortDescending | |
Display sort icon | Off | Enable this option to include a sort icon in the first heading row for sortable columns. An icon is displayed for the last column sorted indicating the direction in which the column was sorted. | sortIcon | |
Sort tip text |
| Enter the text to be displayed when the mouse is over a sortable column. Example: Click to sort or an equivalent translation. | sortTip | |
Row formatting | ||||
Data rows | Row styles |
| Click Start Styling to format your table rows. Each of the row styles is made up of one or more properties. Each style is applied to the respective row. Row styles are applied to the table row and participate with other CSS properties to determine the look of an element. In particular, some properties may be overridden by table or element styles or classes. Example:
| row index |
Header & Footer rows | Number of header rows | 1 | Enter the number of rows to be considered as heading rows. Heading rows are not considered in the sorting of columns. | heading |
Header row styles |
| Enter a comma-separated list of CSS styles (similar to Row styles) that is applied on the header row. | rowStyles | |
Hide header rows | Off | Enable this option to hide the table header rows as specified in Number of header rows. By default, all header rows are shown.
| hideHeader | |
Number of footer rows | 0 | Enter the number of rows to be considered as footing rows. Footing rows are not considered in the sorting of columns. | footing | |
Auto total row styles |
| Enter the CSS styles to be applied on the auto total row. Valid only if Column settings > Numbering and totaling > Auto total numeric columns is enabled. | autoTotalRowStyle | |
Advanced settings | Retain row style order after re-sorting | On | Disable this option to retain the original style given to a row no matter where the row is displayed after sorting. By default, the row styles correspond to the order the rows are displayed on the screen. | retainRowStyleOrder |
Apply Row styles to data rows | Off | Enable this option to make the styles in the Row styles field (Data rows tab) persist for the specified columns. Available since 8.1.0. |
| |
Enable row highlighting on mouse over | On | Disable this option to stop row highlighting as the mouse moves over a table row. By default, the row is highlighted as the mouse moves. | enableHighlighting | |
Highlight color | lightgoldenrodyellow | Enter a color to highlight when the mouse is over a row element. See Web Colors for instructions on how to specify this. | highlightColor | |
Examples
Compatibility
Chart macro - the JSON Table macro can be used to create data for a chart
Beanshell macro - can be used to generate JSON Table macro and data as output from Java code (use output=wiki)
Groovy macro - can be used to generate JSON Table macro and data as output from Groovy code (use output=wiki)
Jython macro - can be used to generate JSON Table macro and data as output from Jython code (use output=wiki)
Other macros
The list of all other macros available within this app is as follows: