DataLab queries


A DataLab query selects Entities by a list of criteria, and returns them, their IDs, a calculation over them or a Data Schema built from them.

People build DataLab queries in Operator, save them, and run them from Operator, from Dashboards and from Navigator menu items. The same selection is what a DataLab action runs against.

This article will cover how to run a search, the shape of a selection and every criterion, and how to write the same search as DataLab User SQL. You can read about saving queries in DataLab saved queries, about running bulk changes and exports in DataLab actions, and about comparing two selections in DataLab secondary queries.

Running a search

Send a selection as "PrimarySelection", or the whole search as "UserSQL" text. Send one or the other, not both.

The request body can also carry the same output options as an Entity search: "Expand" (a comma separated list of Location, LODCount, Import, Relation, Attachment, EntityType, Source, SourceData, LOD, Tag and Comment), "LODType", "Scenario", "Schema" and the historic values "HistoricKey", "HistoricFrom", "HistoricTo" and "HistoricPoint". These change the returned records, not which Entities match. You can read about historic values in Entity historic data.

Run a DataLab search

Request body
interface IPostBody {
    // The selection to run. See "Selection" below.
    "PrimarySelection"?: ISelection;
    // The same search written as DataLab User SQL, a SELECT.
    // Send this or the selections, not both.
    "UserSQL"?: string;
    // Runs a secondary selection instead of the primary one.
    // See the DataLab secondary queries page.
    "SecondarySelection"?: ISelection;
    // Number of matches to skip.
    // Default is 0.
    "Skip"?: number;
    // Number of matches to return.
    // Default is 50.
    // Set Skip and Load to -1 to return only the count.
    "Load"?: number;
    // An Entity ID. Returns that Entity's position in the ordered matches instead of a page.
    "IndexOf"?: string;
    // Account admins only. Adds the generated SQL to the response.
    "Debug"?: boolean;
}
Response
interface IResponse {
    // The matching Entities for this page.
    // Select decides what each item is: a record, an ID, an Entity Type ID or the internal values.
    "Items"?: any[];
    // The fields applied when Select listed "Fields".
    "SelectedFields"?: string[];
    // Number of matches, when Skip and Load were both -1.
    "Count"?: number;
    // Zero based position of the IndexOf Entity, or -1 when it does not match.
    "Index"?: number;
    // The calculation, when Select was "CALC".
    "Output"?: any;
    // The Data Schema, when Select was "DATA_SCHEMA".
    "DataSchema"?: any;
    // True when the page was full, so another page may exist.
    "NextPage"?: boolean;
    // A ready made url for the next page. Follow it as it is.
    "NextPageURL"?: string;
    // For a secondary selection, each primary Entity ID to the IDs of its secondary matches.
    "Mapping"?: { [primaryEntityId: string]: string[] };
    // Problems that did not stop the search, such as an external source that failed to load.
    "Error": string[];
    // Advisories, such as a truncated secondary result.
    "Warning": string[];
}
Javascript example

A search with no criteria is refused unless the selection sets "NoCriteria" to true, so a mistake cannot return every Entity in the account.

Counting and finding a position

Set both "Skip" and "Load" to -1 to count the matches instead of returning them. The response then carries "Count" and no "Items".

Set "IndexOf" to an Entity ID to find where that Entity sits in the ordered matches. The response carries "Index", the zero based position, or -1 when the Entity does not match. "Skip" and "Load" are ignored, and Skip = Index - (Index % Load) is the page that holds it. "IndexOf" only works on a primary selection that returns Entities.

// Count the matches.
const countBody = {
    "PrimarySelection": {
        "Items": [
            {
                "key": "entityType",
                "EntityType": { "EntityType.ID": "my-entity-type-id" }
            }
        ]
    },
    "Skip": -1,
    "Load": -1
};

// Find which page holds one Entity.
const indexBody = {
    "PrimarySelection": { ... },
    "IndexOf": "my-entity-id"
};

Selection

A selection is a list of criteria plus what to return and how to order it. Criteria combine in order, each joined to the one before it by its "LogicOperator". Use a "subQuery" criterion when you need brackets.

Attribute paths are written as quoted segments separated by a forward slash, Eg: "\"address\"/\"city\"". Internal values sit under "Bruce", Eg: "\"Bruce\"/\"ID\"".

interface ISelection {
    // The criteria. Each item's "key" decides its other fields.
    "Items": ICriterion[];
    // What the search returns. See "Selection: Select".
    // Default is whole Entity records.
    "Select"?: ISelect;
    // How matches are ordered. See "Selection: Order".
    "OrderBy"?: string | IOrderBy;
    // Used with a string OrderBy.
    // Default is ASC.
    "SortOrder"?: "ASC" | "DESC";
    // Set true to allow a selection with no criteria, which matches every Entity.
    // Default is false.
    "NoCriteria"?: boolean;
    // Limits the matches to these Entity IDs, on top of the criteria.
    "Entities"?: string[];
}

