Azure Digital Twins query language reference: JOIN clause
This document contains reference information on the JOIN clause for the Azure Digital Twins query language.
JOIN clause is used in the Azure Digital Twins query language as part of the FROM clause when you want to query to traverse the Azure Digital Twins graph.
This clause is optional while querying.
Core syntax: JOIN ... RELATED
Because relationships in Azure Digital Twins are part of digital twins, not independent entities, the
RELATED keyword is used in
JOIN queries to reference the set of relationships of a certain type from the twin collection (the type is specified using the relationship's
name field from its DTDL definition). The set of relationships can be assigned a collection name within the query.
The query must then use the
WHERE clause to specify which specific twin or twins are being used to support the relationship query, which is done by filtering on either the source or target twin's
--SELECT ... FROM DIGITALTWINS <twin-collection-name> JOIN <target-twin-collection-name> RELATED <twin-collection-name>.<relationship-name> <OPTIONAL: relationship-collection-name> WHERE <twin-collection-name-OR-target-twin-collection-name>.$dtId = '<twin-id>'
The following query selects all digital twins that are related to the twin with an ID of
ABC through a
SELECT T, CT FROM DIGITALTWINS T JOIN CT RELATED T.contains WHERE T.$dtId = 'ABC'
Up to five
JOINs are supported in a single query, which allows for the traversal of multiple levels of relationships at once.
--SELECT ... FROM DIGITALTWINS <twin-collection-name> JOIN <relationship-collection-name-1> RELATED <twin-collection-name>.<relationship-type-1> JOIN <relationship-collection-name-2> RELATED <twin-or-relationship-collection-name>.<relationship-type-2>
The following query is based on Rooms that contain LightPanels, and each LightPanel contains several LightBulbs. The query gets all the LightBulbs contained in the LightPanels of rooms 1 and 2.
SELECT LightBulb FROM DIGITALTWINS Room JOIN LightPanel RELATED Room.contains JOIN LightBulb RELATED LightPanel.contains WHERE Room.$dtId IN ['room1', 'room2']
The following limits apply to queries using
For more information, see the following sections.
Depth limit of five
Graph traversal depth is restricted to five
JOIN levels per query.
The following query illustrates the maximum number of
JOIN clauses that are possible in an Azure Digital Twins query. It gets all the LightBulbs in Building1.
SELECT LightBulb FROM DIGITALTWINS Building JOIN Floor RELATED Building.contains JOIN Room RELATED Floor.contains JOIN LightPanel RELATED Room.contains JOIN LightBulbRow RELATED LightPanel.contains JOIN LightBulb RELATED LightBulbRow.contains WHERE Building.$dtId = 'Building1'
No OUTER JOIN semantics
OUTER JOIN semantics aren't supported, meaning if the relationship has a rank of zero, then the entire "row" is eliminated from the output result set.
Consider the following query illustrating a building traversal.
SELECT Building, Floor FROM DIGITALTWINS Building JOIN Floor RELATED Building.contains WHERE Building.$dtId = 'Building1'
If Building1 contains no floors, then this query will return an empty result set (instead of returning one row with a value for Building and
undefined for Floor).
Relationships in Azure Digital Twins can't be queried as independent entities; you also need to provide information about the source twin that the relationship comes from. This functionality is included as part of the default
JOIN usage in Azure Digital Twins through the
Queries with a
JOIN clause must also filter by any twin's
$dtId property in the
WHERE clause, to clarify which twin(s) are being used to support the relationship query.