Επεξεργασία

Azure Digital Twins query language reference: JOIN clause

  • Άρθρο

This document contains reference information on the JOIN clause for the Azure Digital Twins query language.

The 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 $dtId value.

Syntax

--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>'

Example

The following query selects all digital twins that are related to the twin with an ID of ABC through a contains relationship.

SELECT T, CT
FROM DIGITALTWINS T
JOIN CT RELATED T.contains
WHERE T.$dtId = 'ABC'

Multiple JOINs

Up to five JOINs are supported in a single query, which allows for the traversal of multiple levels of relationships at once.

Syntax

--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>

Example

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']

Limitations

The following limits apply to queries using JOIN.

For more information, see the following sections.

Depth limit of five

Graph traversal depth is restricted to five JOIN levels per query.

Example

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.

Example

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).

Twins required

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 RELATED keyword.

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.