interface ICriterion {
    // Which criterion this is. Matched without regard to case.
    // Eg: "entityType", "attribute", "layer", "root".
    "key": string;
    // How this criterion joins the one before it.
    // On the first criterion only the NOT applies.
    // Default is AND.
    "LogicOperator"?: "AND" | "OR" | "AND NOT" | "OR NOT";
}

Here is a selection for Entities of one Entity Type whose "status" is "Active".

{
    "PrimarySelection": {
        "Items": [
            {
                "key": "entityType",
                "EntityType": { "EntityType.ID": "my-entity-type-id" }
            },
            {
                "key": "attribute",
                "LogicOperator": "AND",
                "AttributeValue": {
                    "AttributePath": "\"status\"",
                    "Operator": "=",
                    "OperandA": "Active"
                }
            }
        ]
    },
    "Skip": 0,
    "Load": 50
}

Selection: Select

"Select" decides what the search returns. It does not change which Entities match. A plain string is read as the "Type".

interface ISelect {
    /**
     * Default is ENTITY.
     * ENTITY = whole Entity records.
     * ID = the Entity IDs as a string array.
     * ENTITY_TYPE_ID = the distinct Entity Type IDs of the matches. Primary selection only.
     * Bruce = the internal values of each record at the root of the item. "Internal" is the same.
     * CALC = a calculation over the matches, returned as "Output".
     * DATA_SCHEMA = a Data Schema built from the matches, returned as "DataSchema".
     */
    "Type"?: "ENTITY" | "ID" | "ENTITY_TYPE_ID" | "Bruce" | "Internal" | "CALC" | "DATA_SCHEMA";
    // ENTITY only. Attribute paths each returned record is cut down to.
    // Matching, order and paging do not change.
    // Eg: [ "\"Bruce\"/\"ID\"", "\"status\"" ].
    "Fields"?: string[];
    // CALC only. Each attribute path to a comma separated list of calculations.
    // Eg: { "\"height\"": "min,max,avg" }.
    "Output"?: { [attributePath: string]: string };
    // CALC only. Attribute path to calculate per unique value of.
    "GroupBy"?: string;
    // DATA_SCHEMA only. Applies the Entity Types' attribute names and types to the built schema.
    // Default is true.
    "OverlayMetadata"?: boolean;
}

With "Fields", each item holds only those paths, nested as a whole record nests them. A path a record does not have is left out of its item, and a value held as null stays null. The response lists the applied paths in "SelectedFields".

The calculations are "min", "max", "avg" (also "average" and "mean"), "total" (the sum), "mode", "count" and "sample". Values are read as numbers for every calculation except "count" and "sample". "sample" returns up to 5 distinct raw values, and "sample:10" returns up to 10. An unknown calculation is skipped.

// Select:
// { "Type": "CALC", "Output": { "height": "min,max" } }
// Output:
{ "height": { "min": 0, "max": 320 } }

// Select with GroupBy:
// { "Type": "CALC", "Output": { "height": "max" }, "GroupBy": "\"region\"" }
// Output. A missing group value is reported as "<null>".
{ "height": { "north": { "max": 92 }, "south": { "max": 85 }, "<null>": { "max": 12 } } }

Selection: Order

Order matches by an attribute with a string "OrderBy" and "SortOrder", or with an object. Order a selection you page through, so pages do not repeat or skip records while the data changes.

interface IOrderBy {
    // Attribute path to order by.
    "AttributePath"?: string;
    // Default is ASC.
    "Order"?: "ASC" | "DESC";
    // The attribute's type, so values sort as numbers or dates.
    // Eg: "Double", "Integer", "Datetime", "String".
    // Leave out to take the type from the account's attribute definitions.
    "AttributeType"?: string;
    // Set true to return only the first match.
    // Default is false.
    "LimitTo1st"?: boolean;

    // Set to "embedding" (or "similarity") to order by closeness to a value instead of an attribute.
    "Key"?: "embedding" | "similarity";
    // Embedding only. Text to compare against, or a vector.
    "Value"?: string | number[];
    // Embedding only. The pgvector distance operator.
    // Default is "<=>" (cosine distance).
    "Operator"?: "<=>" | "<->" | "<#>" | "<+>";
    // Embedding only. Also compares the Entities' attached files.
    // Default is false.
    "SearchAttachments"?: boolean;
}

Criteria: Entity Type

