Ontology SQL functions

Ontology SQL functions allow you to define reusable, parameterized queries over ontology objects using SQL. Once published, an ontology SQL function can be used anywhere in Foundry that accepts ontology functions, including Workshop, Actions, Automate, and the Ontology SDK (OSDK).

Ontology SQL functions are read-only: you cannot modify the ontology from within an ontology SQL function.

Define your function with SQL

You can write ontology SQL functions in SQL Studio or SQL Console. You must be in Object mode to create a function.

Functions support parameters, which are declared using variables in the SQL dialect. In the example below, animalParam is a parameter that is passed in when the function is called. The default value 'dog' is used when testing the query in the editor.

Parameters can only hold literal values (for example, a string, number, or boolean) of the data types supported by the ontology SQL dialect. A parameter cannot contain a SQL fragment or expression, and can only be used in positions where the SQL dialect expects a literal value, such as the right-hand side of a WHERE clause comparison.

Copied!
1
2
3
4
5
DECLARE @animalParam STRING = 'dog';

SELECT *
FROM `ri.ontology.main.object-type...`
WHERE animaltype = @animalParam;

Publish your function

When you are satisfied with your function, save your worksheet and select the Publish button. Your worksheet must be saved to a project before you can publish the function.

In the Publish function dialog, provide a Name and API name for your function, as with any other function in Foundry.

Use your function

Ontology SQL functions return a list of structs whose fields match the selected columns, with one struct per row in the result. When only a single column is selected, the function returns a list of that column's type instead. For example, SELECT animal, animalType, age FROM ... returns List<Struct<animal: STRING, animalType: STRING, age: INTEGER>>, while SELECT animal FROM ... returns List<STRING>.

After your function is published, it can be used throughout Foundry like any other ontology function, including in Workshop, Actions, Automate, and OSDK. Ontology SQL functions can also be called from TypeScript and Python functions. As with other functions in Foundry, Ontology SQL functions support querying on branches and scenarios.

For details on Ontology SQL more broadly, refer to the Ontology SQL overview.