Developer Reference

Widget Reference

The following sections describe the platform widgets and which configuration options are available for them. You can create custom widgets and use them just like platform widgets. Please refer to the section Development / Production. Note that all widgets have the title option:

• title: when the widget is a direct child of the page container, the widget is placed in a card with this title

Widgets can be grouped into the following three categories.

Container Widgets

Container widgets can contain other widgets. All container widgets have features that control under which conditions content is shown or hidden. Please note that these features are enforced on the client and thus can be manipulated by malicious users. Specifically, do not rely on these features to implement security and data privacy. You can safely restrict access on the server side by applying access control to functions, databases, tables, and queries.

card

Layout card with a title and nested widgets:

• text: card title
• roles: show container only if user is in one of these roles

container

Container with a plain layout

• roles: show container only if user is in one of these roles
• if: show the widget if the expression is true
• redrawInterval: redraw interval (seconds). Periodically refreshes the container and all contained content.

expansion

Collapsible container with nested widgets

• text: card title
• roles: show container only if user is in one of these roles

Form Widgets

Like containers, form widgets allow adding input elements. Every input widget must use a unique name which is in turn used as the key in the resulting JSON structure. Inputs can be of the following type:

• boolean: displays an on/off toggle and returns a boolean value
• string; a regular text box that returns a string
• number: a number input returning a number
• auto complete: an input field with auto-complete options (which are generated by a JSONata expression)
• select: like auto complete, but uses a select widget instead of a text field
• multi select: like select but allows multiple selections, returns an array
• key value: allows entering any key value pairs, returns an object
• password: like string but hides the input
• textarea: like string but uses a multi-line input
• date: like string but uses a date picker
• time: like string but uses a time picker
• datetime: like string but uses a datetime picker
• file: like string but uses the content of an uploaded file are the value
• binary file: like file, but uses base64 encoding
• file with metadata: like file but includes file metadata
• binary file with metadata: like binary file but includes file metadata

button

Runs / evaluates an expression when clicked.

• text: text shown for the run button (default is "Run")
• print: evaluates this expression when clicked
• deleteConfirmation: optional confirmation message before performing the action

The form content (if a form is present), is added to the context using the key "form".

create

Creates new database records:

• text: text shown for the create button (default is "Create")
• database: optional database to create the record in (defaults to the database of the table you are currently displaying)
• table: optional table to create the record in (defaults to the table you are currently displaying)

edit

allows editing a database record. Note that the form layout can be customized in the layout editor. Please also refer to the FAQ for information on how to further customize the input form.

• deleteConfirmation: optional confirmation message before deleting the record

variable

Displays a form that allows setting session variables. If a variable "x" is defined and set, it can be referenced in other widgets using "variable.x". A variable can be set via a URL query parameter. Appending ?a=1&b=test to the URL will set variable.a to "1" and variable.b to "test". Note that only string variables can be set this way, so you might have to use $number(variable.a) when using the variable.

• text: text shown for the apply button (default is "Apply")

Regular Widgets

actionTable

The Action table widget works like the table widget. In addition, it allows selecting several rows in the table. If rows are selected, action buttons become visible. The actions are configured via a set of key value pairs. The key specifies the action button's label, the value contains the expression that is run if the button is pressed.

• expression: allows configuring the widget data via JSONata which must evaluate to an array of objects. Note that the table is able to display links, images, and lists thereof. Please refer to the display widget for information on how the JSON data must be structured. If omitted, the widget uses $query(database, query, arguments)
• properties: a set of key value pairs. For every key, an action button with the key used as its label is displayed. When the button is pressed, the JSONata expression specified in the value is run

aichat

Chatbot widget for interacting with large language models.

• url: LLM Service URL or function name
• name: Chatbot name
• tagline: Chatbot tagline
• logo: Logo URL
• system_prompt: AI system prompt

chart

Chart for visualizing query results.

• database: database to run the query on
• query: query to run; the query is expected to project the following column structure:
  • label followed by a value column: in this case, a chart with a single series is shown. The first column is used as the series axis label and the second column is used as the value range
  • two label columns followed by a value column: in this case, a chart with a multiple series is shown. The first column identifies which series the row belongs to. From there, the process described above is repeated