Matches Entities of one or more Entity Types. Most selections start with this criterion, and attribute criteria read their types from these Entity Types' Data Schemas.

interface ICriterionEntityType {
    // "entity-type" also works.
    "key": "entityType";
    "LogicOperator"?: string;
    "EntityType": {
        // One ID, a comma separated list, or an array of IDs. Any of them matches.
        "EntityType.ID": string | string[];
    };
    // Also match Entity Types that inherit from these.
    // Default is false.
    "IncludeChildren"?: boolean;
}

Criteria: Attribute

Compares one attribute of each Entity. The comparison sits under "AttributeValue". The operators that apply depend on the attribute's type, which is read from the selected Entity Types' Data Schemas, then from the account's attribute definitions, and is a string when neither knows it.

interface ICriterionAttribute {
    "key": "attribute";
    "LogicOperator"?: string;
    "AttributeValue": {
        // Attribute path segments are separated by a forward slash. Eg: '"address"/"city"'.
        "AttributePath": string;
        // See "Attribute: Operators".
        // Default is "=".
        "Operator"?: string;
        // The value to compare against.
        // An array for IN, or for any of several values.
        "OperandA"?: any;
        // The second value, for BETWEEN.
        "OperandB"?: any;
        // Where OperandA comes from. See "Attribute: Value sources".
        // Default is CONST.
        "ValueOption"?: "CONST" | "USER" | "SAMEENTITY" | "PRIMARY";
        // Where OperandB comes from.
        // Default is the same as OperandA.
        "SelectedValueOptionB"?: string;
        // Overrides the attribute's type instead of reading it from the schema.
        "Attribute"?: {
            // Eg: "String", "Double", "Integer", "Datetime", "Boolean".
            "Type": string;
        };
        // Radius in metres, for NEAR BY.
        "Distance"?: number;
    };
}

Attribute: Operators

Every type accepts "NULL" and "NOTNULL". For text, "NULL" matches a missing attribute or an empty string. An operator the type does not support fails the request.

/**
 * String attributes.
 * "=" (also "EQUALS") = equal to OperandA, or to any value when OperandA is an array.
 * "IN" = the same as "=" with an array.
 * "!=" = not equal.
 * "CONTAINS" = contains OperandA, ignoring case.
 * "STARTS WITH", "ENDS WITH" = starts or ends with OperandA, ignoring case.
 * "CONTAINEDBY" = the attribute's value is contained in OperandA, ignoring case.
 * "CONTAINSBIDIR" = either value contains the other, ignoring case.
 * ">", "<", ">=", "<=" = compared as text.
 * An array OperandA with CONTAINS, STARTS WITH, ENDS WITH or CONTAINEDBY matches any of its values.
 * An attribute declared as an array in the schema matches when the array holds the value.
 */
type StringOperator = "=" | "IN" | "!=" | "CONTAINS" | "STARTS WITH" | "ENDS WITH" | "CONTAINEDBY" | "CONTAINSBIDIR" | ">" | "<" | ">=" | "<=" | "NULL" | "NOTNULL";

/**
 * Number attributes ("Double" or "Integer").
 * "BETWEEN" = from OperandA to OperandB.
 * "IN" = any value in the OperandA array.
 */
type NumberOperator = "=" | "!=" | "<" | ">" | "<=" | ">=" | "BETWEEN" | "IN" | "NULL" | "NOTNULL";

/**
 * Date and time attributes ("Datetime").
 * "AT" (also "="), "BEFORE" (also "<"), "AFTER" (also ">"), ">=", "<=", "BETWEEN", "IN" and "!=" compare against dates.
 * The DAYS operators compare to now, with OperandA as a number of days:
 * "DAYSBEFORENOWLESS" = less than OperandA days in the past.
 * "DAYSBEFORENOWMORE" = more than OperandA days in the past.
 * "DAYSAFTERNOWLESS" = less than OperandA days in the future.
 * "DAYSAFTERNOWMORE" = more than OperandA days in the future.
 */
type DateOperator = "AT" | "BEFORE" | "AFTER" | ">=" | "<=" | "BETWEEN" | "IN" | "!=" | "DAYSBEFORENOWLESS" | "DAYSBEFORENOWMORE" | "DAYSAFTERNOWLESS" | "DAYSAFTERNOWMORE" | "NULL" | "NOTNULL";

// Boolean attributes.
type BooleanOperator = "=" | "!=" | "NULL" | "NOTNULL";

Attribute: Value sources

"ValueOption" decides where the compared value comes from.

/**
 * CONST = OperandA is the value itself.
 * USER = read the same as CONST. Navigator's advanced search lets the person running the query change the value first.
 * SAMEENTITY = OperandA is { "AttributePath": string }, another attribute on the same Entity.
 * PRIMARY = OperandA is { "AttributePath": string }, read from the primary Entity. Secondary selections only.
 */
