How can I call SQL on Ontology from Functions?

Ask the Community
1

I’m writing a function that queries the Ontology. I would like to use a SQL syntax to query the Ontology.
It seems that functions don’t have enough permissions to perform this query (missingScope": "api:usage:sql-queries-execute). What is the workaround to perform such queries ?

Context: Some users prefer the syntax in SQL to perform particular operations (joins and aggregation)

2 
import { Function, Double } from "@foundry/functions-api";
import { Objects } from "@foundry/ontology-api";

export class CustomerFunctions {
  @Function()
  public async avgOrderAmount(region: string): Promise<Double> {
    // "JOIN" → traverse the Customer → Orders link instead of joining tables
    const result = Objects.search()
      .customers()
      .filter(c => c.region.exactMatch(region))
      .searchAroundOrders()        // link traversal replaces the JOIN
      .aggregate(agg => agg.amount.avg());   // GROUP BY / AVG via the aggregation API

    return result.amount.avg ?? 0;
  }
}

Use the Ontology SDK (OSDK) instead of raw SQL — this is usually the best path. Rather than SQL syntax, use the OSDK’s object/link traversal APIs.

Functions in Foundry run with a restricted token by default. SQL queries against the Ontology require the api:usage:sql-queries-execute scope, which isn’t granted automatically — hence the missingScope error