JSON Features

Data encoded in the JSON format is commonly encountered in relational databases. Ultorg has several features that help display and “unpack” JSON data.

The JSON Data Type

Ultorg supports JSON as a basic data type in table columns and formulas. Functionality such as pretty-printing is available on fields that are explicitly associated with the JSON type.

JSON-typed table columns are recognized automatically on PostgreSQL, MySQL, DuckDB, Microsoft SQL Server, and Google BigQuery. These databases all have JSON-oriented data types at the SQL level.

In other cases, you may have JSON data that is stored in a text-typed table column. To convert text values to JSON, use the jsonParse function in a formula.

Pretty-Printing

JSON data is automatically pretty-printed in the user interface, in a style appropriate for the current text wrapping mode.

A table layout with JSON-typed data in one column, displayed in pretty-printed form.

Pretty-printing of JSON is enabled by default, but can be disabled in the Format popup, via the Pretty-Print JSON Data option. The latter setting also controls whether pretty-printing is enabled in the formula bar, the cell editor, and the expanded cell editor.

Use in Formulas

In formulas, the jsonValue function can be used to extract primitive values from JSON data. See the function documentation for more information, and the Expand JSON Fields action, which creates jsonValue formulas automatically.

The jsonValue function being used to extract a string field from an adjacent JSON value in the same subquery, with function documentation open.

If your JSON data is in a text field, use a formula like =jsonValue(jsonParse([textColumn]), ...)

Expand JSON Fields

The Expand JSON Fields action will examine retrieved JSON objects and create one jsonValue formula for each observed JSON object field. This lets you interact with JSON object fields in the same way as with regular database columns; you can hide and show them from the field selector, filter/sort on them, and so on.

Screenshots showing the Expand JSON Fields action.

The action is available in the context menu of any JSON-typed field.

Fields are detected from currently loaded data only. If additional data becomes available, you can invoke Expand JSON Fields again to make new fields appear in the field selector.

Other notes about the behavior of the Expand JSON Fields action:

  • Multiple levels of JSON objects are supported.
  • Numeric fields are detected and automatically casted as such using the num function.
  • JSON arrays are not traversed, though this may change in a future Ultorg version.
  • Field names are adjusted for readability if the Auto-Format Field Names setting is enabled.

Export to JSON

A JSON version of the latest loaded perspective data is available by clicking Help→Diagnostics→SQL/JSON Output.