type ValueOption = "CONST" | "USER" | "SAMEENTITY" | "PRIMARY";

// Eg: Entities whose "actual" is greater than their own "expected".
{
    "key": "attribute",
    "AttributeValue": {
        "AttributePath": "\"actual\"",
        "Operator": ">",
        "ValueOption": "SAMEENTITY",
        "OperandA": { "AttributePath": "\"expected\"" }
    }
}

Attribute: Geometry

Three attribute paths compare an Entity's geometry instead of a value: "\"Bruce\"/\"Location\"" (its point), "\"Bruce\"/\"Boundaries\"" (its bounding box) and "\"Bruce\"/\"VectorGeometry\"" (its shape). Containment counts a geometry touching the edge as inside.

/**
 * "WITHIN" (also "INSIDE" and "CONTAINEDBY") = the Entity is inside OperandA.
 * "CONTAINS" = the Entity encloses OperandA.
 * "=" = the geometries are equal.
 * "INTERSECTS" = the geometries intersect.
 * "BOUNDSOVERLAP" = the bounding boxes overlap, a cheaper test when an approximate hit is enough.
 * "NEAR BY" (also "NEARBY" and "DISTANCETO") = within "Distance" metres of OperandA.
 * "NULL" = the Entity has no location. "NOTNULL" = it has one.
 */
type GeometryOperator = "WITHIN" | "CONTAINS" | "=" | "INTERSECTS" | "BOUNDSOVERLAP" | "NEAR BY" | "NULL" | "NOTNULL";

// OperandA is a Bruce geometry, one of:
// { "longitude": number, "latitude": number, "altitude"?: number }
// { "BottomLeftBack": ..., "TopRightFront": ... }
// { "Polygon": [ { "LinearRing": "lon,lat lon,lat ..." } ] }, or "MultiGeometry", "Point" or "LineString".
// For NEAR BY it can also be a "lon,lat" string.

// Eg: within 500 metres of a point.
{
    "key": "attribute",
    "AttributeValue": {
        "AttributePath": "\"Bruce\"/\"Location\"",
        "Operator": "NEAR BY",
        "Distance": 500,
        "OperandA": "174.7633,-36.8485"
    }
}

Attribute: Connected records

An attribute path can read a value from a record the Entity reaches, through an Entity Type source or an Entity relation. The path opens with "Bruce", then one or more hops, then "Data" and the attribute in the reached record.

// Through an Entity Type source, by its ID.
"\"Bruce\"/\"Outline\"/\"ETS:1\"/\"Data\"/\"HomeID\""
// Back towards the Entity Types that source from this one.
"\"Bruce\"/\"Dependent\"/\"ETS:1\"/\"Data\"/\"HomeID\""
// Through a relation to its Other Entities, by Relation Type ID.
"\"Bruce\"/\"Relation\"/\"RT:CountryOf\"/\"Data\"/\"Name\""
// Through a relation back to its Principal Entities.
"\"Bruce\"/\"RelationOf\"/\"RT:CountryOf\"/\"Data\"/\"Name\""
// Hops chain, Eg: a source of a source.
"\"Bruce\"/\"Outline\"/\"ETS:1\"/\"Outline\"/\"ETS:4\"/\"Data\"/\"Name\""

These paths also work in "OrderBy" and in a CALC "Output". A hop that can reach several records, a relation or a "Dependent" hop, cannot order matches, and calculations over it reduce per Entity first. "mode" is not available over such a path.

Criteria: Tags

Matches Entities by their Tags. Name Tags by ID, by name, or either. A name matches every Tag carrying it, ignoring case, and a name no Tag carries is reported in "Error" and matches nothing. You can read about Tags in Tags.

interface ICriterionTags {
    "key": "layer";
    "LogicOperator"?: string;
    "Layers": {
        /**
         * Default is "all".
         * all = the Entity has every listed Tag.
         * either = the Entity has at least one listed Tag.
         * neither = the Entity has none of the listed Tags.
         * none = the Entity has no Tags at all. No Tags need listing.
         */
        "Operator"?: "all" | "either" | "neither" | "none";
        // Tag IDs.
        "Layer.ID"?: number[];
        // Tag names.
        "Layer.Name"?: string[];
        // IDs or names. A whole number is read as an ID.
        "Layer"?: (number | string)[];
    };
}

Criteria: Created by

interface ICriterionCreatedBy {
    "key": "createdBy";
    "LogicOperator"?: string;
    "CreatedBy": {
        // A single User ID.
        "User.ID"?: string;
        // Several User IDs. Takes precedence over User.ID.
        "User.IDs"?: string[];
        // Used with User.IDs. "notin" excludes the listed Users, anything else matches any of them.
        "Operator"?: "in" | "notin";
    };
}