• arguments: optional expression resulting in query arguments
• chart: chart type
• style: key value pairs that construct chart option object - for instance, scales.y.min = 0 makes sure the y-axis starts at 0. By default, the chart limits the number of data points to 1000. This can be overridden by setting "limit" to the desired value
• graph: specifies whether the query is a graph query
• expression: allows configuring the widget via JSONata. If omitted, the widget uses $query(database, query, arguments)

Examples: * chart-stacked-bar * chart-timeline

diagram

Allows displaying and editing data as a graph of nodes and edges.

Nodes are represented by the following JSON structure:

{
  id: node id
  database: database name
  table: table name
  position?: {
    x: coordindate
    y: coordindate
  },
  data?: {
    label: display label
  }
}

Edges are represented by the following JSON structure:

{
  source: id of the edge source node
  target: id of the edge target node
}

• style: CSS widget styles (e.g. width and height of the widget)
• nodes: JSONata that generates a list of nodes
• edges: JSONata that generates a list of edges
• moveNode: optional JSONata to persist new coordinates (e.g. $update("northwind", "EMPLOYEES", node.id, {"Y": node.position.y}))
• addNode: optional JSONata to persist new node that was added via SHIFT click (e.g. ($x := {"id": node.name, "x": node.position.x, "y": node.position.y}; $create("db", "table", $x); $x) )
• removeNode: optional JSONata to remove node in the DB (passed via node.id)
• addEdge: optional JSONata to create a relationship (passed via edge.source, edge.target)
• removeEdge: optional JSONata to remove a relationship (passed via edge.source, edge.target)

display

Displays the result of an expression:

• display: expression to display
• icons: if display evaluates to an object, icons maps the object keys to material icons

Depending on the result of the evaluation, one of the following cases applies:

• a single result value is displayed as is
• an object is displayed as a key-value list
• if the object has exactly the keys "database", "table", and "pk1", the result is displayed as a link to the record identified by these values
• if the object has exactly the keys "database", "table", "pk1", and "page", the result is displayed as a link to the record identified by these values and uses the specified page to visualize the record
• an array of objects is displayed as a table
• if the object has exactly the key "img" (with optional width and height), the result is displayed as an HTML image with the value of the img field being used as the image src attribute
• if the object has exactly the key "href" or the keys "href" and "label", the object is displayed as a hyperlink

Example:

  "display": {
    "item one": "this item's value",
    "item two": "another value",
    "item three": "last value",
  },
  "icons": {
    "item one": "traffic",
    "item two": "turn_left"
  }

Item one will be displayed with the "traffic" icon, item two with the "turn_left" icon. When no item is specified for a key, the default item is used. In the above example, "item three" will display the default icon.

When icons is "*": "icon", all icons will be mapped to that same specified icon.

editRelated

Allows editing related records of a database record:

• prop: foreign key column on the related table
• columns: columns to display in the editRelated table display

graph

Displays data as a directed graph

• nodes: expression to generate nodes, this can either be the result of an OpenCypher query or a list of nodes
• _3d: if true, displays the graph in 3D, 2D otherwise

To add a single starting node, you can simply use the following expression for "nodes":

{
  "database": "northwind",
  "table": "EMPLOYEES",
  "pk": [1]
}

To add all employees, you can select all employee IDs and convert them into resource objects as shown above:

$all("northwind", "EMPLOYEES").EMPLOYEE_ID.{
  "database": "northwind",
  "table": "EMPLOYEES",
  "pk": [$]
}

Alternatively, you can use an OpenCypher query:

$adHocQueryGraph("*", "MATCH (e:EMPLOYEES) return e").e

The previous examples only added nodes to the canvas. You can use OpenCypher to pre-select a path:

$adHocQueryGraph("*", "MATCH path=(e:EMPLOYEES) -[REPORTS_TO]-> (x) return path").path

html

Displays custom HTML

• html: HTML to display
• context: an expression that allows setting additional context variables that can be referenced via ${context.VARIABLE}

icon

Displays a hyperlink icon with tooltip

