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
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.
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\"".
Here is a selection for Entities of one Entity Type whose "status" is "Active".
Selection: Select
"Select" decides what the search returns. It does not change which Entities match. A plain string is read as the "Type".
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.
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.
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.
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.
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.
Attribute: Value sources
"ValueOption" decides where the compared value comes from.
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.
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.
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.
Criteria: Created by
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.
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.
Criteria: Historic
Matches Entities that have historic records. You can read about historic records in Entity historic data.
Criteria: Attachment
Criteria: Relation
Matches Entities by whether they are the Principal of a relation. You can read about relations in Entity relations.
Criteria: Related
Matches the Entities related to one Entity by a Relation Type.
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.
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.
Criteria: File import
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.
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.
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.
Criteria: Groups
A "subQuery" criterion holds its own list of criteria and acts as one bracketed condition.
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
Format DataLab JSON as User SQL
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.
User SQL: Examples
Upper case names are placeholders for your account's own Entity Types, attributes and IDs.
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.