Criteria: Created or updated time

Matches Entities by when they were created or last updated. By default each operand is read at the precision it is written in: a bare date is the whole day, and a time is its minute. Set "Precise" to compare exact instants.

interface ICriterionCreatedTime {
    "key": "createdTime";
    "LogicOperator"?: string;
    "CreatedTime": {
        "Operator": "AT" | "=" | "BEFORE" | "<" | "AFTER" | ">" | ">=" | "<=" | "BETWEEN";
        // ISO 8601 date or date time. Eg: "2026-01-31".
        "OperandA": string;
        // The end, for BETWEEN.
        "OperandB"?: string;
        // Default is "CreatedTime".
        "Column"?: "CreatedTime" | "Updated";
        // Default is false.
        "Precise"?: boolean;
    };
}

Criteria: LOD

Matches Entities by their levels of detail. Assemblies hold Entities that only group others and have no geometry, so this is the usual way to narrow an assembly down to what can be seen. You can read about LODs in Entity LODs.

interface ICriterionLOD {
    "key": "lod";
    "LogicOperator"?: string;
    "LOD": {
        // LOD category key. Eg: "GLB".
        "Type"?: string;
        // Required with Type.
        // have = has an LOD of the category. have-not = has none of the category.
        "TypeOperator"?: "have" | "have-not";
        // A level to compare against.
        "Level"?: number;
        // Required with Level.
        "LevelOperator"?: "=" | ">=" | "<=" | "!=";
        // Counts an LOD on the Entity Type as the Entity having it.
        // Default is false.
        "IncludeEntityTypeLODs"?: boolean;
    };
}

Criteria: Historic

Matches Entities that have historic records. You can read about historic records in Entity historic data.

interface ICriterionHistoric {
    "key": "historic";
    "LogicOperator"?: string;
    "Historic"?: {
        // Only records under this historic attribute key. Eg: '"RecordedAt"'.
        // Default is any key.
        "HistoricKey"?: string;
        // Only records at this date or date time, to the precision it is written in.
        // Default is any time.
        "DateTime"?: string;
        // Scenario ID or key.
        // Default is the base records.
        "Scenario"?: string;
    };
}

Criteria: Attachment

interface ICriterionAttachment {
    "key": "attachment";
    "LogicOperator"?: string;
    "Attachment": {
        /**
         * Required.
         * attachment-type = has an attachment of "AttachmentType.ID".
         * attachment-group = has an attachment in "Group", ignoring case.
         * attachment-file-ext = has an attached file with "FileExt", ignoring case.
         * attachment-file-name = has an attached file whose original name contains "FileNameContains", ignoring case.
         */
        "Function": "attachment-type" | "attachment-group" | "attachment-file-ext" | "attachment-file-name";
        "AttachmentType.ID"?: string;
        "Group"?: string;
        "FileExt"?: string;
        "FileNameContains"?: string;
    };
}

Criteria: Relation

Matches Entities by whether they are the Principal of a relation. You can read about relations in Entity relations.

interface ICriterionRelation {
    "key": "relation";
    "LogicOperator"?: string;
    "Relation": {
        /**
         * Required.
         * have = is the Principal of a relation.
         * have-not = is the Principal of no relation.
         * primary = is the Principal of a relation to the primary Entity. Secondary selections only.
         */
        "Operator": "have" | "have-not" | "primary";
        // Relation Type ID.
        // Default is any Relation Type.
        "Type"?: string;
    };
}

Criteria: Related

Matches the Entities related to one Entity by a Relation Type.

interface ICriterionRelated {
    "key": "related";
    "LogicOperator"?: string;
    "Related": {
        "RelationTypeID": string;
        /**
         * forward = matches the Other Entities of relations whose Principal is the given Entity.
         * reverse = matches the Principal Entities of relations whose Other is the given Entity.
         * Default is forward.
         */
        "RelationDirection"?: "forward" | "reverse";
        // Required. CONST uses Entity.ID. PRIMARY uses the primary Entity, in a secondary selection.
        "ValueOption": "CONST" | "PRIMARY";
        "Entity.ID"?: string;
    };
}

Criteria: Within a hierarchy

Matches every Entity beneath one or more root Entities, the root included. This is how you list the records of an assembly or under a known parent.