• href: optional link target
• icon: icon to display (see https://material.io/resources/icons/?style=baseline)
• tooltip: icon tooltip
• roles: show container only if user is in one of these roles

links

Displays links to related records

map

Displays a map for a given location.

• display: expression that results in a location - this value is resolved using the q query parameter of the Open Streetmap API service. The expected result structure is explained below
• css: CSS code to apply to the map
• card: determines if the map is shown on a card (paper background)

The map can be fed with the following data:

{
  center: {lat, lon}   // map center
  zoom: number         // zoom level where globe=1 and street=15
  points: [            // array of marker points
    {
      address: string  // address where marker is placed or marker coordinates
      location: {lat, lon}
      color: string    // marker color
      radius: number   // marker radius
      tooltip: any     // additional info to be displayed in tooltip or popup
      popup: any       // any JSON is displayed like in the display widget
    }
  ]
}

Note that most information is optional. The platform chooses good defaults. For instance, you can display a single address with a link to a record page as follows:

{
  "points": [
    {
      "address": "London",
      "popup": {
        "database": "northwind",
        "table": "CUSTOMERS",
        "pk": [ "AROUT" ]
      }
    }
  ]
}

If you would just like to show a marker at London, you can simply pass the string:

"London"

markdown

Displays markdown

• markdown: markdown to display
• context: an expression that allows setting additional context variables that can be referenced via ${context.VARIABLE}
• card: determines if the markdown is shown on a card (paper background)

Note that the HTML generated by the markdown engine is sanitized in order to avoid XSS vulnerabilities. Specifically, if you are using HTML tags, style attributes are filtered. A common task is to add margins to images. You can achieve this by adding a class attribute to the element and setting the value to a predefined material class like mat-elevation-z8. Alternatively, the markdown widget defines the styles margin1 to margin5 which set the element margin to 1em to 5em.

notebook

This widget is the JSONata equivalent of a Jupyter notebook. It allows composing and running several expressions. The result of every expression is stored in the browser session. You can also assign variables and use them in other expressions.

The widget offers a save function at the bottom. This saves the entire page and the expressions contained within. Please note that the notebook widget should only be used as the sole widget on the page, since saving the notebook will delete other widgets you might place on the page.

table

Displays query results as a table

• database: database to run the query on
• query: query to run
• arguments: optional expression resulting in query arguments
• graph: specifies whether the query is a graph query
• expression: allows configuring the widget data via JSONata which must evaluate to an array of objects. Note that the table is able to display links, images, and lists thereof. Please refer to the display widget for information on how the JSON data must be structured. If omitted, the widget uses $query(database, query, arguments)
• columns: if no query or expression is specified, this array allows specifying the columns to be projected. Note that this option is only available in Dashjoin Studio

Database and table can be omitted on table pages. In this case, the widget displays the equivalent of a select all from the respective table.

text

Displays a simple text

• href: optional link target
• text: text to display
• icon: optional icon to display in front of the text

tree

Displays a tree based on a recursive query

• database: database to run the query on
• query: query that projects a single column with the keys of the current node's children
• arguments: expression that passes the current node's primary key as a query argument
• expression: allows configuring the widget via JSONata. If omitted, the widget uses $query(database, query, arguments)

Functions

Apart from changing data in databases, Dashjoin can call functions on the backend. Functions come in two flavors: First, there are functions that simply extend the functionality you can use in expressions. An example would be a simple toUpperCase function that transforms a string to upper case. These functions are introduced further later in the section on expressions. Second, there are configurable functions. These work very much like their counterpart, however, they require additional configuration parameters. An example would be a function to send an email. The actual function call requires you to specify subject, sender, receiver, and the body. But you would not want to have to repeat the email server address and credentials every time. So you can register an instance of email service with specific parameters and call it email-service-1. This section describes the latter configurable functions.

Click here for a demo video.

Function Reference

The system supports the following functions. Each section lists the function configuration parameters that are constant any time this function is called as well as the parameters that are specific for each invocation.

RestJson

Calls an external REST service.

Configuration

• url: the URL of the REST service to call (the URL may contain template variables ${var} which are replaced with the respective argument field)
• username: optional HTTP basic authentication user name
• password: optional HTTP basic authentication password

Invocation parameter

• object: If object is specified, POSTs the object serialized as JSON. If object is null, GETs the result

Return value

• JSON result returned by the service

Email

Sends an email.

Configuration

• username: username to log into the email service
• password: password to log into the email service
• properties: SMTP server configuration

Invocation parameter

• from: email sender in RFC822 syntax
• to: email recipient in RFC822 syntax
• subject: email subject line
• text: email text

Return value

• none

Invoke

Allows saving an expression on the server. When run, we evaluate / apply the expression with the data context passed as an argument.

Configuration

• expression: The expression to save and run when invoked

Invocation parameter

• object: the expression evaluation context (see next chapter)

Return value

• expression result

Credentials

Allows saving credentials that can be references by other functions.

Configuration:

• username: credential username
• password: credential password

Mapping Functions

Mapping functions are specialized functions that have no invocation parameters and outputs. They are used to write data into a database and can be run in a scheduled fashion. All mapping functions perform the following three steps.

Click here for a demo video.

Gathering Data

It is up to the mapping function how this task is achieved. The only requirement is that the function gathers a set of tables.

The Mapping Step

This step is common to all mapping functions and is supported by a specialized mapping editor. The mapping step transforms the gathered set of tables into another set of tables. The mapping step supports the following operations:

• remove table: a table from the initial step can be removed / ignored
• remove column: drops a column from a table
• rename table: a table from the initial step can be renamed
• rename column: renames a column in a table
• add table: a table can be added by providing the name of an initial table
• add column: a column can be added to a table
• modify column: sets the column to a new expression (the default simply copies the original value 1:1 using $.columnname; please see the next section for more details on expressions)
• extract table: if an input table contains a column with array values, extracts the union of these arrays into a new table

The Save Step

The save step writes the output of the mapping step into the database. The following modes are supported:

Ignore

Simply add the data (update in case the record already is in the DB, insert if not). We follow the "normal" update semantics meaning that key=null actually deletes the value in the DB, whereas missing keys remain untouched.

Database

id	_dj_source	name
1		Joe
2		Mike

Extracted Data from ETL "ignore"

id	name
1	John
3	Nate

Result

id	_dj_source	name
1		John
2		Mike
3	ignore	Nate

Row 1 is updated. Row 2 remains unchanged. Row 3 is added and thus gets marked as having source e.

Extracted Data from ETL "ignore"

id	name
1	John
4	Jane

Result

id	_dj_source	name
1		John
2		Mike
3	ignore	Nate
4	ignore	Jane

Row 4 gets added. Row 3 remains even though it is no longer in the extraction result.

Refresh

All records from the target tables that have the _dj_source column matching the ID of this function are updated. If a key is no longer present in the new data, the record is deleted.

Database

id	_dj_source	name
1		Joe
2		Mike

Extracted Data from ETL "refresh"

id	name
1	John
3	Nate

Result

id	_dj_source	name
1		John
2		Mike
3	refresh	Nate

The first run of "refresh" has the same effect as "ignore".

Extracted Data from ETL "refresh"

id	name
1	John
4	Jane

Result

id	_dj_source	name
1		John
2		Mike
4	refresh	Jane

The third row, which was added by "refresh" previously, is deleted and row 4 is added.

Delete All

All records from the target tables are deleted, if createSchema is true, the tables are also dropped in case columns are no longer needed or previously had another datatype.

Database

id	_dj_source	name	age
1		Joe	33
2		Mike	44

Extracted Data from ETL "delete-all"

id	name
1	John
3	Nate

Result

id	_dj_source	name
1	delete-all	John
3	delete-all	Nate

The table content is deleted. If create schema is specified, the age column is also deleted. Rows 1 and 3 get added with the respective source.

Extracted Data from ETL "delete-all"

id	name
1	John
4	Jane

Result

id	_dj_source	name
1	delete-all	John
4	delete-all	Jane

The table content is deleted and rows 1 and 4 are added.

Mapping Function Reference

ETL

The ETL function uses an expression as input into the mapping process. The expression result can be a map of table names to an array of rows (JSON objects). If the expression result has a simpler structure (for instance only a single table), the ETL function wraps this in a default table called "table".

If you want to load a large amount of data, you can use the "foreach" expression to specify how to split the loading process into smaller parts. Assume you have a directory with thousands of files to load. The foreach expression can list the files using $ls("url"). The expression then specifies how each file is handled. Its $ context is set to each individual URL and the expression and subsequent ETL are called for each URL individually.

Note that you can also stream large JSON, XML, or CSV files via the streamJson, streamXml, and streamCsv functions. In this case, these functions split a large file into smaller chunks which are then passed to the mapping expression.

Consider the following example expressions:

$openExcel("https://download.microsoft.com/download/1/4/E/14EDED28-6C58-4055-A65C-23B4DA81C4DE/Financial%20Sample.xlsx")

Note that you have the full power of JSONata available for this operation. Consider the following example. We'd like to incrementally load files that are placed in an upload folder. Only files that have been added since the last run should be considered:

$ls("file:upload/delta")[modified > $jobStatus().start].url.$openJson($);

The ls function returns an array containing objects with the modified file timestamp as well as the url of the file. The jobStatus function returns information about the last job run. Therefore, we can filter the files to only include the ones that have a modified timestamp after the job ran last.

The setting "ETL worker threads" can be used to achieve parallel writes to the database. This setting is only applicable if a foreach expression is specified. In this case, the setting "ignore ETL errors and continue process" specifies that any error that occurs when streaming a large file (e.g. a formatting error towards the end of the file) or when workers map and write the contents to the database (e.g. due a malformatted date string) are ignored and do not stop the other workers.

Receive

The receive function allows handling cases, where the platform is being sent data that is to be processed and saved into a database. This use case is common in IoT scenarios where a stream of sensor data is passed via the REST API. The Receive function can be configured like the ETL function and allows mapping the data into the desired structure. The create schema parameter works like in the ETL case and optionally adapts the underlying schema to accommodate new fields and tables. Receive defines a parameter called sample where a stream data sample can be added. This sample data is used to edit the mapping. Note that Receive always appends the new data like the Ignore mode in the ETL case. The difference is that there is no expression that fetches data. Instead, the data is passed via the API call.

Dashjoin Expression Reference

In addition to the default JSONata built-in functions (see Function Library), the following Dashjoin functions are added (some internal functions are omitted - you can refer to the platform's info page for a full list):

These functions can be classified as frontend and backend functions. Frontend functions run in the browser and can be used to trigger a screen popup or to set a browser session variable. Backend functions typically access backend data. You can mix both kinds in a single JSONata expression tree.

Frontend Expressions

Function	Syntax	Returns
confirm	$confirm(message)	Opens a confirm dialog. Returns true if confirmation was given, false otherwise
setVariable	$setVariable(key, value)	Sets variable key to value. The key value pair then becomes accessible via the context by other expressions
prompt	$prompt(message)	Prompts the user for an input. Returns the input or undefined if the prompt is cancelled
alert	$alert(message)	Shows a modal alert message
notify	$notify(message)	Shows the message at the bottom of the screen
refresh	$refresh()	refreshes the screen just (just like hitting the refresh icon in the toolbar)
reload	$reload()	reloads the browser page
log	$log(value)	logs value to the developer console
navigate	$navigate(url)	points the browser to the URL
clearCache	$clearCache()	clears the HTTP cache - can be used in conjunction with expressions that trigger side effects on the backend

Backend Expressions

Function	Syntax	Returns
create	$create(database, table, pk1)	ID of the new record
all	$all(database, table)	array of all table records
all	$all(database, table, offset, limit, sort, descending, filter)	array of all table records whose columns match the filter key's values
read	$read(database, table, pk1)	The record
traverse	$traverse(database, table, pk1, fk)	Record(s) related to the current record via the property fk. If fk is a simple column name, fk is an outgoing foreign key and the single related record is returned. If fk is a full property ID like dj/database/table/column, then a list of records from that table that have a fk pointing to the current record are returned
update	$update(database, table, pk1, object)
delete	$delete(database, table, pk1)
call	$call(function, argument)	Dashjoin function result
query	$query(database, queryId, arguments)	Query result table
queryGraph	$queryGraph(database, queryId, arguments)	Graph query result, specifying the database as * runs an OpenCypher query over all DBs
adHocQuery	$adHocQuery(database, query, limit?)	Runs as ad hoc select / read query
search	$search(term, limit?, database?, table?)	Searches the databases(s)
incoming	$incoming(database, table, pk1)	[{id: ID of the record where the link originates, pk: ID of the pk column, fk: ID of the fk column}, ...]
echo	$echo(any)	Prints the parameter to the log
index	$index()	Generates a unique row index ID
djVersion	$djVersion()	Returns the platform version information
djRoles	$djRoles()	Returns the roles of the current user
djUser	$djUser()	Returns the current user's name
isRecursiveTrigger	$isRecursiveTrigger()	true if the current expression is called from a trigger expression (trigger calls trigger)
jobStatus	$jobStatus()	if evaluated within a function, start and stop timestamps (millis since 1970) and job status
moveField	$moveField(object, 'from', 'to')	Moves the object's from key into the to key, where to must be an object or array
ls	$ls(url, preview-limit)	Lists all URLs found at url (the URL can also contain filter wildcards like *.txt). preview limit determines how many results are returned in preview mode (defaults to 10)
streamJson	$streamJson(url, jsonPointer)	Parses JSON at the url and splits it at the json pointer location
streamXml	$streamXml(url, jsonPointer)	Parses XML at the url, converts it to JSON, and splits it at the json pointer location
streamCsv	$streamCsv(url, options)	Parses CSV at the url and splits it at the record boundaries. By default, CSV is parsed as RFC4180. Options can be provided, where the key is a "with" method like withDelimiter and the value is the argument. Please see the documentation for more details.
streamDb	$streamDb(database, table)	Streams records from the database table specified
curl	$curl(method, url, data?, headers?)	Full fledged HTTP client. Use {"Authorization": credential} to reference a credential set defined in functions
openJson	$openJson(url)	Parses JSON at the url
openCsv	$openCsv(url, options)	Parses CSV at the url and converts it to JSON. By default, CSV is parsed as RFC4180. Options can be provided, where the key is a "with" method like withDelimiter and the value is the argument. Please see the documentation for more details.
openXml	$openXml(url, arrays)	Parses XML at the url and converts it to JSON. In this process, openXml guesses which XML tags need to be converted to arrays and which become simple fields. This process might produce inconsistent results when the XML tree contains lists with single entries. To avoid this, you can optionally pass a list of tag names that must be arrays.
openYaml	$openYaml(url)	Parses YAML at the url and converts it to JSON
openExcel	$openExcel(url)	Parses Excel at the url and converts it to JSON
openText	$openText(url, encoding)	Parses the url and converts it to a string
parseJson	$parseJson(json)	Parses JSON (see openJson)
parseCsv	$parseCsv(csv, options)	Parses CSV and converts it to JSON (see openCsv)
parseHtml	$parseHtml(html, query, [xpath	css])
parseXml	$parseXml(xml, arrays)	Parses XML at converts it to JSON (see openXml)
parseYaml	$parseYaml(yaml)	Parses YAML and converts it to JSON (see openYaml)
parseExcel	$parseExcel(base64)	Parses Excel and converts it to JSON (see openExcel). The parameter must be a base64 encoded data URL (RFC 2397)
uuid	$uuid()	Generates a random UUID
exec	$exec(executable, arguments, [json, xml, csv, yaml])	runs the script or executable located in the app's bin folder and optionally parses the output to JSON, XML, or CSV
erDiagram	$erDiagram(database?)	Generate an ER diagram for https://dbdiagram.io/d
stats	$stats(database, table, limit?)	Generate statistics for a database table (type, min, max, count, distinct values, etc.)
gitStatus	$gitStatus()	Run git status
gitPull	$gitPull()	Run git pull
gitRestore	$gitRestore(path)	Revert a change
gitCommit	$gitCommit(message, [paths])	Run git commit and push
gitClone	$gitClone(url)	Run git clone url - only available in the DJ playground
saveTable	$saveTable(Ignore	Refresh
reconcileEntity	$reconcileEntity(entity, entity-language?, limit?)	Uses the wikidata query service to reconcile a string to a wikidata id. The entity is a simple string. The entity language is the language the entity is expressed in (defaults to en). The limit (default 1) determines the number of results returned (see chapter AI & ML)
classifyEntities	$classifyEntities([entities], entity-language?, limit?, subclass-depth?)	Reconciles entities and finds common classifications that all entities are an instance of. The parameters are similar to the reconcileEntity function. The subclass depth (default 1) describes the number of superclasses that are included in the results (see chapter AI & ML)
synonym	$synonym({algorithm: threshold}, [terms], [variants], ignoreCase?, ignoreEquality?)	Allows generating synonym table to match keys despite small typos etc. (see chapter AI & ML)
urlExists	$urlExists(url)	Returns true if the url is reachable, false otherwise
wait	$wait(object, millisecs)	Wait millisecs provided before returning object