interface ICriterionRoot {
    "key": "root";
    "LogicOperator"?: string;
    // One or more root Entity IDs, as an array or one comma separated string.
    // Required unless AssemblyID is given.
    "RootID"?: string | string[];
    // Assemblies whose root Entities to search under, each following the Relation Type on its record.
    "AssemblyID"?: number | number[];
    // Relation Type the hierarchy follows, and an Assembly's when its record names none.
    // Default is "BruceRsrvContains".
    "RelationTypeID"?: string;
    // Keeps only the hierarchies that hold these Entity IDs.
    "ChildID"?: string | string[];
    // Narrows the matches to these Entities and everything beneath them.
    // A root also listed here is matched in full, and a root holding none of them matches nothing.
    "RootBranchID"?: string | string[];
}

Criteria: Assembly roots

Matches the root Entities of assemblies: Entities that contain others and are contained by none. Their IDs are what Tilesets are generated from, and what a "root" criterion takes.

interface ICriterionAssemblyRoot {
    "key": "assemblyRoot";
    "LogicOperator"?: string;
    // Skips UCS Entities, which share coordinates between assemblies, and finds the roots beneath them.
    // Default is true.
    "IgnoreUCSRoots"?: boolean;
    // Leaves out roots that already have a model Tileset.
    // Default is false.
    "IgnoreWithTilesets"?: boolean;
}

Criteria: File import

interface ICriterionFileImport {
    "key": "fileImport";
    "LogicOperator"?: string;
    // ID of the import record. Matches the Entities recorded against that import.
    "ID"?: number;
    // Or an Entity ID, to use the import that Entity came from.
    "Entity.ID"?: string;
}

Criteria: Text and search

"text" matches an Entity whose ID equals the text, whose name contains it, or whose text and number values match it as a full text search. The full text search reads the text as web search syntax, so quotes and a leading minus work.

"search" matches an Entity whose ID equals the text ignoring case, or whose name, description or display name attributes contain it. The display name attributes are those of the Entity Type when the selection names one. Where the account has embeddings, it also matches Entities and attached files whose meaning is close to the text.

interface ICriterionText {
    "key": "text";
    "LogicOperator"?: string;
    "Text": string;
}

interface ICriterionSearch {
    "key": "search";
    "LogicOperator"?: string;
    "Search": {
        // Required.
        "Text": string;
    };
}

Criteria: Similarity

Matches Entities whose embedding is within a distance of a value. The account's database needs the vector extension, or the request fails. Text values are turned into an embedding by the installation's embeddings service.

interface ICriterionSimilarity {
    // "similarity" also works.
    "key": "embedding";
    "LogicOperator"?: string;
    "Embedding": {
        // Text to compare against, or a vector.
        "Value": string | number[];
        // The pgvector distance operator.
        // Default is "<=>" (cosine distance).
        "ValueOperator"?: "<=>" | "<->" | "<#>" | "<+>";
        // How the distance is compared. Eg: "<", "<=", "=", ">", ">=" or "BETWEEN".
        "DistanceOperator": string;
        "Distance": number;
        // The upper distance, for BETWEEN.
        "DistanceB"?: number;
        // Also compares the Entities' attached files.
        // Default is false.
        "SearchAttachments"?: boolean;
    };
}

Criteria: Values from another query

Matches Entities whose attribute holds one of the values another selection finds. Eg: Work Orders whose "status" is one of the values in a saved "Valid statuses" query. The other selection is a saved query, or a selection written inline. A saved query that refers back to itself is refused.

interface ICriterionAttributeJoin {
    // "attribute-join" also works.
    "key": "attributeJoin";
    "LogicOperator"?: string;
    "AttributeJoin": {
        // Attribute in this selection to compare.
        "InputAttributePath": string;
        // Attribute in the other selection to gather values from.
        "TargetAttributePath": string;
        // Give exactly one of the three below.
        // Saved query ID.
        "DataLabQueryID"?: number;
        // Saved query ID, Key or DisplayName, tried in that order.
        "DataLabQuery"?: string | number;
        // A selection written inline.
        "Selection"?: ISelection;
        // Set Type to "Double" or "Integer" to compare as numbers.
        // Default compares as text. Other types are refused.
        "InputAttribute"?: { "Type": string };
    };
}

Criteria: Groups

A "subQuery" criterion holds its own list of criteria and acts as one bracketed condition.

// Entities of a type that contain "A" or "B" in an attribute.
{
    "PrimarySelection": {
        "Items": [
            {
                "key": "entityType",
                "EntityType": { "EntityType.ID": "my-entity-type-id" }
            },
            {
                // "sub-query" also works.
                "key": "subQuery",
                "LogicOperator": "AND",
                "Subquery": {
                    "Items": [
                        {
                            "key": "attribute",
                            "AttributeValue": {
                                "AttributePath": "\"name\"",
                                "Operator": "CONTAINS",
                                "OperandA": "A"
                            }
                        },
                        {
                            "key": "attribute",
                            "LogicOperator": "OR",
                            "AttributeValue": {
                                "AttributePath": "\"name\"",
                                "Operator": "CONTAINS",
                                "OperandA": "B"
                            }
                        }
                    ]
                }
            }
        ]
    }
}

User SQL

DataLab User SQL writes a selection, or a selection and an action, as one SQL statement. Fields are named the way they appear on an Entity record, and the text compiles to the same JSON described above. Send it as "UserSQL" to "v3/datalab/runSearch" for a SELECT, or to "v3/datalab/runAction" for an UPDATE, DELETE, INSERT, MERGE or COPY. A statement sent to the wrong route is refused.

Compile a statement first. It changes nothing, and an error starts with the line and column of the problem, Eg: "Line 3, column 7: ...". The full reference and every example are also served as plain text at "llms/datalab" on your account's API, without a session.

Compile User SQL

Request body
interface IPostBody {
    "UserSQL": string;
}
Response
interface IResponse {
    "PrimarySelection": ISelection;
    // Present when the text has a JOIN or reads other records.
    "SecondarySelection"?: ISelection;
    // Present when the text is not a plain SELECT.
    "Action"?: any;
    // The language features this server compiles.
    // A feature that is not named is not supported.
    "Capabilities": {
        "Version": number;
        // CAST(attribute AS DOUBLE or INTEGER) compared in a WHERE.
        "WhereCast"?: boolean;
        // A SELECT of a list of fields returns records holding only those.
        "RecordProjection"?: boolean;
        // count(*).
        "CountStar"?: boolean;
    };
}
Javascript example

Format DataLab JSON as User SQL

Request body
// A selection, a body holding PrimarySelection with optional SecondarySelection and Action,
// or a saved query record from GET entities/datalab/savedQuery/{query_id}.
type IPostBody = any;
Response
interface IResponse {
    // Criteria with no nicer text are written as criterion('key', '{...}').
    "UserSQL": string;
}

User SQL: Language

Names go in double quotes and values in single quotes. "STATUS" is an attribute and "GROUP"."STATUS" a nested one. Values compare as text unless the attribute is declared a number in its Entity Type.

-- Internal fields.
"Bruce"."ID", "Bruce"."Name", "Bruce"."EntityType.ID", "Bruce"."Created", "Bruce"."Updated",
"Bruce"."CreatedBy.User.ID", "Bruce"."Location", "Bruce"."Boundaries", "Bruce"."VectorGeometry"

-- Tags: by ID, by name, or either (a whole number is an ID).
"Bruce"."Layer.ID", "Bruce"."Layer"."Name", "Bruce"."Layer"

-- Conditions.
=, !=, <, >, <=, >=, [NOT] IN, [NOT] BETWEEN, IS [NOT] NULL, IS [NOT] EMPTY, [NOT] LIKE with % at either end,
combined with AND, OR, NOT and brackets.
-- Tags use CONTAINS, CONTAINS ANY (...), CONTAINS ALL (...) and IS EMPTY.
-- CAST("ATTR" AS DOUBLE) compares or sorts as a number however it is stored.
-- day("Bruce"."Created") = '2026-01-31' matches a whole day.

-- Functions in WHERE.
search('words'), text('words'),
in_hierarchy('ENTITY_ID', ...), in_hierarchy(assembly => ID), either with relation_type => 'ID',
is_assembly_root(ignore_ucs_roots => TRUE, ignore_with_tilesets => TRUE),
has_attachment(type, group, file_ext or file_name => '...'),
has_historic(key, date_time and scenario => '...', each optional),
from_import(IMPORT_ID), has_relation('RELATION_TYPE', ...),
lod_level('LOD_CATEGORY') >= 2, or IS NULL for none.
-- A field IN (SELECT field FROM QUERY 'QUERY_KEY') matches a saved query, by ID, then Key, then name.

-- SELECT takes *, a list of fields, "Bruce"."ID" alone, "Bruce".*, DISTINCT "Bruce"."EntityType.ID", SCHEMA,
-- or count(*), min, max, avg, mean, sum, mode, count and sample with GROUP BY.
-- FROM takes Entity Types by name or ID, with INCLUDE CHILDREN. ORDER BY takes ASC or DESC, and LIMIT 1.

-- JOIN reads a second set against each match, aliased p for the FROM records and s for the joined ones.
-- JOIN LATERAL with LIMIT 1 keeps the first joined record per match.
-- ST_Within, ST_Contains, ST_Intersects and ST_DWithin(a, b, metres) compare geometries,
-- and RELATED(p, 'RELATION_TYPE', s) follows a relation.

-- UPDATE sets attributes, Tags, Entity Types or descriptions. DELETE removes records,
-- or relations with DELETE FROM RELATED. INSERT INTO RELATED relates pairs, INSERT INTO ATTACHMENT attaches a file.
-- MERGE copies attributes across matched pairs. COPY exports as CSV, XLSX, JSON or NSX.

-- Anything else, by its DataLab JSON.
criterion('KEY', '{...}')
SELECT action('ACTION_TYPE', '{...}') FROM ...

User SQL: Examples

Upper case names are placeholders for your account's own Entity Types, attributes and IDs.

-- Number ranges on a nested attribute.
SELECT *
FROM "ENTITY_TYPE"
WHERE "GROUP_ATTR"."NUMBER_ATTR" > 50
  AND "NUMBER_ATTR" BETWEEN 10 AND 20

-- Carrying either of two Tags, by name.
SELECT *
FROM "ENTITY_TYPE"
WHERE "Bruce"."Layer"."Name" CONTAINS ANY ('TAG_NAME', 'OTHER_TAG_NAME')

-- Only some fields of each matching record, highest first.
SELECT "Bruce"."ID", "Bruce"."Name", "GROUP_ATTR"."NUMBER_ATTR"
FROM "ENTITY_TYPE"
WHERE CAST("GROUP_ATTR"."NUMBER_ATTR" AS DOUBLE) >= 20
ORDER BY CAST("GROUP_ATTR"."NUMBER_ATTR" AS DOUBLE) DESC

-- How many records match.
SELECT count(*)
FROM "ENTITY_TYPE"
WHERE "STATUS_ATTR" = 'VALUE'

-- Average and count per group.
SELECT "GROUP_ATTR", avg("NUMBER_ATTR"), count("NUMBER_ATTR")
FROM "ENTITY_TYPE"
GROUP BY "GROUP_ATTR"

-- Matching the results of a saved query.
SELECT *
FROM "ENTITY_TYPE"
WHERE "KEY_ATTR" IN (SELECT "KEY_ATTR" FROM QUERY 'QUERY_KEY')

-- Records within 50 metres of each match.
SELECT s.*
FROM "ENTITY_TYPE" AS p
JOIN "OTHER_ENTITY_TYPE" AS s ON ST_DWithin(s."Bruce"."Location", p."Bruce"."Location", 50)

-- Set attributes, typed by a CAST or the schema.
UPDATE "ENTITY_TYPE"
SET "STATUS_ATTR" = 'VALUE',
    "NUMBER_ATTR" = CAST(5 AS DOUBLE),
    "NAME_ATTR" = NULL
WHERE "STATUS_ATTR" = 'OTHER_VALUE'

-- Add a Tag, by name.
UPDATE "ENTITY_TYPE"
SET "Bruce"."Layer"."Name" = "Bruce"."Layer"."Name" || ARRAY['TAG_NAME']
WHERE "STATUS_ATTR" = 'VALUE'

-- Apply a saved Data Transform.
UPDATE "ENTITY_TYPE"
SET * = data_transform('DATA_TRANSFORM_NAME')
WHERE "STATUS_ATTR" = 'VALUE'

-- Keep the first record per key, merging the rest into it.
DELETE FROM "ENTITY_TYPE"
WHERE ROW_NUMBER() OVER (PARTITION BY "KEY_ATTR" ORDER BY "Bruce"."ID") > 1

-- Relate each record to the records sharing its key.
INSERT INTO RELATED(p, 'RELATION_TYPE', s)
SELECT *
FROM "ENTITY_TYPE" AS p
JOIN "OTHER_ENTITY_TYPE" AS s ON s."KEY_ATTR" = p."KEY_ATTR"

-- Export chosen columns as CSV.
COPY (SELECT "KEY_ATTR" AS "Key", "STATUS_ATTR" FROM "ENTITY_TYPE" WHERE "NUMBER_ATTR" > 5) TO STDOUT WITH (FORMAT CSV)

Suggest a query from a prompt

Operator can draft a saved query from a sentence. The request sends the prompt and a summary of the account's Entity Types and attributes to OpenAI, using the account's OpenAI key. You can read about that key in OpenAI. When the suggestion does not validate, the response falls back to a "text" criterion holding the prompt. Nothing is saved.

Suggest a DataLab query

Request body
interface IPostBody {
    // The search described in words.
    "Prompt": string;
    // Most criteria to generate, from 1 to 16.
    // Default is 6.
    "MaxCriteriaItems"?: number;
    // OpenAI model name.
    // Default is "gpt-4o-mini".
    "Model"?: string;
}
Response
// The response is a saved query record without an ID, ready to review and save.
// See the DataLab saved queries page for the record structure.
Javascript example